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 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:
- The search does not continue in the inner query
- The condition is flagged true.
- If a sub query row value is not found:
- The condition is flagged false.
- 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.
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.