Difference between sub query and nested query ?
- 19 Answers
- 62306 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 |
Two triggers one is before insert and other is after insert are firing on a table.If 10 times you insert in a table.Then how many times before trigger and how many types after trigger will fire in pl/sql.
How to create a server parameter file?
We have one Package(which has many function/procedures, SQL quires etc). Now we need to check, which query or procedure is taking lot of time in that Package. ? How do we do it.
How do I know if oracle is installed on windows?
Differentiate between pre-select and pre-query?
query optmization techniques and quwry analyser+projects+ppts
Her departmandaki isçilerden empno' su ikinci sirada olan isçilerin empno, deptno, hiredate, sira_no bigilerini döndüren sorguyu yaziniz?
What are joins, explain all types of joins?
18. Display the clientno and total value for all orders placed by that client. Output the result in the following format: Client <clientno> has placed orders to the value of <total value>
Describe the different type of Integrity Constraints supported by ORACLE ?
Can u make a synonym for deptno=10 only from emp table.
How to drop a stored procedure in oracle?
Oracle General 
