Nested subquery and Co-related query

Question

What is the difference between Nested Subquery and co-related query.

Tulasi · Answer

Hi 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 Kumar · Answer

Correlated SubQuery : Inner query is depend upon the outer query. Ex: Select A.nIDA.sName from table1 A where A.sCity = (Select sCityName from table2 where nId = A.nID) Nested Query: There is no relationship between inner and outer query. Ex: Select nIDsName from table1 where sCity in (Select distinct sCityName from table2) 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 More · Answer

Hi lucy 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.