Difference between sub query and nested query ?

Categories >> Software >> Databases >> Oracle >> Oracle General
Suggest New Category

Question Posted / neelam

19 Answers
68489 Views
Keane India Ltd, TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Difference between sub query and nested query ?..

Answer / lakshmi

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 Correct ?

96 Yes

11 No

Difference between sub query and nested query ?..

Answer / rk

Hi all , My kind request is before jumping out and write the
answers. please check it whether , ur answer is correct.
Becos, most of the ppl. prepare for the interview with your
Answers.

In this thread , the answer given by "Vrushali" is totally
WRONG....
please don't mislead the ppl, with ur misunderstanding!!

cheers
RK.

PS: i don't want to hurt anyone ,,,, please think abt. this ...

Is This Answer Correct ?

67 Yes

4 No

Difference between sub query and nested query ?..

Answer / chiyan

Hi all, i go wth RK....plz dnt mislead others...best example
is me for dat case......i jst told the answer what i read
frm one another person !!!!!!!!!!The interviewer jst sacked
me out of the panel...so jst dnt laugh but think guys nd
gals.......

Is This Answer Correct ?

21 Yes

5 No

Difference between sub query and nested query ?..

Answer / sunil

A correlated Subquery runs for the rows selected from the outer query. It takes the value from the outer query
and execute the inner query for that value

example:

select * from emp e
where e.deptno in(select d.deptno from dept d
where e.deptno = d.deptno);

in this query emp table's deptno will be passed into the inner query(select deptno from dept d where e.deptno = d.deptno).
And the inner query will execute only for that value from the outer query.
That's why it is called correlated subquery

In Nested subquery the inner query runs only once and pass the result set to the outer query.

example

select * from emp e
where e.deptno in(select d.deptno from dept d);

Here the inner query (select d.deptno form dept d) will run first and fetches all the rows from the dept table
and the outer query will select only the records that has the matching deptno in the result set fetched by the
inner query. The outer query will act as a nesting query and that is why this is called nested subquery.

Here in correlated subquery, the outer query executes first and the inner query will execute second.

But in Nested subquery, the inner query executes first and the outer query executes second.

Hope this helps.
thanks to binosh who helped me to understand this concept before posting here...

Is This Answer Correct ?

5 Yes

0 No

Difference between sub query and nested query ?..

Answer / dinesh sahoo

The query inside a query is known as a subquery.
When we have another query again query inside subquery then
it is known as nested subquery, which resinde in the WHERE
clause.

Is This Answer Correct ?

12 Yes

9 No

Difference between sub query and nested query ?..

Answer / nirvaya

A subquery is a query within a query. In Oracle, you can
create subqueries within your SQL statements. These
subqueries can reside in the WHERE clause, the FROM clause,
or the SELECT clause. Most often, the subquery will be
found in the WHERE clause. These subqueries are also called
nested subqueries.

Is This Answer Correct ?

9 Yes

6 No

Difference between sub query and nested query ?..

Answer / roshan

ONE THING I WOULD LIKE TO TELL.. SUBQUERY RUNS MUCH FASTER
THAN CO-RELATED QUERY..
BECAUSE EACH TIME IT HAS TO COMPARE THE OUTER QUERY IS
EVALUATED EACH TIME............

Is This Answer Correct ?

7 Yes

4 No

Difference between sub query and nested query ?..

Answer / mohammed al-fahed

The query inside a query is known as a subquery.
When we have another query again query inside subquery then
it is known as nested subquery, which resinde in the WHERE
clause.

Is This Answer Correct ?

3 Yes

2 No

Difference between sub query and nested query ?..

Answer / mohanty

query inside the query is nested query.
it is also called as sub query.
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.

Is This Answer Correct ?

1 Yes

0 No

Difference between sub query and nested query ?..

Answer / vinoth

subquery is also known as nested query ....
so guys don't confused about answer
what u had is the correct answer
baiiiiiiiiiiiiiiiii.....

Is This Answer Correct ?

2 Yes

2 No

Post New Answer