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

What is a package in oracle?

750

---

Explain oracle’s system global area (sga).

840

---

What is a nested table and how is it different from a normal table?

780

---

Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracles. What database recovery options are available? Database is in archive log mode.

1735

---

How to assign values to variables?

795

---

---

6. Display the client name and order date for all orders using the traditional method.

1988

---

What is the difference between 10g OEM and 11g OEM?

715

---

Why do we need integrity constraints in a database?

752

---

Explain the difference between replace() and translate() functions in oracle?

781

---

Explain an extent?

827

---

What is oracle instant client?

738

---

Difference between varchar and varchar2 data types?

829

---

Explain the use of inctype option in exp command.

765

---

How to get a create statement for an existing table?

724

---

What is an oracle table?

729

---