Correlated Sub Queries

Correlated sub queries are used for row-by-row processing. 

Here each sub query is executed once for every row of the outer query.The oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. 

A correlated sub query is evaluated once for each row processed by the parent statement (query)

The parent statement can be a SELECT, UPDATE or DELETE statement.

The general for of a correlated sub query is:

select column1, column2, . . . . . . .

from table1 outer

where [column1] operator (select column1, column2,

from table2 where expr1= outer.expr2);

The sub query references a column from a table in the parent query, using the Exists operator:  a Boolean operator.

The exists operator tests for existence of rows in the results of the sub query.

• If a sub query value is found:

1. The search does not continue in the inner query
2. The condition is flagged true.

• If a sub query row value is not found:

1. The condition is flagged false.
2. The search continues in the inner query.

While nesting select statement, all logical operators are valid.  In addition, we can use the exists operator.  This operator is frequently used with correlated sub queries to test whether a value retrieved by the outer query exists in the results set of the values retrieved by the inner query.

Example:

To find all the employees who have at least one person reporting to him, we go as follows

select empno, ename,job,deptno from emp outer

   where exists(select 'x' from emp where mgr=outer.empno);

    EMPNO ENAME      JOB          DEPTNO

--------- ---------- --------- ---------

     7566 JONES      MANAGER          20

     7698 BLAKE      MANAGER          30

     7782 CLARK      MANAGER          10

     7788 SCOTT      ANALYST          20

     7839 KING       PRESIDENT        10

     7902 FORD       ANALYST          20

6 rows selected.  

Using the Not Exists operator:

To find all the departments those do not any employees.

select deptno, dname,loc from dept d

  where not exists(select 'x' from emp where deptno=d.deptno);

   DEPTNO DNAME          LOC

--------- -------------- -------------

       40 Operations     BOSTON

The correlated sub query can be an update or a delete also.