Subqueries

A subquery is a SELECT nested inside another statement. Where it appears decides what it must return: one value, a list, or a whole table. The examples share two tables.

-- setup.sql
CREATE TABLE customers (id INT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    category    TEXT NOT NULL,
    amount      NUMERIC(8, 2) NOT NULL
);
INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cai'), (4, 'Dee');
INSERT INTO orders VALUES
    (101, 1, 'books', 40.00), (102, 1, 'games', 120.00), (103, 1, 'books', 25.00),
    (104, 2, 'games', 60.00), (105, 2, 'music', 15.00),
    (106, 3, 'books', 90.00);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 6

Scalar Subqueries

A subquery that returns exactly one value can go anywhere a value can.

-- scalar.sql
SELECT id, amount,
       (SELECT round(avg(amount), 2) FROM orders) AS overall_avg,
       amount - (SELECT avg(amount) FROM orders) > 0 AS above_avg
FROM orders
ORDER BY id;

$ psql -d sqldemo -f scalar.sql
 id  | amount | overall_avg | above_avg 
-----+--------+-------------+-----------
 101 |  40.00 |       58.33 | f
 102 | 120.00 |       58.33 | t
 103 |  25.00 |       58.33 | f
 104 |  60.00 |       58.33 | t
 105 |  15.00 |       58.33 | f
 106 |  90.00 |       58.33 | t
(6 rows)

The inner query does not depend on the outer row, so PostgreSQL computes the average once and reuses it. t and f are how psql prints true and false.

When a Scalar Subquery Returns Too Much

The same syntax fails the moment the subquery returns more than one row.

-- scalar_error.sql
SELECT name,
       (SELECT amount FROM orders o WHERE o.customer_id = c.id) AS amount
FROM customers c;

$ psql -d sqldemo -f scalar_error.sql
psql:scalar_error.sql:4: ERROR:  more than one row returned by a subquery used as an expression

Ana has three orders, so “Ana’s amount” is ambiguous and the whole query fails, not just Ana’s row. Decide what you mean: sum(amount), the latest order with ORDER BY ... LIMIT 1, or a join that returns one row per order.

IN: Matching Against a List

IN (subquery) keeps outer rows whose value appears in the subquery’s result.

-- in.sql
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE category = 'books')
ORDER BY name;

$ psql -d sqldemo -f in.sql
 name 
------
 Ana
 Cai
(2 rows)

Ana ordered two books but appears once. IN asks “is this id in the list?”, so duplicates in the list do not multiply rows, which a plain join would do.

EXISTS and NOT EXISTS

EXISTS is true when the subquery returns at least one row. It is the natural way to ask “who has never ordered?”

-- not_exists.sql
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

$ psql -d sqldemo -f not_exists.sql
 name 
------
 Dee
(1 row)

The subquery mentions c.id from the outer query, which makes it correlated: it is checked per customer. SELECT 1 is a convention; EXISTS never looks at the selected values, only at whether a row exists.

ALL: Comparing Against Every Row

> ALL (subquery) is true when the value beats every row the subquery returns. Its partner ANY is true when it beats at least one, and = ANY means the same as IN.

-- all.sql
SELECT id, category, amount
FROM orders
WHERE amount > ALL (SELECT amount FROM orders WHERE category = 'books')
ORDER BY id;
SELECT count(*) AS orders_above_every_toy
FROM orders
WHERE amount > ALL (SELECT amount FROM orders WHERE category = 'toys');
SELECT name AS never_ordered
FROM customers
WHERE id <> ALL (SELECT customer_id FROM orders);

$ psql -d sqldemo -f all.sql
 id  | category | amount 
-----+----------+--------
 102 | games    | 120.00
(1 row)

 orders_above_every_toy 
------------------------
                      6
(1 row)

 never_ordered 
---------------
 Dee
(1 row)

The most expensive books order is 90.00, so only the 120.00 games order beats all of them. The second query is the surprise: there are no toys orders, and every one of the 6 orders counts as greater than all of them. ALL over an empty set is true, because there’s no row to fail the test, so check that the subquery can actually return rows. The third query shows that NOT IN is shorthand for <> ALL: Dee is the only customer whose id differs from every customer_id in orders. That’s also why NOT IN breaks on NULLs. A single NULL in the list makes <> NULL unknown for every row.

