Subqueries and CTEs

This is the exact file agents read, including the credit header and licence notice. Save it as subqueries-and-ctes/SKILL.md.

---
name: subqueries-and-ctes
description: >
  Structure, rewrite and debug SQL subqueries and common table expressions (CTEs) across PostgreSQL, MySQL, SQLite, SQL Server and other engines, including scalar subqueries, derived tables, correlated subqueries, EXISTS and IN membership checks, the WITH clause, and recursive CTEs for hierarchies and graphs. Use it when a query nests a SELECT inside another, uses WITH, IN, NOT IN, EXISTS, NOT EXISTS or WITH RECURSIVE, when someone asks to "break this query into steps", "find rows with/without a match", "walk an org chart or category tree", "my NOT IN returns nothing", "my recursive query loops forever", or "is a CTE slower than a subquery". Do NOT use it for plain two-table joins (see sql-joins), for running totals and ranking (see window-functions), for stacking rows with UNION EXCEPT INTERSECT, or for schema design.
license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/sql-querying/subqueries-and-ctes/.
---

# Subqueries and CTEs

## When to use this, and when not to

Use this skill when a query needs a query inside it, or when a long query should be read as a sequence of named steps. The triggers are a nested `SELECT`, a `WITH` clause, membership tests with `IN` or `EXISTS`, their negations, and `WITH RECURSIVE` for hierarchies and graphs. It also covers the common rewrites: a slow correlated subquery that should be a join or a window, and a `NOT IN` that should be a `NOT EXISTS`.

Do not use it for a plain join between two tables (that is `sql-joins`), for per-row running totals or ranking (that is `window-functions`), or for combining result sets with `UNION`, `EXCEPT` or `INTERSECT`. It shapes how a query is nested and how existence is tested; it does not design the schema.

## Inputs the agent must have before starting

Ask for anything missing. Two of these, the engine and the NULL question, change whether a query is correct or merely fast.

1. The SQL engine and version. CTE inlining and materialisation differ sharply between engines and between PostgreSQL versions (see the appendix), and recursive syntax and limits differ too.
2. The question and the tables, columns and grain involved.
3. Whether the subquery is correlated (its inner query references the outer row) or standalone. This decides between `IN`, `EXISTS` and a join.
4. Whether any column used in a `NOT IN` or `NOT EXISTS` can be `NULL`. A single `NULL` silently breaks `NOT IN`.
5. For recursion: the starting rows, the step that moves to the next level, the natural termination, and the maximum depth you expect. Also whether the graph can contain cycles.
6. For an expensive intermediate step: roughly how many rows it produces, so you can judge whether materialising it helps or hurts.

If the query will drive an `INSERT`, `UPDATE`, `DELETE` or `MERGE` inside a CTE, confirm who approves the change and default to a read-only preview.

## The workflow, step by step

### 1. Classify what the nested query is for

| Need | Form |
|---|---|
| A single value to compare or display | Scalar subquery (one row, one column) |
| Membership against a set | `IN` or `EXISTS` |
| A table to join or select from | Derived table (subquery in `FROM`, aliased) or a CTE |
| A value computed per outer row | Correlated subquery (often rewritable to a join or window) |
| Walking a hierarchy or graph | Recursive CTE |

Name the need before writing SQL. The wrong form is the usual cause of both wrong results and slow ones.

### 2. Choose a CTE or an inline subquery for readability

Use a CTE (`WITH name AS (...)`) when a step is referenced more than once, when the query reads as stacked stages, or when naming the step makes the intent clear. Use an inline subquery for a one-off lookup that does not need a name. Decision rule: do not wrap a trivial single-table lookup in a CTE, and do not inline the same subquery three times when one named CTE would do. Readability is the main reason to reach for a CTE; performance is covered separately in step 5.

### 3. Decide between IN, EXISTS and a join

| Situation | Use |
|---|---|
| A short, static list of literals | `IN ('a', 'b', 'c')` |
| An uncorrelated subquery returning a small distinct set | `IN (SELECT ...)` |
| An existence check against a large related table | `EXISTS (correlated)` |
| You need columns from the other table, not just a yes/no | `JOIN` (watch fan-out; see sql-joins) |
| A negation where the inner column can be `NULL` | `NOT EXISTS` only |

