Descending from Oracle 9i release 2, the WITH clause has been implemented.
The WITH clause is like a temp table function that used widely and frequently in other database systems.
It's too bad this clause surfaces so late, but to look on the good side, I am glad we finally have such a good and convenient syntax to organize more efficient queries.


WITH temp_name AS (
    select NVL(column_name,'') as CN
    from table_a
    where .....
select ....
from table_b
join temp_name on ...
where ...

Use WITH can improve the efficiency and usability ,and it's easy to read and modify.
It also provides a way to tune heavy-loading queries when they are needed to be tuned.



swangs 發表在 痞客邦 留言(0) 人氣()