Nested subquery and Co-related query
What is the difference between Nested Subquery and co-related query.
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
TulasiPosted Oct 24, 2011, 12:36 AM
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.
Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
For example,
Correlated Subquery:
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:
select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)Please mark the answer as Accepted if it helps you.
Hemant KumarPosted Oct 25, 2011, 9:28 AM
Nested Query: There is no relationship between inner and outer query.
For more info...http://www.databasejournal.com/features/mssql/article.php/3485291/Using-a-Correlated-Subquery-in-a-T-SQL-Statement.htm
http://www.dba-sql-server.com/sql_server_tips/t_super_sql_411_subqueries.htm
http://vivekjohari.blogspot.com/2009/09/difference-between-subquery-nested.html
Pravin MorePosted Oct 24, 2011, 12:33 AM
Co-related sub query is one in which inner query is evaluated only once and from that result outer query is evaluated.
Nested query is one in which Inner query is evaluated for multiple times for gatting one row of that outer query.
ex. Query used with IN() clause is Co-related query.
Query used with = operator is Nested query.
Thanks,
Pravin.