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 can we create the complete backup of data in the oracle.

2159

---

How does oracle handle read consistency?

1097

---

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

1072

---

How remove data files before opening a database?

1072

---

11. Display the client number and name and the client number and name of the person who referred that client.

2339

---

How to use "out" parameter properly?

1082

---

How do I limit the number of rows returned by an oracle query after ordering?

1169

---

What is a system tablespace and when it is created?

972

---

Explain the features of oracle?

1046

---

How many types of cluster table in Oracle?

1034

---

What is oracle sid?

955

---

What are the advantages of oracle 12c?

1180

---

How to set up autotrace for a user account?

1059

---

What are operators in oracle?

1055

---

What are the differences between char and varchar2 in oracle?

1170

---