`IN` evaluates its subquery once and tests membership; `EXISTS` runs per outer row and stops at the first match. Decision rule: modern planners (PostgreSQL, and MySQL 8.0+) rewrite `IN` and `EXISTS` into semi-joins and often produce the same plan, so choose the form that reads clearly and confirm with `EXPLAIN` rather than assuming one is faster. The one rule that is not about speed: a negation over a nullable column must be `NOT EXISTS`, because `NOT IN` returns no rows when the subquery yields a single `NULL`.

### 4. Rewrite per-row correlated subqueries that re-aggregate

A scalar correlated subquery in the `SELECT` list that aggregates the same table once per outer row runs in roughly O(n times m) time:

```sql
-- Slow: the subquery runs once per customer row
SELECT c.id,
       (SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;
```

Decision rule: when the correlated subquery aggregates, rewrite it as a single grouped join or a window function so the table is scanned once:

```sql
-- Fast: one pass, grouped, then joined
WITH last_order AS (
    SELECT customer_id, MAX(created_at) AS last_order
    FROM orders
    GROUP BY customer_id
)
SELECT c.id, lo.last_order
FROM customers c
LEFT JOIN last_order lo ON lo.customer_id = c.id;
```

Keep a correlated subquery only when it is a genuine `EXISTS` existence check, or when the outer table is tiny.

### 5. Understand CTE materialisation before relying on it for speed

A CTE is primarily a readability tool, not a performance lever, and its runtime behaviour is engine-specific.

- **PostgreSQL 12 and later (2019 onward)**: a non-recursive, side-effect-free CTE referenced once is folded into the parent query, so it plans like a subquery. Referenced more than once, it is materialised once and reused. Override either choice with `MATERIALIZED` or `NOT MATERIALIZED`.
- **PostgreSQL 11 and earlier**: every CTE was an optimisation fence, always materialised, and the planner could not push filters into it. Code written for that era may slow down or speed up when moved to 12+.
- **SQL Server, MySQL 8.0+, SQLite**: CTEs are generally inlined like views, with no guaranteed materialisation.

Decision rule: do not assume a CTE is materialised, and do not use one as a performance fence by default. If you really need a step evaluated once (to stop the planner re-running a volatile function, or to force a single pass over an expensive step), say so explicitly with `MATERIALIZED` on PostgreSQL and confirm the plan with `EXPLAIN`; on engines without that keyword, a temporary table is the portable fence. The optimiser also cannot push a filter from the parent into a multiply-referenced materialised CTE, so a `WHERE` outside the CTE may scan more than you expect.

### 6. Write recursive CTEs that terminate

A recursive CTE is an anchor term, then `UNION` or `UNION ALL`, then a recursive term that references the CTE by name:

```sql
WITH RECURSIVE subtree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL          -- anchor: the roots
  UNION ALL
    SELECT c.id, c.parent_id, c.name, s.depth + 1
    FROM categories c
    JOIN subtree s ON c.parent_id = s.id
    WHERE s.depth < 50               -- hard depth guard
)
SELECT * FROM subtree;
```

Rules, in order:

1. Use `UNION ALL` unless you specifically need duplicate rows removed; `UNION` deduplicates every iteration, which is slower and does not by itself stop a cycle.
2. Every recursive query must have a reason it stops: a depth limit, or a step whose frontier shrinks to nothing. Add a depth column and a `WHERE depth < N` guard as a backstop.
3. If the data can contain cycles (a graph, a self-referencing table with bad data), carry a path array and stop when a node repeats, or use the `CYCLE` clause where the engine supports it. Without a cycle guard, a loop runs until it exhausts memory or hits the engine's recursion limit.
4. `LIMIT` in the outer query is a testing aid, not a termination guarantee; do not ship it as the guard, because sorting or joining the recursive result can defeat it.

### 7. Keep derived tables and scalar subqueries valid

A derived table (a subquery in `FROM`) must be aliased in most engines, or the query errors. A scalar subquery used where one value is expected must return exactly one row and one column: more than one row is a runtime error, zero rows yields `NULL`. Add `LIMIT 1` with an explicit `ORDER BY` only when any one matching value is acceptable.

### 8. Treat data-modifying CTEs as irreversible

