Subqueries

[farrokhp]

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.

[mvander2]

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

[padmam]

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