Derived Tables: Subqueries in FROM

A subquery in FROM acts as a temporary table. You need one to aggregate an aggregate.

-- derived.sql
SELECT round(avg(total), 2) AS avg_customer_spend
FROM (
    SELECT customer_id, sum(amount) AS total
    FROM orders
    GROUP BY customer_id
) AS per_customer;

$ psql -d sqldemo -f derived.sql
 avg_customer_spend 
--------------------
             116.67
(1 row)

The inner query produces one total per customer (185, 75, 90), and the outer query averages those three totals. Averaging the six orders directly gives 58.33, as scalar.sql showed, because Ana’s three orders would count three times. PostgreSQL 16 and later accept a derived table without an alias, but older versions and most other databases require one (AS per_customer), so write it anyway.

Correlated Subqueries

This one compares each order with the average of the same customer’s orders.

-- correlated.sql
SELECT o.id, o.customer_id, o.amount
FROM orders o
WHERE o.amount > (
    SELECT avg(o2.amount) FROM orders o2 WHERE o2.customer_id = o.customer_id
)
ORDER BY o.id;

$ psql -d sqldemo -f correlated.sql
 id  | customer_id | amount 
-----+-------------+--------
 102 |           1 | 120.00
 104 |           2 |  60.00
(2 rows)

Ana’s average is 61.67, so only her 120.00 order beats it; Ben’s is 37.50, so his 60.00 order qualifies. Cai has a single order, which cannot be greater than its own average. The subquery runs per outer row, which is fine here and costly on millions of rows.

LATERAL: Top N per Group

A LATERAL subquery may refer to tables listed before it, so it runs once per outer row. That makes “each customer’s two largest orders” a single query.

-- lateral.sql
SELECT c.name, top.id, top.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.id, o.amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.amount DESC
    LIMIT 2
) AS top
ORDER BY c.name, top.amount DESC;

$ psql -d sqldemo -f lateral.sql
 name | id  | amount 
------+-----+--------
 Ana  | 102 | 120.00
 Ana  | 101 |  40.00
 Ben  | 104 |  60.00
 Ben  | 105 |  15.00
 Cai  | 106 |  90.00
(5 rows)

Ana has three orders but shows two, because LIMIT 2 applies per customer. Dee has no orders, and CROSS JOIN LATERAL drops customers whose subquery returns nothing; use LEFT JOIN LATERAL (...) AS top ON true to keep them.

Subquery, JOIN, or CTE?

Use a scalar subquery for a single value, EXISTS/NOT EXISTS for “has any” questions, a join when you need columns from both tables, and a CTE (the CTEs lesson) when a derived table grows big enough that naming it makes the query readable.

Practice

1. List customers who have ordered from more than one category.

-- practice_multi_category.sql
SELECT name FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
    GROUP BY customer_id
    HAVING count(DISTINCT category) > 1
)
ORDER BY name;

$ psql -d sqldemo -f practice_multi_category.sql
 name 
------
 Ana
 Ben
(2 rows)

2. Show each customer’s name with their number of orders, using a correlated scalar subquery, including customers with zero orders.

-- practice_order_counts.sql
SELECT name,
       (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c
ORDER BY c.id;

$ psql -d sqldemo -f practice_order_counts.sql
 name | orders 
------+--------
 Ana  |      3
 Ben  |      2
 Cai  |      1
 Dee  |      0
(4 rows)

3. Change lateral.sql to show each customer’s single largest order, keeping Dee.

-- practice_lateral_left.sql
SELECT c.name, top.amount
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.amount FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.amount DESC LIMIT 1
) AS top ON true
ORDER BY c.name;

$ psql -d sqldemo -f practice_lateral_left.sql
 name | amount 
------+--------
 Ana  | 120.00
 Ben  |  60.00
 Cai  |  90.00
 Dee  |       
(4 rows)