Subqueries

Some questions cannot be answered in one pass. Which products cost more than the average product? Which customers have a pending order? Which items are priced above their own category's average?

Each one needs a second query to feed the first. A subquery is a SELECT nested inside another statement, and it is the first tool for multi-step thinking in SQL.

Scalar subqueries: one value#

A subquery that returns exactly one row and one column can stand wherever a value can.

SELECT name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products)
ORDER BY unit_price DESC;

            name             | unit_price
-----------------------------+------------
 Standing Desk               |     499.00
 Ergonomic Chair             |     349.00
 27-inch Monitor             |     289.00
 Noise-Cancelling Headphones |     199.00
(4 rows)

The inner query runs once and produces the average (147.77 here). The outer query compares every product to it.

If a scalar subquery returns more than one row, PostgreSQL stops you:

ERROR:  more than one row returned by a subquery used as an expression

Zero rows give NULL, and a comparison against NULL matches nothing. Both failures happen later, when the data changes shape, so know which one you are relying on.

List subqueries: IN#

A subquery returning one column can feed IN.

SELECT name
FROM products
WHERE product_id IN (
    SELECT oi.product_id
    FROM order_items oi
    JOIN orders o ON o.order_id = oi.order_id
    WHERE o.status = 'cancelled'
);

That returns the products that appeared in a cancelled order, which is just the Ergonomic Chair here. ANY and ALL generalize the idea. unit_price > ALL (SELECT unit_price FROM products WHERE category = 'Accessories') means "more expensive than every accessory".

Derived tables: a subquery in FROM#

A subquery in FROM behaves like a temporary table for the duration of the statement. PostgreSQL requires an alias.

SELECT c.full_name, s.total
FROM (
    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.customer_id
) AS s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.total > 500
ORDER BY s.total DESC;

 full_name  | total
------------+--------
 Farhan Ali | 882.00
 Aisha Khan | 726.00
 Grace Lin  | 607.50

Aggregating inside the derived table first, then joining, is the standard fix for the fan-out problem. The join sees one row per customer, so nothing multiplies.

Correlated subqueries#

A correlated subquery refers to a column of the outer query. Conceptually it re-runs for every outer row.

Products priced above the average of their own category:

SELECT p.name, p.category, p.unit_price
FROM products p
WHERE p.unit_price > (
    SELECT AVG(p2.unit_price)
    FROM products p2
    WHERE p2.category = p.category
)
ORDER BY p.category, p.name;

        name         |  category   | unit_price
---------------------+-------------+------------
 Mechanical Keyboard | Accessories |      89.00
 Standing Desk       | Furniture   |     499.00
 Desk Lamp           | Office      |      24.00

For each product, the inner query computes that product's category average and the outer WHERE compares. Mechanical Keyboard (89) beats the Accessories average of about 49.38. Standing Desk (499) beats the Furniture average of 424.

 outer row: Standing Desk (Furniture, 499)
     |
     +--> inner query runs with category = 'Furniture'
              AVG(349, 499) = 424
     |
     +--> 499 > 424  -> keep the row

Beware that "conceptually per row" is not always "actually per row". The planner often rewrites a correlated subquery into a join. When it cannot, it shows up in the plan as a SubPlan executed once per outer row, and that is where slowness comes from on big tables.

EXISTS#

EXISTS asks only whether the subquery returns any row. It does not care about the values, so the select list is irrelevant. Convention is SELECT 1.

SELECT c.full_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id AND o.status = 'pending'
)
ORDER BY c.full_name;

  full_name
-------------
 Farhan Ali
 Isla Murphy

EXISTS stops at the first match. It also never returns duplicates for the outer row, unlike a join, which would repeat Farhan Ali if he had two pending orders. When you want to filter by "has at least one related row", EXISTS states that most directly.

IN versus EXISTS, and the NOT IN trap#

For positive checks, IN and EXISTS usually plan identically, and modern PostgreSQL converts both to a semi-join. Choose whichever reads better.

For negation they are not interchangeable. NOT IN returns nothing as soon as the subquery yields one NULL, as the operators lesson showed with the employee-manager query. NOT EXISTS is immune. Use NOT EXISTS.

-- Employees who manage nobody
SELECT e.full_name
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.employee_id)
ORDER BY e.full_name;

Six rows: Daniel Okafor, Grace Thompson, Kenji Watanabe, Lena Fischer, Rahul Verma, Sofia Martins.

Subqueries in the SELECT list#

SELECT c.full_name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders
FROM customers c;

It works, and each scalar subquery is a candidate for re-running per row. A LEFT JOIN with GROUP BY usually expresses the same thing more clearly and gives the planner more room. Use the select-list form for a single lookup, not for five columns of it.

Choosing: subquery, join or CTE#

• Need columns from the other table in the output? Join.
• Only filtering by existence or by a computed value? EXISTS or a scalar subquery.
• Need to compute something first, then use it more than once, or the nesting reaches three levels? A CTE, which the next module covers. It gives the intermediate step a name and flattens the reading order.

Compare how the same logic reads when nested:

SELECT ... FROM (SELECT ... FROM (SELECT ... ) a ) b WHERE ...

Nesting forces the reader to start in the middle and read outward. A CTE reads top to bottom.

Common mistakes#

Scalar subquery returning many rows. It works in testing when the data happens to be unique, then fails in production.

NOT IN over a nullable column. Use NOT EXISTS.

Forgetting the alias on a derived table. PostgreSQL requires one (subquery in FROM must have an alias).

Correlated subqueries on big tables without an index on the correlation column. The inner query scans the table once per outer row.

Using a subquery where a join is clearer. If you need columns from the inner table, you wanted a join.

Practice#

1. List products priced above the overall average. You should get 4 rows, starting with Standing Desk.
2. Find customers whose total non-cancelled spend exceeds the average spend per buying customer. You should see Farhan Ali, Aisha Khan and Grace Lin. The average is 392.4375.
3. List customers who have placed an order for the 27-inch Monitor, using EXISTS. Then do it again with a join and explain whether duplicates can appear.
4. Find the employee earning the highest salary in each department using a correlated subquery. Finance should return Rahul Verma.
5. Rewrite the "products above their category average" query with a derived table and a join instead of a correlated subquery. Which is easier to read?