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

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

Showing Answers 1 - 25 of 25 Answers

samiksc

  • Jan 19th, 2006
 

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)

  Was this answer useful?  Yes

anand prakash

  • Mar 21st, 2007
 

Read this example (diffrence between sub query and correlated sub query)

create table a(id number(2), ad varchar2(2));
create table b(id number(2), ad varchar2(2));

id-ad  <-- records in table a
1  A
2  B
3  C
4  D
5  E

id - ad
1
1
1
1
1
2
2
2
3
2
1
3
3
4
1
1
3
3
5
4
4
4

now i want to update b with "ad" from a

queyr will be .....
update b set ad =(select ad from a where a.id=b.id)

look at sub query ---> your sub query is (select ad from a where a.id=b.id)
when u will execute this independently it will gen. err. because "b" is not in from list
it is refenced from outer query.
so you can say it is an example of correlated sub query.

now
select  ename,sal,deptno from emp
where deptno in(select deptno from dept)

this is your subquery
 


Tushar

  • May 9th, 2007
 

When we need distinct with join we use corelated sub query (where exists clause) which works faster than nested sub query

  Was this answer useful?  Yes

If the evaluation of an inner querry(a sub querry) depends on a variable which gets its value in an outer querry,such a subquerry has to be evaluated for each value of the variable.Such a querry is a correlated subquerry.If no such variable is used,the subquerry needs to be evaluated only once for the entire querry,and is called a non-correlated subquerry.

  Was this answer useful?  Yes

V.CHIRU

  • Oct 13th, 2011
 

If the evaluation of an inner query(a sub query) depends on a variable which gets its value in an outer query,such a subquery has to be evaluated for each value of the variable.Such a query is a correlated subquery.If no such variable is used,the subquery needs to be evaluated only once for the entire query,and is called a non-correlated subquery.

  Was this answer useful?  Yes

Ashish

  • Apr 30th, 2016
 

Nested Sub Query: First inner query execute after that outer query execute means outer query is dependent upon result of inner query.

Co-Related Sub Query: First outer query execute after that inner query execute means inner query is dependent upon result of outer query.

  Was this answer useful?  Yes

Pranav

  • Jul 27th, 2016
 

Thank you Ashish

  Was this answer useful?  Yes

qptopm

  • Oct 13th, 2016
 

Co-Related Vs Nested-SubQueries.
Technical difference between Normal Sub-query and Co-related sub-query are:
1. Looping: Co-related sub-query loop under main-query; whereas nested not; therefore co-related sub-query executes on each iteration of main query. Whereas in case of Nested-query; subquery executes first then outer query executes next. Hence, the maximum no. of executes are NXM for correlated subquery and N+M for subquery.
2. Dependency(Inner to Outer vs Outer to Inner): In the case of co-related subquery, inner query depends on outer query for processing whereas in normal sub-query, Outer query depends on inner query.
3.Performance: Using Co-related sub-query performance decreases, since, it performs NXM iterations instead of N+M iterations. ¨ Co-related Sub-query Execution.

Abdul Rahman

  • Jul 18th, 2017
 

A nested sub query is one sub query inside another sub query. A correlated sub query is a sub query that references a column from a table which appears in the parent statement.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions