Correlated Subquery

A correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause.

For example, a query to list employees whose salary is more than their department’s average:

SQL> select oe.deptno, oe.ename, oe.sal
  2  from   emp oe
  3  where  sal >
  4            -- Correlated subquery starts
  5               (select avg(ie.sal) from emp ie
  6                where ie.deptno = oe.deptno)
  7            -- Correlated subquery ends
  8  order by oe.deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 JONES            2975
        20 SCOTT            3000
        20 FORD             3000
        30 ALLEN            1600
        30 BLAKE            2850

6 rows selected.

In this query, the inner query is a correlated subquery: it references department number of the outer query. The outer query uses the correlated subquery to calculate the average salary for each department.

While a simple subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row.

The outer query (also called parent query) can be a SELECT, UPDATE or DELETE.

Syntax of the Correlated Subquery

A look at the syntax in case of difference DML operations: SELECT, UPDATE and DELETE.

SELECT

SELECT select_list
    FROM table1 t_alias1
    WHERE expr operator
        (SELECT column_list
            FROM table2 t_alias2
            WHERE t_alias1.column
               operator t_alias2.column); 

UPDATE

UPDATE table1 t_alias1
    SET column =
        (SELECT expr
            FROM table2 t_alias2
            WHERE t_alias1.column = t_alias2.column); 

DELETE

DELETE FROM table1 t_alias1
    WHERE column operator
        (SELECT expr
            FROM table2 t_alias2
            WHERE t_alias1.column = t_alias2.column); 

For further reading

• Understanding subqueries and their types – An overview of subqueries of various types supported in Oracle SQL.
• What is a scalar subquery? – A closer look at a particular type of subquery.
• The difference between DECODE and CASE – two keywords that do almost the same thing: how do they compare?