How to Write Correlated Subqueries in Oracle SQL

Some questions compare each row with its own group: employees who earn more than the average of their department, or departments shown with their own headcount. An ordinary subquery is evaluated once for the whole statement, so it cannot answer them. A correlated subquery can, because it refers to a column of the outer query and is evaluated, conceptually, once for each outer row.

This guide shows correlated subqueries in WHERE and in the select list, how to read them, and an analytic alternative.

Code for This Guide

The main examples are in the examples/subqueries and examples/basic-elements folders of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

What Makes a Subquery Correlated

A subquery is correlated when it uses a column from a table of the outer query. Give both tables aliases, because the same table often appears on both levels:

Syntax:

select ... from table outer_alias
where  expr operator (select ... from table inner_alias
                      where  inner_alias.col = outer_alias.col)

Read it row by row: for the current outer row, run the subquery with that row's values, then test the condition. Oracle does not have to execute it literally that way; the optimizer often rewrites a correlated subquery into a join, so it need not be slow.

Compare Each Row with Its Own Group

This query finds employees in departments 30 and 40 who earn more than the average of their own department. The subquery uses e.department_id from the outer row, so each employee is compared with a different average.

Example:

-- employees who earn more than the average of their own department
select e.department_id, e.last_name, e.salary
from   employees e
where  e.salary > (select avg(x.salary) from employees x
                   where  x.department_id = e.department_id)
and    e.department_id in (30, 40)
order  by e.department_id, e.salary desc;

Output:

   DEPARTMENT_ID LAST_NAME       SALARY
________________ ____________ _________
              30 Rahman           22000
              30 Taylor           11800
              30 Okafor           11600
              40 Evans            34000
              40 Kapoor           21000
              40 Thompson         14500

6 rows selected.

The aliases are what make this work. Inside the subquery, x is the employee being averaged and e the employee being tested. Without the condition x.department_id = e.department_id, the subquery would compute the company-wide average instead.

Correlated Subqueries in the Select List

A correlated scalar subquery in the select list computes a value for each row of the outer query. Here each department gets its own headcount and top salary.

Example:

select d.department_name,
       (select count(*) from employees e where e.department_id = d.department_id) as staff,
       (select max(salary) from employees e
        where  e.department_id = d.department_id) as top_pay
from   departments d
where  d.department_id in (20, 30, 90);

Output:

DEPARTMENT_NAME         STAFF    TOP_PAY
____________________ ________ __________
Flight Operations          12      27000
Cabin Services             11      22000
Customer Care               4      12000

A scalar subquery must return at most one row; if it returns none, the value is NULL. That differs from a join, which would drop a department with no employees unless you wrote an outer join. With count(*), a department without staff simply shows 0.

The Analytic Alternative

The comparison with a group average can also be written with an analytic function, which computes the average for every row in one pass over the table. It returns the same rows as the correlated version.

Example:

select department_id, last_name, salary
from   (select e.department_id, e.last_name, e.salary,
               avg(e.salary) over (partition by e.department_id) as dept_avg
        from   employees e
        where  e.department_id in (30, 40))
where  salary > dept_avg
order  by department_id, salary desc;

Output:

   DEPARTMENT_ID LAST_NAME       SALARY
________________ ____________ _________
              30 Rahman           22000
              30 Taylor           11800
              30 Okafor           11600
              40 Evans            34000
              40 Kapoor           21000
              40 Thompson         14500

6 rows selected.

The analytic version is often faster on large tables, because it reads the table once. The correlated version reads more naturally and works in more places, such as UPDATE and DELETE statements.

Things to Know

• Always qualify columns with aliases in a correlated subquery. An unqualified column resolves to the innermost table that has it, which can silently turn a correlated subquery into an uncorrelated one, or the reverse.
• Correlated subqueries work in UPDATE and DELETE too, for example setting a column to a value looked up per row.
• Correlated EXISTS and NOT EXISTS subqueries test whether matching rows exist at all, and are the usual way to write semi-joins and anti-joins.

Related Guides

• How to Use Subqueries in Oracle SQL
• Oracle SQL INNER JOIN: Complete Guide to Matching Records Between Tables

Conclusion

A correlated subquery refers to a column of the outer query and is evaluated for each outer row, which lets you compare a row with its own group or compute a value per row in the select list. Qualify every column with an alias, remember that a scalar subquery with no rows returns NULL, and consider an analytic function when the same comparison runs over a large table.