Subqueries

A subquery is a SELECT inside another statement. You use one when the value you need to filter or compare against is itself the answer to a query.

"Products priced above average" is the clearest case. You cannot write WHERE unit_price > AVG(unit_price), because the average is a property of the whole table rather than of the row being tested. A subquery computes it first.

-- 1. Scalar: returns exactly one value, used like a constant
SELECT p.product_name, p.unit_price
FROM products AS p
WHERE p.unit_price > (SELECT AVG(unit_price) FROM products);

-- 2. List: returns one column of many values, used with IN
SELECT c.company_name
FROM customers AS c
WHERE c.customer_id IN (
    SELECT o.customer_id
    FROM orders AS o
    WHERE o.order_date >= '2026-01-01'
);

-- 3. Derived table: returns a whole result used in FROM, and must be aliased
SELECT order_totals.order_id, order_totals.goods_value
FROM (
    SELECT oi.order_id, SUM(oi.quantity * oi.unit_price) AS goods_value
    FROM order_items AS oi
    GROUP BY oi.order_id
) AS order_totals
WHERE order_totals.goods_value > 5000;

• A scalar subquery must return one row and one column. If it returns more, the statement fails at run time — which is why an unconstrained scalar subquery is risky in production code.
• The list form is readable and works well when the inner query returns a modest number of values. Watch for nulls in that list: the previous lesson on WHERE covered why NOT IN breaks when one appears.
• A derived table needs an alias in SQL Server and PostgreSQL. Without it you get a syntax error that does not obviously say "add a name".

A correlated subquery refers to a column from the outer query, so it cannot be computed once up front. Conceptually it runs again for each candidate row.

That makes it expressive and potentially expensive. Query optimisers frequently rewrite a correlated subquery into a join internally, so it is not automatically slow — but on a large outer table with an unindexed correlation column, it can be dramatically slower than the equivalent join.

SELECT
    c.company_name,
    (SELECT MAX(o.order_date)
     FROM orders AS o
     WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers AS c
ORDER BY last_order_date DESC;

• The inner query references c.customer_id, which belongs to the outer query. That reference is what makes it correlated.
• A customer with no orders gets NULL rather than being dropped, which is the same behaviour a LEFT JOIN would give and is usually what you want here.
• The equivalent LEFT JOIN with GROUP BY returns the same result. Which is faster depends on the data and the indexes, so this is a case for measuring rather than assuming.

-- Customers who have ordered at least one discontinued product
SELECT c.company_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN products    AS p  ON p.product_id = oi.product_id
    WHERE o.customer_id = c.customer_id
      AND p.discontinued = 1
);

-- Products that have never been ordered
SELECT p.product_name, p.unit_price
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items AS oi
    WHERE oi.product_id = p.product_id
);

• EXISTS returns true as soon as the inner query produces one row, so it can stop early instead of building a full result.
• SELECT 1 is conventional inside EXISTS. Nothing reads the value — only whether a row came back — so there is no reason to fetch columns.
• NOT EXISTS is the reliable way to express "has no matching row". Unlike NOT IN, a NULL in the inner data cannot make it return nothing, because EXISTS only ever answers true or false.

One genuinely useful property of the subquery form: it cannot multiply your rows. "Customers who ordered in 2026" written with IN or EXISTS returns one row per customer, guaranteed. The same question written as a join returns one row per matching order, so you need DISTINCT or a GROUP BY to get back to one row per customer.

When you only want to filter, and not to display anything from the other table, EXISTS states the intent more precisely than a join plus DISTINCT.