Home » SQL & PL/SQL » SQL & PL/SQL » Correlated sub-query
Correlated sub-query [message #237982] Wed, 16 May 2007 04:56 Go to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

What is the difference between a correlated subquery and a nested sub query?
Re: Correlated sub-query [message #238010 is a reply to message #237982] Wed, 16 May 2007 06:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The difference is about 50000 hits on Google.
Did it occur to you to just search for the both and compare what you find?
Re: Correlated sub-query [message #238165 is a reply to message #238010] Wed, 16 May 2007 22:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'll take a different tack on this. Without taking Frank's advice and actually searching on Google, I'd bet that what I'm about to say could not be easily found. However, If you took the time to fully understand both concepts you would be able to arrive at the same result.

Correlated sub-query is reference to the way the sub-query is WRITTEN. The WHERE clause of the sub-query references columns from the outer query.
eg.
SELECT *
FROM   EMP
WHERE  EXISTS (
    SELECT 1
    FROM   DEPT
    WHERE  dept.dept_no = emp.dept_no)


A non-correlated sub-query does not reference a column in the outer query:
SELECT *
FROM emp
WHERE dept_no IN (
    SELECT dept_no
    FROM   dept)


A Nested sub-query is a reference to the way the sub-query is EXECUTED. If the sub-query is executed once for each row returned from the outer-query, then it is said to be NESTED. If the sub-query is executed just once and the results are joined in some way to the outer query, then it is not NESTED.

In older versions of Oracle (8.0 and earlier I think), EXISTS and NOT EXISTS sub-queries were always executed as NESTED. Also, all correlated sub-queries were executed as NESTED even if they used IN or NOT IN.

In current versions, Oracle has the capability to rewrite most queries internally in order to execute either nested or unnested.

Ross Leishman
Re: Correlated sub-query [message #238211 is a reply to message #237982] Thu, 17 May 2007 02:08 Go to previous message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Frank, It's better to keep shut than bluffing. I think u don't know the answer for my question and ur reply very well suggests that....lol...


Leishman, Thanks for the reply...it is clear for me now
Previous Topic: DBMS_LOCK'.sleep not working. Alternate?
Next Topic: Can you guyz check this code plz !!!
Goto Forum:
  


Current Time: Sat Dec 10 05:17:30 CST 2016

Total time taken to generate the page: 0.23895 seconds