-
Can you explain to me in a plain english with basic example what is the difference between reqular subqueries and correlated subquries ? It's a little fuzzy to me.
An ounce of prevention is worth a pound of cure
-
Hi ,
from the oracle documentation ...
Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement
The following statement returns data about employees whose salaries exceed their department average. The following statement assigns an alias to emp, the table containing the salary information, and then uses the alias in a correlated subquery:
SELECT deptno, ename, sal
FROM emp x
WHERE sal > (SELECT AVG(sal)
FROM emp
WHERE x.deptno = deptno)
ORDER BY deptno;
hope this helps,
greetings,
Marc
-
Solution
Hi, 7th june 2001 10:19 hrs chennai
SubQuery:
------------
In normal condition the sub query Evaluates first with a Output value to the main Query based on which the main Query is executed ONCE.
correlated subQuery
-----------------------
In this type of subquery for each and every record in the main query the sub query is invoked
Follow the example as above here what the emp table does is for every record in the main Query table (of the same emp table ) in the inner sub queries evaluates average sal is calculated for the partcular deptno(as per join condition and the i.e on the main query Deptno and inner query deptno) and compared for the condition to pass.
cheers
padmam
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|