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
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)
WHERE x.deptno = deptno)
ORDER BY deptno;
hope this helps,
Hi, 7th june 2001 10:19 hrs chennai
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.
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.
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
Click Here to Expand Forum to Full Width