Subqueries

A subquery is a SELECT statement written inside another SQL statement. The inner query runs first, produces a result, and that result is used by the outer query. Subqueries are one of the most powerful tools in SQL for expressing complex logic in a readable, step-by-step way.

What is a Subquery and When Should You Use One?

Think of a subquery like a helper calculation. Instead of doing two separate queries and manually connecting the results, you embed the helper query directly inside the main query.

Without a subquery (two separate queries):

-- Step 1: find the average salary
SELECT AVG(salary) FROM employees;   -- result: 77500

-- Step 2: manually use that number
SELECT name, salary FROM employees WHERE salary > 77500;

With a subquery (one query):

-- The inner query runs first and its result is used in WHERE
SELECT name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

Much cleaner — and it works even if the average changes tomorrow, because it is calculated fresh each time.

Tables Used in This Chapter

employees:

orders:

customers:

Single-Row Subquery

A single-row subquery returns exactly one row and one column (a scalar value). Use it with the standard comparison operators: =, >, <, >=, <=, <>.

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

The inner query SELECT AVG(salary) FROM employees returns one number: 77500. The outer query then uses that as the threshold.

Result:

-- Employee with the single highest salary
SELECT name, salary
FROM   employees
WHERE  salary = (SELECT MAX(salary) FROM employees);
-- Result: Frank, 95000

-- Employees hired after the most recently hired manager
SELECT name, hire_date
FROM   employees
WHERE  hire_date > (
    SELECT MAX(hire_date)
    FROM   employees
    WHERE  manager_id IS NULL   -- subquery: find when the last manager was hired
);

Multi-Row Subquery — IN, NOT IN, ANY, ALL

When the inner query returns multiple rows, use multi-row operators.

IN — value matches any row in the subquery result

-- Employees in departments where the average salary exceeds 80000
SELECT name, dept
FROM   employees
WHERE  dept IN (
    SELECT dept
    FROM   employees
    GROUP BY dept
    HAVING AVG(salary) > 80000
);
-- Inner query returns: ('IT') — only IT has avg salary > 80000
-- Outer query: employees where dept is in that list

Result:

-- Customers who have placed at least one order
SELECT name
FROM   customers
WHERE  customer_id IN (
    SELECT DISTINCT customer_id FROM orders
);
-- Inner query returns: (1, 2)
-- Result: Alice and Bob (Carol has no orders)

NOT IN — value does not match any row

-- Customers who have NEVER placed an order
SELECT name
FROM   customers
WHERE  customer_id NOT IN (
    SELECT customer_id FROM orders
);
-- Result: Carol (customer_id 3 is not in the orders table)

ANY — comparison is true for at least one row

-- Employees earning more than at least one person in the HR department
SELECT name, salary
FROM   employees
WHERE  salary > ANY (
    SELECT salary FROM employees WHERE dept = 'HR'
);
-- HR salaries are: 60000, 55000
-- ANY: salary > 55000 (the minimum HR salary)
-- Same as: salary > (SELECT MIN(salary) FROM employees WHERE dept = 'HR')

ALL — comparison is true for every row

-- Employees earning more than ALL employees in the HR department
SELECT name, salary
FROM   employees
WHERE  salary > ALL (
    SELECT salary FROM employees WHERE dept = 'HR'
);
-- HR salaries are: 60000, 55000
-- ALL: salary > 60000 (the maximum HR salary)
-- Same as: salary > (SELECT MAX(salary) FROM employees WHERE dept = 'HR')

Quick reference:

Subquery in FROM Clause — Derived Table / Inline View

A subquery in the FROM clause acts as a temporary table (also called a derived table or inline view). You must give it an alias.

-- Calculate average salary per department, then filter that summary
SELECT dept, avg_sal
FROM (
    SELECT dept,
           ROUND(AVG(salary), 0) AS avg_sal
    FROM   employees
    GROUP BY dept
) dept_summary                    -- alias is required
WHERE avg_sal > 70000
ORDER BY avg_sal DESC;

The inner query creates a temporary result set called dept_summary. The outer query then treats it like a regular table and applies a WHERE filter.

Result:

Why use a derived table?

You cannot filter on aggregate results in WHERE (aggregates belong in HAVING). But by wrapping the aggregation in a subquery and treating it as a derived table, you can apply any filter you want:

-- Find the top-earning department summary, then rank it
SELECT dept_name,
       avg_salary,
       RANK() OVER (ORDER BY avg_salary DESC) AS salary_rank
FROM (
    SELECT dept AS dept_name,
           ROUND(AVG(salary), 0) AS avg_salary
    FROM   employees
    GROUP BY dept
) dept_stats;

Subquery in SELECT Clause — Scalar Subquery

A scalar subquery in the SELECT clause returns a single value for every row of the outer query. This is useful for computing per-row comparisons or lookups.

-- Show each employee's salary vs the company average
SELECT name,
       salary,
       (SELECT ROUND(AVG(salary), 0) FROM employees) AS company_avg,
       salary - (SELECT ROUND(AVG(salary), 0) FROM employees) AS diff_from_avg
FROM   employees;

Result:

Correlated Subquery — References the Outer Query

A correlated subquery is one that references a column from the outer query. Because it depends on the current row being processed, it is re-executed once for each row in the outer query.

-- Find the highest earner in each department
-- The inner query references e1.dept from the outer query
SELECT name, dept, salary
FROM   employees e1
WHERE  salary = (
    SELECT MAX(salary)
    FROM   employees e2
    WHERE  e2.dept = e1.dept    -- reference to outer query's current row
);

