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

 Posted by Bharathi Cherukuri on 4/23/2012 | Category: Sql Server Interview questions | Views: 1381 | Points: 40

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.


select e1.empname, e1.basicsal, e1.deptno from emp e1 

where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.


select empname, basicsal, deptno from emp 

where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response