Difference between sub query and nested query ?
- 19 Answers
- 64932 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 |
candidate key is subset of super key but not vice-verse explain
What is Normalization ?
55 Answers ACS, Genpact, Graphix Solution, IBM, Keane India Ltd, TCS, Vault,
What are the differences between primary key and unique key?
What is a database schema in oracle?
What is connection pooling in oracle?
What is Database Buffers ?
What happens if you set the sga too low in oracle?
which is best insttute for teradata,sybase,mysql,oracle in hyderabad
What types of joins are used in writing subqueries?
What does `(+)` do in a where clause?
what is difference between where clause and having clause?
create or replace procedure show_tab_rec ( P_tab VARCHAR2 ) IS cmd varchar2(50); begin cmd := 'select * from '|| P_tab; for int in ( execute immediate cmd ) loop dbms_output.put_line ( int.ename||' '||int.deptno); end loop; end; when i m compling this procedure i m getting this error PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in is mod remainder not range rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ PLZ solve this error give this question answer asap Thanks advance.......
Oracle General 
