|
|
|
|
| Re: Correlated sub-query [message #238165 is a reply to message #238010] |
Wed, 16 May 2007 22:17   |
rleishman
Messages: 3728
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