How this executes, step by step:

1. Outer query starts processing row 1: Alice, HR, 60000
2. Inner query runs: SELECT MAX(salary) FROM employees WHERE dept = 'HR' → returns 60000
3. Outer WHERE condition: 60000 = 60000 → TRUE, Alice is included
4. Move to row 2: Bob, IT, 85000
5. Inner query runs: SELECT MAX(salary) FROM employees WHERE dept = 'IT' → returns 95000
6. Outer WHERE condition: 85000 = 95000 → FALSE, Bob is excluded
7. Continue for all rows...

Result:

-- Employees earning above their own department's average
SELECT name, dept, salary
FROM   employees e1
WHERE  salary > (
    SELECT AVG(salary)
    FROM   employees e2
    WHERE  e2.dept = e1.dept    -- each row uses its own department's average
);

EXISTS / NOT EXISTS

EXISTS tests whether a subquery returns any rows at all. As soon as the subquery finds one matching row, it stops searching and returns TRUE. This makes it more efficient than IN when checking for existence.

-- Customers who have placed at least one order
SELECT c.name
FROM   customers c
WHERE  EXISTS (
    SELECT 1               -- the value returned doesn't matter — just need any row
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
);

The inner query only needs to find one match per customer to return TRUE. It immediately stops looking, even if there are 100 orders.

Note: The SELECT list in the subquery can be SELECT 1, SELECT *, or any expression — it does not matter, because EXISTS only cares whether any rows were returned, not what was in them.

NOT EXISTS — the anti-join alternative

-- Customers who have NEVER placed an order
SELECT c.name
FROM   customers c
WHERE  NOT EXISTS (
    SELECT 1
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
);
-- Result: Carol

EXISTS vs IN — When to Prefer Each

-- IN: simpler to read, good for small lists
SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- EXISTS: better for large subqueries (stops at first match)
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Choose EXISTS over IN when:

• The subquery returns a large number of rows
• You are checking for the existence of a related record (not comparing values)
• The subquery columns could contain NULLs (EXISTS handles NULLs safely; NOT IN does not)

Nesting Subqueries

Subqueries can be nested multiple levels deep, though deeply nested queries become hard to read:

-- Employees whose salary is above the average of the top-3 departments
SELECT name, salary
FROM   employees
WHERE  dept IN (
    SELECT dept
    FROM (
        -- Subquery 2: top 3 departments by average salary
        SELECT dept, AVG(salary) AS avg_sal
        FROM   employees
        GROUP BY dept
        ORDER BY avg_sal DESC
        FETCH FIRST 3 ROWS ONLY
    ) top_depts   -- alias for inner derived table
)
AND salary > (SELECT AVG(salary) FROM employees);

When subqueries get this deep, a CTE (WITH clause) is almost always clearer.

Converting Correlated Subqueries to JOINs

Correlated subqueries are often rewritable as JOINs, which the query optimiser can usually execute more efficiently:

-- Correlated subquery version (slower on large tables)
SELECT name, dept, salary
FROM   employees e1
WHERE  salary > (
    SELECT AVG(salary)
    FROM   employees e2
    WHERE  e2.dept = e1.dept
);

-- JOIN version (often faster)
SELECT e.name, e.dept, e.salary
FROM   employees e
JOIN (
    SELECT dept, AVG(salary) AS dept_avg
    FROM   employees
    GROUP BY dept
) dept_avgs ON e.dept = dept_avgs.dept
WHERE  e.salary > dept_avgs.dept_avg;

-- CTE version (most readable)
WITH dept_avgs AS (
    SELECT dept, AVG(salary) AS dept_avg
    FROM   employees
    GROUP BY dept
)
SELECT e.name, e.dept, e.salary
FROM   employees e
JOIN   dept_avgs d ON e.dept = d.dept
WHERE  e.salary > d.dept_avg;

Subquery vs JOIN vs CTE — Decision Guide

-- Practical example: all three approaches for "employees earning above dept average"

-- 1. Correlated subquery (readable, can be slow)
SELECT name, dept, salary FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e1.dept);

-- 2. JOIN with derived table (faster)
SELECT e.name, e.dept, e.salary
FROM employees e
JOIN (SELECT dept, AVG(salary) AS avg FROM employees GROUP BY dept) d
  ON e.dept = d.dept
WHERE e.salary > d.avg;

-- 3. CTE (cleanest for complex logic)
WITH dept_avg AS (
    SELECT dept, AVG(salary) AS avg_sal
    FROM   employees
    GROUP BY dept
)
SELECT e.name, e.dept, e.salary
FROM   employees  e
JOIN   dept_avg   d ON e.dept = d.dept
WHERE  e.salary > d.avg_sal;

Common Errors

Interview Corner

-- IN: fetches all department_ids first, then filters employees
SELECT last_name FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- EXISTS: stops at first match per employee row
SELECT last_name FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.department_id = e.department_id AND d.location_id = 1700
);

-- Correlated: subquery references e.department_id from the outer query
SELECT last_name, salary
FROM   employees e
WHERE  salary > (
    SELECT AVG(salary)
    FROM   employees
    WHERE  department_id = e.department_id   -- correlated column
);

Related Topics

• Joins — many subquery patterns can be rewritten as JOINs for better performance
• Aggregate Functions — scalar subqueries often use aggregates to produce a single value
• CTEs & Procedures — CTEs as a readable alternative to deeply nested subqueries
• Window Functions — window functions often replace correlated subquery patterns