Subqueries & CTEs

#backend #database #sql #tutorial

What are subqueries?

A subquery is a query nested inside another query - it runs first, and its result is used by the outer query, whether that's as a filter condition, a computed value, or a virtual table to select from. Subqueries can appear almost anywhere: inside a WHERE clause, a SELECT list, or a FROM clause.

There are a few flavors worth knowing:

Scalar subquery - returns a single value, usable anywhere a single value is expected.
Row/column subquery - returns a set of values, often used with IN or ANY/ALL.
Correlated subquery - references a column from the outer query, so it re-runs once per row of the outer query rather than just once overall.

What are CTEs?

A CTE (Common Table Expression) is a named, temporary result set defined with a WITH clause at the start of a query, which you can then reference like a regular table for the rest of that query. It exists only for the duration of that one query.

WITH high_value_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE order_id IN (
        SELECT order_id FROM order_items WHERE quantity > 3
    )
)
SELECT * FROM high_value_orders;

Differences between them

Readability - a CTE gives a name to an intermediate result and reads top-to-bottom like a sequence of steps; a deeply nested subquery reads inside-out, which gets hard to follow fast.
Reuse - a CTE can be referenced multiple times later in the same query; a subquery has to be rewritten (or the query restructured) if you need that same intermediate result twice.
Recursion - CTEs support WITH RECURSIVE, which lets a query reference itself - useful for hierarchical data like an org chart or a category tree. Plain subqueries can't do this.
Performance - in PostgreSQL, this used to be a bigger differentiator (CTEs were historically an "optimization fence"), but modern Postgres treats simple CTEs and subqueries similarly in most cases. The real reason to reach for one over the other is usually clarity, not speed.

Practical examples and use cases

Using the Sunrise Supermarket schema as the example: finding customers whose orders include at least one item with a quantity above 3 - first as a subquery:

SELECT full_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_id IN (
        SELECT order_id FROM order_items WHERE quantity > 3
    )
);

The same logic as a CTE - flatter, and easier to read as a sequence of "first this, then this":

WITH bulk_items AS (
    SELECT order_id FROM order_items WHERE quantity > 3
),
bulk_orders AS (
    SELECT customer_id FROM orders WHERE order_id IN (SELECT order_id FROM bulk_items)
)
SELECT full_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM bulk_orders);

A correlated subquery example - products priced above the average price within their own category, not the overall average:

SELECT product_name, category, unit_price
FROM products p1
WHERE unit_price > (
    SELECT AVG(unit_price)
    FROM products p2
    WHERE p2.category = p1.category
);

What I understood from this

The nested-subquery version and the CTE version return the exact same result - that's the part that took a moment to click. The choice between them isn't about correctness, it's about who has to read the query six months from now. Once a query needs two or three logical steps chained together, I reach for a CTE almost automatically now, because naming each intermediate step (bulk_items, then bulk_orders) makes the query's intent visible, not just its mechanics.

DEV Community