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 will you differentiate between varchar & varchar2?

827


How can we find out the current date and time in oracle?

856


In Oracle Clinical 4.5.0, can the VIEW_TEMPLATE_ID column in DATA_EXTRACT_VIEWS table contain NULL value?

2148


i wrote a pl/sql procedure. it must run every sunday 4.40 How can i schedule it with the help of dbms_jobs (or another other procedure with out creating bat file,exe file)

2041


How to end the current transaction in oracle?

788


material view and view disadvantages?

832


What privilege is needed for a user to create tables in oracle?

770


What is oracle latest version?

876


What happens if variable names collide with table/column names?

873


Can multiple cursors being opened at the same time?

836


What is the meaning of recursive hints in oracle?

786


What happens if you use a wrong connect identifier?

739


What is recovery manager(rman) backup in Oracle?

938


what is dynamic SGA and static SGA

4083


How can we create the complete backup of data in the oracle.

1930