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...

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

---