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.