Difference between sub query and nested query ?

Answer Posted / 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



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

How many categories of data types?

766


How to check the oracle tns settings?

798


What is save point in oracle database?

1014


How many anonymous blocks can be defined?

813


Why use resource manager in Oracle?

869


How to delete an existing row from a table in oracle?

851


What are the uses of a database trigger?

719


Which are the five query types available in oracle?

732


Differentiate between post-database commit and post-form commit?

788


What is tns name?

738


How to best split csv strings in oracle 9i?

797


What is control file used for?

809


How view is different from a table?

810


What is the difference between primary key and unique key and foreign key in oracle?

765


What is oracle and what are its different editions?

795