Chapter 15: Subqueries and Derived Tables

Subqueries allow you to nest one query inside another. They return values or tables that the outer query can use for filtering, comparison, or joining. This chapter explores scalar, row, and table subqueries, explains EXISTS, ANY, and ALL, and concludes with Common Table Expressions (CTEs) for clarity and reuse.

Scalar, row, and table subqueries

A subquery is enclosed in parentheses and can appear almost anywhere an expression or table name could. The outer query treats the subquery’s result as a single value, a row, or a full virtual table.

Scalar subqueries (single value)

A scalar subquery returns exactly one value (one row and one column). Use it wherever a literal could appear.

-- Find customers whose total spend exceeds the average order total
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE (
  SELECT SUM(oi.quantity * oi.unit_price)
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.customer_id = c.customer_id
) > (
  SELECT AVG(total)
  FROM (
    SELECT SUM(oi.quantity * oi.unit_price) AS total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    GROUP BY o.order_id
  ) AS sub
);

Row subqueries (multiple columns, one row)

A row subquery returns one row with several columns. It can be compared to another row using tuple syntax.

-- Compare composite (city, state) values
SELECT c1.customer_id, c1.customer_name
FROM customers AS c1
WHERE (c1.city, c1.state) = (
  SELECT c2.city, c2.state
  FROM customers AS c2
  WHERE c2.customer_id = 42
);

Table subqueries (derived tables)

When a subquery returns a full result set, it can be treated as a table source. These are often called derived tables or inline views.

-- Derived table to compute revenue per customer
SELECT t.customer_id, t.revenue
FROM (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  GROUP BY o.customer_id
) AS t
WHERE t.revenue > 1000;

Always give derived tables an alias. Without one, most databases reject the query.

Correlated subqueries

A correlated subquery references columns from its outer query. It executes once per outer row, not just once overall.

-- Customers whose latest order is within 30 days
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.order_date > CURRENT_DATE - INTERVAL '30' DAY
);

EXISTS, ANY, ALL

These operators compare outer query rows to subquery results. They provide powerful logic for inclusion and comparison tests.

EXISTS

EXISTS tests whether a subquery returns at least one row. It stops scanning as soon as one match is found.

-- Customers who have ever placed an order
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

EXISTS ignores the subquery’s actual values; only presence matters.

NOT EXISTS

To find outer rows with no matches, invert the condition.

-- Customers with no orders
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

ANY and ALL

ANY (or SOME) compares a value to each subquery result and succeeds if any comparison is true. ALL requires all comparisons to be true.

-- Orders larger than the average for any customer in Germany
SELECT o.order_id, o.customer_id, o.total_amount
FROM orders AS o
WHERE o.total_amount > ANY (
  SELECT AVG(oi.quantity * oi.unit_price)
  FROM orders AS g
  JOIN order_items AS oi ON oi.order_id = g.order_id
  JOIN customers AS c ON c.customer_id = g.customer_id
  WHERE c.country = 'Germany'
  GROUP BY g.customer_id
);

-- Products cheaper than all others in their category
SELECT p.product_id, p.product_name
FROM products AS p
WHERE p.unit_price < ALL (
  SELECT p2.unit_price
  FROM products AS p2
  WHERE p2.category_id = p.category_id
    AND p2.product_id <> p.product_id
);

Common Table Expressions

Common Table Expressions (CTEs) provide named, reusable subqueries that improve readability. They are introduced with WITH and can be referenced by later queries in the same statement.

Basic CTE syntax

WITH totals AS (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  GROUP BY o.customer_id
)
SELECT c.customer_name, t.revenue
FROM customers AS c
JOIN totals AS t ON t.customer_id = c.customer_id
WHERE t.revenue > 1000;

A CTE acts like a temporary named table visible only to the main query (and to other CTEs declared after it).

Recursive CTEs

Recursive CTEs reference themselves to walk hierarchies or compute sequences. They use UNION ALL between an anchor member and a recursive member.

-- Organization hierarchy
WITH RECURSIVE org_chart AS (
  SELECT employee_id, manager_id, employee_name, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.employee_name, oc.level + 1
  FROM employees AS e
  JOIN org_chart AS oc ON e.manager_id = oc.employee_id
)
SELECT *
FROM org_chart
ORDER BY level, employee_name;

CTEs vs derived tables

CTEs are easier to read and maintain, but engines may not always materialize them as temporary results. Derived tables can be faster in some cases. Most optimizers treat them equivalently unless hints or recursion are involved.

-- Derived table version of the same revenue query
SELECT c.customer_name, t.revenue
FROM customers AS c
JOIN (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  GROUP BY o.customer_id
) AS t ON t.customer_id = c.customer_id;

Dialect notes

Subqueries, derived tables, and CTEs all provide ways to compose complex logic. Choose the simplest structure that expresses your intent clearly while remaining efficient for the database engine.