In PostgreSQL a `WITH` clause can contain `INSERT`, `UPDATE`, `DELETE` or `MERGE`. These run exactly once and to completion whether or not the outer query reads their output, on a single shared snapshot, so sub-statements cannot see one another's effects, and updating the same row twice in one statement is unsupported. Only the `RETURNING` output is visible to the rest of the query. Because the change is real and irreversible, show the read-only version first, report the affected row count, and require explicit human confirmation before running it.

### 9. Verify before handing over

Run `EXPLAIN` and confirm no step re-scans a table once per row unintentionally. For a recursive CTE, confirm it terminates: check the row count and the maximum depth against what you expect. For any `NOT IN` or `NOT EXISTS`, test the `NULL` case. For a scalar subquery, confirm it can return only one row.

## Worked example, end to end

The question: list customers whose most recent order is more than 90 days old, with the date and value of that order, as of 2026-10-29.

A first attempt uses two correlated scalar subqueries, each scanning `orders` once per customer:

```sql
SELECT c.id, c.name,
       (SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = c.id) AS last_order,
       (SELECT o.total FROM orders o
         WHERE o.customer_id = c.id
         ORDER BY o.created_at DESC LIMIT 1) AS last_value
FROM customers c
WHERE (SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = c.id)
        < DATE '2026-10-29' - INTERVAL '90 days';
```

It is correct but scans `orders` three times per customer. The rewrite computes each customer's latest order once in a CTE, then filters:

```sql
WITH ranked AS (
    SELECT o.customer_id, o.created_at, o.total,
           ROW_NUMBER() OVER (PARTITION BY o.customer_id
                              ORDER BY o.created_at DESC) AS rn
    FROM orders o
),
latest AS (
    SELECT customer_id, created_at AS last_order, total AS last_value
    FROM ranked
    WHERE rn = 1
)
SELECT c.id, c.name, l.last_order, l.last_value
FROM customers c
JOIN latest l ON l.customer_id = c.id
WHERE l.last_order < DATE '2026-10-29' - INTERVAL '90 days';
```

One pass over `orders`, the "latest row per customer" expressed with a window function (see `window-functions`), and the filter applied after. The grain is one row per qualifying customer, so the result row count must not exceed `SELECT COUNT(*) FROM customers`.

For a hierarchy, the recursive template in step 6 walks a category tree from its roots with a depth guard; swap the anchor's `WHERE parent_id IS NULL` for `WHERE id = :start_id` to walk down from one node.

## Templates you can use verbatim

```sql
-- Existence check (correlated, stops at first match)
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- NULL-safe negation
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Derived table (must be aliased)
SELECT d.customer_id, d.n
FROM (SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id) d
WHERE d.n > 5;

-- CTE chain (readable stages)
WITH paid AS (
    SELECT * FROM invoices WHERE status = 'paid'
),
by_month AS (
    SELECT date_trunc('month', paid_at) AS month, SUM(amount) AS revenue
    FROM paid GROUP BY 1
)
SELECT * FROM by_month ORDER BY month;

-- Recursive CTE with a cycle guard
WITH RECURSIVE walk AS (
    SELECT id, parent_id, ARRAY[id] AS path, false AS is_cycle
    FROM nodes WHERE id = :start
  UNION ALL
    SELECT n.id, n.parent_id, w.path || n.id, n.id = ANY(w.path)
    FROM nodes n JOIN walk w ON n.parent_id = w.id
    WHERE NOT w.is_cycle
)
SELECT * FROM walk WHERE NOT is_cycle;

-- Force a single evaluation on PostgreSQL 12+
WITH expensive AS MATERIALIZED (SELECT * FROM slow_function_over_table())
SELECT * FROM expensive WHERE key = :k;
```

Prompts that should trigger this skill: "Break this 200-line query into steps", "Find products that have never been ordered", "Walk the reporting chain up from this employee", "My NOT IN query returns zero rows", "Is my CTE making this slower?"

## Failure modes and anti-patterns

