Correlated subquery
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.
from the row selected by the outer query.
Correlated subquery follows down to top approach i.e., main query is executed first(even though parenthesis
are present) and then child query.
are present) and then child query.
We can also say:In a subquery.
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
Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the
outer query row.
outer query row.
We can also say: in a Correlated subquery,Inner query condition is used in the outer query.
Nested subquery follows top-down approach i.e., child query is executed first and then parent .
Outer query condition is used in the the inner query.
Example:
select empname, basicsal, deptno from emp
where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)
No comments:
Post a Comment