Oracle Database Administration: The Essential Refe

Correlated Subqueries

A correlated subquery is a subquery that is executed for each row of the base table. An example of a correlated subquery in a WHERE clause is shown here:

UPDATE emp e
SET e.deptno = (SELECT deptno FROM dept WHERE dname = 'SALES')
WHERE NOT EXISTS
(SELECT 'X' FROM dept d
WHERE  e.deptno = d.deptno);

In this example, all employees that do not have a department are assigned to the Sales department. The key characteristic of a correlated subquery is that it is called for each row retrieved.