samiksc

 Profile Answers by 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)

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

rangunagaraj

 Profile Answers by rangunagaraj 

• Jul 5th, 2007

 

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.

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.

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.

Pranav

• Jul 27th, 2016

 

Thank you Ashish

qptopm

 Profile Answers by 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.