Difference between sub query and nested query ?
- 19 Answers
- 62346 Views
- Keane India Ltd, TCS, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
What are the various oracle database objects?
can anyone help me ? an index has been done on the primary key of a table. an update operation was performed on that table. now my question is 1> what abt the performance ? means faster or slower due to indexing ? 2> does the operation affect to the primary key constraint ?
What does `(+)` do in a where clause?
Explain enable novalidate constraint.
How many categories of data types in oracle?
How to view the dropped tables in your recycle bin?
How i can handle exception in large code like 1000 line without distrubing the code or without exception handler sction?
How to test null values?
Explain coalesce function?
How many categories of data types?
Explain the use of record option in exp command.
There is a big table with "n" of rows and 40 + columns .It doesn't have primary key.How do you select the primary key. In other words how do you get the duplicate records.
Oracle General 