1. **NOT IN with a NULL returns nothing.** Spot it when a negated membership query returns zero rows against data with known non-matches. Fix: use `NOT EXISTS`, or exclude NULLs in the subquery with `WHERE col IS NOT NULL`.
2. **A correlated subquery re-aggregates per row.** Spot it in `EXPLAIN` as a subplan executed once per outer row, and as a query that slows down linearly with the outer table. Fix: rewrite to a grouped join or a window function.
3. **A recursive CTE never terminates.** Spot it as a query that runs until it errors on recursion depth or memory. Fix: add a depth guard and, for graphs, a path-based cycle check or the `CYCLE` clause.
4. **Assuming a CTE is always materialised.** Spot it when a query tuned on PostgreSQL 11 behaves differently on 12+, or when the same CTE pattern is fast on one engine and slow on another. Fix: state `MATERIALIZED` or `NOT MATERIALIZED` explicitly on PostgreSQL, use a temp table for a portable fence, and read the plan.
5. **A scalar subquery returns more than one row.** Spot it as a runtime error ("more than one row returned by a subquery used as an expression"). Fix: add the missing correlation or an aggregate, or `ORDER BY ... LIMIT 1` when any one value is acceptable.

## Quality bar

- Each nested query uses the right form for its need from step 1.
- `IN`/`EXISTS`/join choice matches the situation table, and every negation over a nullable column uses `NOT EXISTS`.
- No correlated subquery re-aggregates the same table per outer row where a join or window would do.
- Recursive CTEs have a termination reason and, for cyclic data, a cycle guard; `LIMIT` is not used as the guard.
- Materialisation is not assumed; any reliance on a single evaluation is stated with `MATERIALIZED` (PostgreSQL) or a temp table, and checked with `EXPLAIN`.
- Scalar subqueries can return at most one row; derived tables are aliased.
- Any data-modifying CTE is shown first as a read-only preview with a row count and held for human confirmation.

## Data handling and safety

Data-modifying CTEs change data and cannot be undone once run, so default to the read-only form, report the row count, and require human sign-off. Never put real personal, health or financial values into a prompt or a shared log; refer to columns by name and type. Do not send table contents to a third-party service to draft a query; the schema and a few synthetic rows are enough.

## Appendix: engine-specific notes (verify before relying on these)

- **CTE materialisation**: PostgreSQL 12+ inlines single-reference, side-effect-free CTEs and materialises multiply-referenced ones, overridable with `MATERIALIZED`/`NOT MATERIALIZED`; PostgreSQL 11 and earlier always materialised. SQL Server, MySQL 8.0+ and SQLite inline CTEs and have no `MATERIALIZED` keyword; use a temp table to force materialisation.
- **Recursive syntax**: PostgreSQL, MySQL 8.0+ and SQLite require `WITH RECURSIVE`. SQL Server uses plain `WITH` for recursive CTEs (no `RECURSIVE` keyword).
- **Recursion limits**: SQL Server defaults to `MAXRECURSION 100`, raised per query with `OPTION (MAXRECURSION n)` (0 removes the cap, which risks a runaway query). PostgreSQL and MySQL have no fixed default depth; add your own guard. MySQL's `cte_max_recursion_depth` defaults to 1000.
- **CYCLE clause**: supported in PostgreSQL 14+ and the SQL standard; emulate with a path array on older PostgreSQL, MySQL and SQLite.
- **Data-modifying CTEs**: PostgreSQL supports `INSERT`/`UPDATE`/`DELETE`/`MERGE` in `WITH` with `RETURNING`. SQL Server allows a CTE as the source of a modifying statement but not arbitrary modifying statements inside `WITH`. MySQL and SQLite do not support data-modifying statements inside a CTE.
- **Derived table alias**: required in PostgreSQL, MySQL and SQL Server; SQLite permits an unaliased subquery in `FROM` but aliasing is clearer.

## Sources

- PostgreSQL 18 documentation, "WITH Queries (Common Table Expressions)": recursive evaluation, `MATERIALIZED`/`NOT MATERIALIZED` default, single-vs-multiple reference folding, data-modifying statements, cycle detection. Verified 29 October 2026.
- PostgreSQL 12 release notes: CTEs no longer an unconditional optimisation fence. Verified 29 October 2026.
- MySQL 8.0 Reference Manual, "WITH (Common Table Expressions)" and `cte_max_recursion_depth`. Verified 29 October 2026.
- Microsoft SQL Server documentation, "WITH common_table_expression" and `MAXRECURSION`. Verified 29 October 2026.
- OneUptime engineering blog, "How to Choose Between IN and EXISTS in MySQL" (NOT IN NULL semantics, correlated vs uncorrelated, semi-join transforms). Verified 29 October 2026.
- Redgate Code Analysis rule PE019, "Consider using EXISTS instead of IN" (NOT IN risk with NULLs, rewrite to NOT EXISTS or outer join). Verified 29 October 2026.