Sub query

Question

What is diffrence between Co-related sub query and nested sub query?

Aarti · Answer

Hi Arjun A correlated Subquery runs for the rows selected from the outer query. It takes the value from the outer query and execute the inner query for that value example: select * from Employee e where e.deptno in(select d.deptno from dept d where e.deptno = d.deptno); in this query Employee table's deptno will be passed into the inner query(select deptno from dept d where e.deptno = d.deptno. And the inner query will execute only for that value from the outer query. That's why it is called correlated subquery In Nested subquery the inner query runs only once and pass the result set to the outer query. example: select * from Employee e where e.deptno in (select d.deptno from dept d); Here the inner query (select d.deptno form dept d) will run first and fetches all the rows from the dept table and the outer query will select only the records that has the matching deptno in the result set fetched by the inner query. The outer query will act as a nesting query and that is why this is called nested subquery. Here in correlated subquery the outer query executes first and the inner query will execute second. But in Nested subquery the inner query executes first and the outer query executes second. Hope this helps. Thanks

Arjun Panwar · Answer

ThanksVidya Rani

Tulasi · Answer

Nested Sub Query : This Query will be Executed only once  inner query is excuted first and result is sent to outer query for execution Ex : select * from emp where (deptnobasicsal) in (select deptnomax(basicsal) from emp group by deptno) where as Co-related Sub Query : is executed for every row of Outer or main Query  outer query is executed first and result is sent to inner query for execution Ex : select * from emp e1 where e1.basicsal=(selcte max(basicsal) from emp e2 where e2.deptno=e1.deptno)

Arjun Panwar · Answer

can you elaborate with example ?

Arjun Panwar · Answer

Both the answers is same.

Satyapriya Nayak · Answer

Hi Arjun A correlated Subquery runs for the rows selected from the outer query. It takes the value from the outer query and execute the inner query for that value example: select * from emp e where e.deptno in(select d.deptno from dept d where e.deptno = d.deptno); in this query emp table's deptno will be passed into the inner query(select deptno from dept d where e.deptno = d.deptno#. And the inner query will execute only for that value from the outer query. That's why it is called correlated subquery In Nested subquery the inner query runs only once and pass the result set to the outer query. example select * from emp e where e.deptno in#select d.deptno from dept d#; Here the inner query #select d.deptno form dept d) will run first and fetches all the rows from the dept table and the outer query will select only the records that has the matching deptno in the result set fetched by the inner query. The outer query will act as a nesting query and that is why this is called nested subquery. Here in correlated subquery the outer query executes first and the inner query will execute second. But in Nested subquery the inner query executes first and the outer query executes second. Refer the link http://en.allexperts.com/q/Oracle-1451/2009/7/Diff-Nested-Correlated-subqueries.htm Thanks