Monday, October 12, 2009

use of with clause in sql

we can use with clause in a query to faster a query (replace of sub-query)
because it retrive subquery value once.

when a subquery value need to retrive for using in whole sql query.

===========================================================================

WITH q AS
(SELECT dummy
FROM DUAL)
SELECT dummy
FROM q


WITH NAME AS
(SELECT *
FROM emp
WHERE sal > 2000)
SELECT *
FROM NAME
WHERE ename LIKE 'S%'



WITH NAME AS
(SELECT empnam, b.empidn
FROM stempgen a, stfacmas b
WHERE a.empidn = b.empidn)
SELECT empnam
FROM NAME
WHERE empnam LIKE 'A%'

No comments: