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

In XIR2 if we lost the administration password .How can we regain the password?thanks in advance.

1980

---

Can we protect our pl/sql source code?

1079

---

How to delete a column in an existing table?

1128

---

What are dml statements in oracle?

1130

---

What is a cursor variable?

1213

---

How index is implemented in oracle database?

1040

---

How to export several tables together?

1103

---

how to use select statement as formal parameter in procedure specification?(someone said that using string) ex:-procedure(a in number,select ename from emp ) i am asking syntax like this?

2052

---

What are the execution control statements in oracle?

1126

---

What is the meaning of recursive hints in oracle?

1094

---

What is an oracle user account?

1037

---

What is the difference between $oracle_base and $oracle_home?

1136

---

What is truncate oracle?

1011

---

why should i declare foreign key constraint as self relation instead of binary relation in tables ?

2088

---

What is control file used for?

1298

---