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

GeekInterview.com > Interview Questions > Database > SQL

Go To First | Previous Question | Next Question

SQL | Question 41 of 139

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

Total Answers and Comments: 4

Last Update: July 05, 2007 Asked by: Chirag

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

January 19, 2006 00:43:41

samiksc

Member Since: October 2005 Contribution: 233

RE: what is diffrence between Co-related sub query and...

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)

Is this answer useful? Yes | No

March 21, 2007 03:33:14

anand prakash

RE: What is difference between Co-related sub query an...

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

Is this answer useful? Yes | No

May 09, 2007 08:26:32

Tushar

RE: What is difference between Co-related sub query an...

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

Is this answer useful? Yes | No

July 05, 2007 19:59:42

rangunagaraj

Member Since: February 2007 Contribution: 5

RE: What is difference between Co-related sub query an...

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.

Is this answer useful? Yes | No

Go To Top