Nested Queries & Sub-queries in SQL

The Story That Explains Sub-queries

A sub-query (also called an inner query or inner select) is a complete SELECT statement enclosed in parentheses and embedded inside another statement. The outer query uses whatever the inner query returns. This tutorial covers every flavour — scalar, multi-row, correlated, EXISTS, derived tables, and true multi-level nesting — each grounded in one small dataset you can trace by hand.

The Sample Database We Will Use

Every example below uses these two tables. Keep them in view — tracing the numbers by hand is how sub-queries truly click.

The Three Shapes a Sub-query Can Return

Before writing one, you must know what a sub-query gives back. There are exactly three shapes, and each shape allows different operators around it.

Where a Sub-query Can Appear

Scalar Sub-query — A Single Value

The most common case: the inner query produces one value, and the outer query compares against it. Crucially, a non-correlated scalar sub-query runs exactly once, before the outer query begins filtering.

-- Employees earning more than the company average
SELECT name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

Sub-query in the SELECT List

A scalar sub-query can also sit in the SELECT list, producing an extra computed column for every row.

-- Show each salary alongside the company average and the gap
SELECT name,
       salary,
       (SELECT AVG(salary) FROM employees) AS company_avg,
       salary - (SELECT AVG(salary) FROM employees) AS diff
FROM   employees;

Multi-row Sub-queries — IN and NOT IN

When the inner query returns a list of values, use IN to keep rows whose value is in the list, or NOT IN to keep those that are not.

-- Employees who work in Engineering or Sales
SELECT name
FROM   employees
WHERE  dept_id IN (
         SELECT dept_id FROM departments
         WHERE dept_name IN ('Engineering', 'Sales')
       );

-- Departments that currently have NO employees
SELECT dept_name
FROM   departments
WHERE  dept_id NOT IN (SELECT dept_id FROM employees);

ANY and ALL — Comparing Against a List

ANY and ALL let you use comparison operators against a list of values. > ALL means "greater than every value" (i.e. greater than the maximum). > ANY means "greater than at least one" (i.e. greater than the minimum).

-- Earn more than EVERY Sales employee (> max of dept 20 = 58,000)
SELECT name, salary
FROM   employees
WHERE  salary > ALL (SELECT salary FROM employees WHERE dept_id = 20);

-- Earn more than AT LEAST ONE Sales employee (> min of dept 20 = 47,000)
SELECT name, salary
FROM   employees
WHERE  salary > ANY (SELECT salary FROM employees WHERE dept_id = 20);

Correlated Sub-queries — The Inner Query Re-runs Per Row

A correlated sub-query references a column from the outer query. Because it depends on the current outer row, it cannot run once — it re-executes for every outer row. The animation below shows the loop in action for the query "employees earning above their own department's average."

-- Employees earning more than the average of THEIR OWN department
SELECT name, dept_id, salary
FROM   employees e1
WHERE  salary > (
         SELECT AVG(salary)
         FROM   employees e2
         WHERE  e2.dept_id = e1.dept_id    -- the correlation
       );

EXISTS and NOT EXISTS

EXISTS returns true the moment the inner query finds its first matching row, then stops — it never cares how many rows match, only whether any do. This short-circuit makes it efficient for "is there at least one?" checks.

-- Departments that have at least one employee
SELECT dept_name
FROM   departments d
WHERE  EXISTS (
         SELECT 1 FROM employees e
         WHERE e.dept_id = d.dept_id
       );

-- Departments that have NO employees (immune to the NULL trap)
SELECT dept_name
FROM   departments d
WHERE  NOT EXISTS (
         SELECT 1 FROM employees e
         WHERE e.dept_id = d.dept_id
       );

Derived Tables — A Sub-query in FROM

When a sub-query returns a whole table, place it in the FROM clause, give it an alias, and query it like any other table. This is a derived table (or inline view).

-- Which departments have an average salary above 60,000?
SELECT dept_id, avg_sal
FROM   (
         SELECT dept_id, AVG(salary) AS avg_sal
         FROM   employees
         GROUP BY dept_id
       ) AS dept_avg
WHERE  avg_sal > 60000;

Nested Sub-queries — Queries Within Queries

A sub-query can itself contain another sub-query, several layers deep. The engine resolves the innermost query first, then works outward. Below, three levels cooperate to find the top earner in Engineering.

-- Highest-paid employee in the Engineering department
SELECT name, salary
FROM   employees
WHERE  dept_id = (SELECT dept_id FROM departments
                  WHERE dept_name = 'Engineering')
  AND  salary = (SELECT MAX(salary) FROM employees
                  WHERE dept_id = (SELECT dept_id FROM departments
                                   WHERE dept_name = 'Engineering'));

Sub-query vs JOIN, and IN vs EXISTS

The Same Question, Two Ways

-- Correlated sub-query (re-runs per row)
SELECT name, salary
FROM   employees e1
WHERE  salary > (SELECT AVG(salary) FROM employees e2
                  WHERE e2.dept_id = e1.dept_id);

-- Equivalent JOIN against a pre-aggregated derived table (computed once)
SELECT e.name, e.salary
FROM   employees e
JOIN   (SELECT dept_id, AVG(salary) AS avg_sal
        FROM employees GROUP BY dept_id) d
       ON e.dept_id = d.dept_id
WHERE  e.salary > d.avg_sal;

Common Mistakes

Practice Problems

Solve each using the sample tables before checking the approach.

-- Q4 solution: top earner(s) in each department
SELECT name, dept_id, salary
FROM   employees e1
WHERE  salary = (SELECT MAX(salary) FROM employees e2
                  WHERE e2.dept_id = e1.dept_id);

Golden Rules