| |
GeekInterview.com > Interview Questions > Database > SQL
| Print | |
Question: What is difference between Co-related sub query and nested sub query?
Answer: 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 |
| March 03, 2007 03:33:14 |
#2 |
| anand prakash |
Member Since: Visitor Total Comments: N/A |
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
|
| |
Back To Question | |
