Subqueries By structure nested · multi-level · WITH

Nested Subquery

Subqueries inside subqueries. The innermost runs first; the result flows outward layer by layer. Useful for multi-step filtering — though CTEs read better past two levels.

What a nested subquery is

A nested subquery is simply a subquery placed inside another subquery — multiple levels of SELECT embedded in each other. The deepest one runs first, then the next level out uses its result, and so on up to the outer query.

It's the same machinery as a regular subquery — just stacked. People often use the words "nested subquery" and "subquery" interchangeably; the distinction worth keeping is that every nested subquery is a subquery, but not every subquery is nested. A single layer of nesting is just a "subquery" in casual language.

How execution stacks up

Nested vs correlated

Aspect	Nested	Correlated
Layers	Multiple SELECTs stacked	Could be a single layer
References outer columns?	No (independent levels)	Yes (depends on outer row)
Run order	Innermost first	Re-runs per outer row
Performance	Each level runs once	Inner runs N times

The two concepts can co-exist — a nested subquery can also be correlated to its outer context. But the labels emphasise different things.

Demo dataset — employees

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    email          VARCHAR(100),
    phone_number   VARCHAR(20),
    hire_date      DATE,
    job_id         INT,
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(100, 'Steven',    'King',      'steven.king@x.org',     '515.123.4567', '1987-06-17',  4, 24000.00, NULL,  9),
(101, 'Neena',     'Kochhar',   'neena.kochhar@x.org',   '515.123.4568', '1989-09-21',  5, 17000.00,  100,  9),
(115, 'Alexander', 'Khoo',      'alexander.khoo@x.org',  '515.127.4562', '1995-05-18', 13,  3100.00,  114,  3),
(116, 'Shelli',    'Baida',     'shelli.baida@x.org',    '515.127.4563', '1997-12-24', 13,  2900.00,  114,  3),
(200, 'Jennifer',  'Whalen',    'jennifer.whalen@x.org', '515.123.4444', '1987-09-17',  3,  4400.00,  101,  1),
(201, 'Michael',   'Hartstein', 'michael.h@x.org',       '515.123.5555', '1996-02-17', 10, 13000.00,  100,  2),
(202, 'Pat',       'Fay',       'pat.fay@x.org',         '603.123.6666', '1997-08-17', 11,  6000.00,  201,  2),
(203, 'Susan',     'Mavris',    'susan.mavris@x.org',    '515.123.7777', '1994-06-07',  8,  6500.00,  101,  4);

Example 1 — two levels deep

Find employees who earn more than the lowest-paid person in department 9.

SQL

SELECT first_name, salary
FROM   employees
WHERE  salary > (
    SELECT MIN(salary)
    FROM   employees
    WHERE  department_id = 9
);

Single nesting — one subquery inside one outer query. The innermost MIN runs first, returns a number, then the outer query filters.

Example 2 — three levels deep

Find employees whose salary is greater than the average salary of all employees who earn more than Jennifer Whalen.

SQL

SELECT first_name, salary
FROM   employees
WHERE  salary > (                                   -- outer
    SELECT AVG(salary)
    FROM   employees
    WHERE  salary > (                               -- middle
        SELECT salary
        FROM   employees
        WHERE  first_name = 'Jennifer'              -- innermost
          AND  last_name  = 'Whalen'
    )
);

Reads top to bottom but executes inside out: Jennifer's salary → average of those above her → outer filter.

Example 3 — nested with IN at each level

SQL

-- Employees in any department that contains a manager
SELECT first_name, department_id
FROM   employees
WHERE  department_id IN (
    SELECT DISTINCT department_id
    FROM   employees
    WHERE  employee_id IN (
        SELECT DISTINCT manager_id
        FROM   employees
        WHERE  manager_id IS NOT NULL
    )
);

Practical limits

The SQL standard allows up to 255 nesting levels in WHERE — far more than anyone should write.
By the third level, readability suffers. If a query needs four or more levels, consider using a CTE (WITH ... AS) to give each layer a name.
Each level of nesting forces the optimizer to think harder; deeply nested queries can hide performance issues.

💡 CTEs are nicer than deep nesting. Rewriting the three-level example above with WITH makes the intent obvious: WITH jennifer AS (...), above_jennifer AS (...) SELECT .... Each step is named and tested independently.

Recap

A nested subquery is a subquery inside another subquery, two or more layers deep.
Innermost runs first; each level passes its result outward.
Different from a correlated subquery — nested doesn't necessarily reference outer columns.
Beyond two or three levels, switch to CTEs (WITH) for readability.