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: 1584 | Points: 40
Answer:

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.

Example:

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.

Example:

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