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 to export your own schema?

572

---

What is the data pump import utility?

581

---

State and explain the different types of data models?

536

---

what's query optimization and without use of IN AND Exist can we get another way data from query

1666

---

Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?

1481

---

---

How to delete all rows a table in oracle?

621

---

Explain about integrity constraint?

603

---

material view and view disadvantages?

635

---

I am using an Oracle 8i Database my data contains Clob data. I am using toad version 7.6 i am able to get the data in toad but unable to extract the data in excel.when trying to extract the data into the excel the toad error says out of memory. Can any body please help me to extract the data through the same toad version. Thanks in advance

1846

---

Calculate difference between 2 date / times in oracle sql?

572

---

How to use an explicit cursor without open statements?

614

---

What is oracle sid?

507

---

How do we represent comments in oracle?

627

---

Why do we use coalesce function in oracle?

629

---

Difference between oracle's plus (+) notation and ansi join notation?

576

---