Subqueries & CTEs · CoreTrail

Give each intermediate question a purpose

A subquery can answer a scalar question, test whether a related row exists, or supply a new table to another stage. A CTE names that stage. Choose the form from the result you need, then investigate execution separately.

Suggested route: Compare existence and membership before learning correlated, lateral, and recursive forms. State the grain of every CTE.

By the end: Explain why a related table should or should not contribute extra output rows.

Quick reference

Syntax	Think	Common use
`EXISTS`	At least one matching row exists	semi-join / “has any”
`NOT EXISTS`	No matching row exists	anti-join / “never”
`IN`	Value belongs to a set	simple membership
`ANY / SOME`	Comparison succeeds for at least one value	`> ANY(...)`, `= ANY(...)`
`ALL`	Comparison succeeds for every value	`> ALL(...)`
scalar subquery	Return one value	thresholds, lookup values
correlated subquery	Inner query references outer row	per-entity existence/comparison
CTE `WITH`	Name an intermediate result	multi-stage reasoning
recursive CTE	Repeat from an anchor	hierarchies, trees
`LATERAL`	Right-side query uses current left row	top-N related rows, per-row expansion

Interview wording → technique

Wording	First technique to consider
“has at least one…” / “ever…”	`EXISTS`
“never…” / “without…”	`NOT EXISTS`
“is one of…”	`IN`
“greater than at least one…”	`> ANY`
“greater than every…”	`> ALL`
“every required item…”	double `NOT EXISTS` or constrained `HAVING`
“top N related rows for each parent…”	`LATERAL + ORDER BY + LIMIT`
“break this into stages…”	CTE

High-value combinations

Semi-join

WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
)

Use when you need the outer row but not columns from the related row.

Anti-join

WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
)

Prefer this over NOT IN when the comparison set can contain nulls.

Every required item: double NOT EXISTS

WHERE NOT EXISTS (
    SELECT 1
    FROM required_products rp
    WHERE NOT EXISTS (
        SELECT 1
        FROM purchases p
        WHERE p.customer_id = c.customer_id
          AND p.product_id = rp.product_id
    )
)

Read it as: there is no required product for which there is no purchase.

Per-row top N with LATERAL

SELECT c.customer_id, x.*
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.*
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_ts DESC
    LIMIT 3
) x ON TRUE;

Common traps

NOT IN plus a null in the subquery can produce unknown instead of true.
A CTE is not automatically faster or automatically materialized.
A correlated subquery is a logical relationship; the optimizer may transform its execution.
EXISTS avoids multiplying an outer row by inner matches, but it does not deduplicate duplicate rows already present in the outer table.
ALL over an empty set is true; ANY over an empty set is false.

In this chapter

EXISTS, IN, ANY & ALLMatch related rows and understand null-sensitive membership.
Subqueries & CTEsUse intermediate results to make a query easier to reason about.
Recursive CTEs & hierarchiesWalk parent-child relationships with an anchor, a recursive step, and a stopping rule.
LATERAL and per-row subqueriesUse a preceding table's values inside a FROM-clause subquery.

How to study

Map the wording to EXISTS, NOT EXISTS, membership, or a staged CTE first. Then check null behavior, output grain, and whether related columns are actually needed.