Cheat SheetsSQLSubqueries & CTEs

Subqueries & CTEs — Cheat Sheet

SQL · 8 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org
Subqueries & CTEs
SQL8 topicsQuick revision reference
1

Scalar Subquery

A scalar subquery returns exactly one row and one column, and can appear anywhere a single value is expected — SELECT list, WHERE, HAVING, or ORDER BY.

  • ✓A scalar subquery must return exactly one row and one column — a runtime error occurs if it returns more than one row.
  • ✓When a scalar subquery returns no rows, the result is NULL — comparisons with NULL silently filter rows.
  • ✓A correlated scalar subquery re-executes once per outer row; on large tables this is an N+1 performance problem.
  • ✓Rewrite correlated scalar subqueries in SELECT as JOINs to pre-aggregated subqueries or as window functions.
  • ✓Non-correlated scalar subqueries execute once and are cached — safe to use for global constants like company-wide averages.
SQL — correlated scalar subquery N+1 and JOIN rewrite
-- Non-correlated: computes once, reused for every row
SELECT
    e.name,
    e.salary,
    (SELECT AVG(salary) FROM employees) AS company_avg_salary,
    e.salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees e;

-- Correlated scalar subquery: re-executes for EVERY employee row (N+1!)
SELECT
    e.name,
    e.salary,
    (
        SELECT AVG(salary)
        FROM employees dept_avg
        WHERE dept_avg.department_id = e.department_id  -- references outer row
    ) AS dept_avg_salary
FROM employees e;
-- On 10 000 employees this runs the subquery 10 000 times.

-- Performance fix: rewrite as a JOIN to a pre-aggregated subquery
SELECT
    e.name,
    e.salary,
    da.dept_avg_salary
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS dept_avg_salary
    FROM employees
    GROUP BY department_id
) da ON da.department_id = e.department_id;
-- Single scan of employees; O(n) instead of O(n²).
2

Correlated Subquery

A correlated subquery references columns from the outer query and re-executes once per outer row, enabling row-by-row comparisons at the cost of potential N+1 performance problems.

  • ✓A correlated subquery references an outer query column and re-executes once per outer row — an N+1 pattern in SQL.
  • ✓Rewrite correlated subqueries that aggregate data as a JOIN to a pre-aggregated subquery or as a window function.
  • ✓EXISTS short-circuits on the first match, making it the most efficient correlated subquery for existence checks.
  • ✓NOT EXISTS is NULL-safe; NOT IN returns no rows if the subquery contains any NULL.
  • ✓Check EXPLAIN to see whether the planner has automatically unnested (decorrelated) the subquery.
SQL — correlated subquery, JOIN rewrite, window function rewrite
-- Find employees earning more than their own department's average salary
-- Correlated subquery version:
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees sub
    WHERE sub.department_id = e.department_id  -- correlated reference
);
-- For each of N employees, a separate AVG scan of that department.
-- O(N * dept_size) — bad on large tables.

-- Rewrite 1: JOIN to pre-aggregated subquery (one scan, then join)
SELECT e.name, e.salary, e.department_id
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
) dept ON dept.department_id = e.department_id
WHERE e.salary > dept.avg_sal;

-- Rewrite 2: window function (single pass, most idiomatic)
SELECT name, salary, department_id
FROM (
    SELECT
        name, salary, department_id,
        AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
) ranked
WHERE salary > dept_avg;
3

IN vs EXISTS vs ANY vs ALL

IN, EXISTS, ANY, and ALL are subquery operators with distinct NULL semantics, short-circuit behaviour, and performance profiles that determine correctness and speed.

  • ✓IN is equivalent to = ANY; NOT IN is equivalent to <> ALL — both are dangerous when the subquery contains NULLs.
  • ✓NOT IN returns zero rows if the subquery has any NULL value — use NOT EXISTS or add IS NOT NULL to the subquery.
  • ✓EXISTS short-circuits on the first match; IN materialises the full subquery result before comparison.
  • ✓ANY returns true if the comparison holds for at least one row; ALL requires it to hold for every row.
  • ✓Optimisers often rewrite IN as a semi-join and EXISTS as an identical semi-join — performance is usually equivalent.
  • ✓For existence checks, prefer EXISTS over IN; for non-existence checks, always prefer NOT EXISTS over NOT IN.
SQL — NOT IN NULL trap, NOT EXISTS fix, LEFT JOIN anti-join
-- Setup: some orders have NULL user_id (e.g., guest checkouts)
-- users table: ids 1,2,3,4,5
-- orders.user_id: 1, 2, NULL

-- Goal: find users who have never placed an order

-- WRONG — NOT IN with a subquery containing NULL returns 0 rows!
SELECT id, name FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
-- NULL in subquery → NOT IN evaluates to UNKNOWN for all rows → empty result!

-- Fix 1: NOT EXISTS (NULL-safe, short-circuits)
SELECT id, name FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Fix 2: NOT IN with explicit NULL filter
SELECT id, name FROM users
WHERE id NOT IN (
    SELECT user_id FROM orders WHERE user_id IS NOT NULL
);

-- Fix 3: LEFT JOIN / IS NULL (anti-join)
SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
4

CTE (WITH Clause)

A Common Table Expression (WITH clause) gives a subquery a name, improving readability and enabling reuse within the same query without creating a temporary table.

  • ✓CTEs are defined before the main SELECT with the WITH keyword and referenced by name in the query body.
  • ✓Multiple CTEs in one WITH block are separated by commas and can reference earlier CTEs in the same block.
  • ✓CTEs improve readability over nested subqueries by naming each logical step.
  • ✓In PostgreSQL 12+, the planner inlines CTEs by default; use MATERIALIZED to cache an expensive CTE used multiple times.
  • ✓CTEs do not persist beyond the query — use a temporary table if you need the intermediate result in multiple statements.
  • ✓A CTE cannot be referenced in a sibling CTE, only in CTEs defined after it or in the main query.
SQL — 4-step chained CTEs for order analysis
-- Goal: for each city, find the top product category by revenue last month

-- Step 1: orders in the last 30 days
WITH recent_orders AS (
    SELECT o.id, o.user_id, o.product_id, o.amount
    FROM orders o
    WHERE o.created_at >= NOW() - INTERVAL '30 days'
      AND o.status = 'completed'
),

-- Step 2: join to get city and category
enriched AS (
    SELECT
        u.city,
        p.category,
        ro.amount
    FROM recent_orders ro
    JOIN users    u ON u.id = ro.user_id
    JOIN products p ON p.id = ro.product_id
),

-- Step 3: revenue by (city, category)
revenue_by_city_cat AS (
    SELECT city, category, SUM(amount) AS revenue
    FROM enriched
    GROUP BY city, category
),

-- Step 4: rank categories within each city
ranked AS (
    SELECT
        city, category, revenue,
        RANK() OVER (PARTITION BY city ORDER BY revenue DESC) AS rnk
    FROM revenue_by_city_cat
)

-- Final: top category per city
SELECT city, category, revenue
FROM ranked
WHERE rnk = 1
ORDER BY revenue DESC;
5

Recursive CTE

A recursive CTE has an anchor query that seeds the recursion and a recursive member that repeatedly joins the CTE to itself until no new rows are produced.

  • ✓A recursive CTE has two parts separated by UNION ALL: the anchor (runs once) and the recursive member (iterates).
  • ✓Use UNION ALL (not UNION) for recursion unless deduplication is required — UNION adds an expensive sort/hash step per iteration.
  • ✓Always include a termination condition: either a WHERE clause that will eventually return no rows, or a depth limit.
  • ✓Track visited IDs in an array (or use the CYCLE clause in PostgreSQL 14+) to prevent infinite loops on cyclic data.
  • ✓Common use cases: org charts, category trees, bill-of-materials, date series generation, graph traversal.
  • ✓Add a maximum depth guard (AND depth < 100) as a safety net against unexpected data cycles.
SQL — recursive CTE org chart, depth tracking
-- employees(id, name, department_id, manager_id, salary, hire_date)
-- Find all employees who report (directly or indirectly) to manager id = 5

WITH RECURSIVE org_tree AS (
    -- Anchor: the manager themselves
    SELECT
        id,
        name,
        manager_id,
        1 AS depth           -- track hierarchy level
    FROM employees
    WHERE id = 5

    UNION ALL

    -- Recursive member: employees whose manager is in the current result
    SELECT
        e.id,
        e.name,
        e.manager_id,
        ot.depth + 1
    FROM employees e
    JOIN org_tree ot ON ot.id = e.manager_id   -- join CTE to itself
)

SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

-- Termination: when no employees have manager_id matching any current org_tree.id,
-- the recursive member returns zero rows and recursion stops.
6

Subquery vs JOIN vs CTE — When to Use Which

Subqueries, JOINs, and CTEs often produce the same execution plan, but the right choice depends on whether you need columns from both tables, multiple references, or readable multi-step logic.

  • ✓Use JOIN when you need columns from both tables in the result — a subquery in WHERE cannot project them.
  • ✓Use EXISTS/IN for pure existence or membership checks where you do not need the matched row's columns.
  • ✓Use a CTE for multi-step logic, named intermediates, or when the same result is referenced more than once.
  • ✓The optimizer often rewrites subqueries as joins automatically — check EXPLAIN to confirm the actual plan.
  • ✓NOT IN vs NOT EXISTS is a correctness issue (NULL trap), not just a style choice — always prefer NOT EXISTS.
  • ✓MATERIALIZED CTE prevents redundant re-evaluation when the CTE is referenced multiple times in the same query.
SQL — order report: subquery, JOIN, CTE comparison
-- Requirement: list order id, amount, customer city, product category
-- for completed orders in 2024.

-- Version 1: Correlated subquery in SELECT (readable but N+1 risk)
SELECT
    o.id,
    o.amount,
    (SELECT u.city FROM users u WHERE u.id = o.user_id)       AS city,
    (SELECT p.category FROM products p WHERE p.id = o.product_id) AS category
FROM orders o
WHERE o.status = 'completed'
  AND o.created_at >= '2024-01-01';
-- Risk: two correlated subqueries → two extra lookups per row.

-- Version 2: JOIN (best when you need columns from both tables)
SELECT
    o.id,
    o.amount,
    u.city,
    p.category
FROM orders o
JOIN users    u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
WHERE o.status = 'completed'
  AND o.created_at >= '2024-01-01';
-- Clean, one scan per table; optimizer free to choose hash/merge/nested loop.

-- Version 3: CTE (best when logic has multiple named steps)
WITH completed_orders AS (
    SELECT id, user_id, product_id, amount
    FROM orders
    WHERE status = 'completed'
      AND created_at >= '2024-01-01'
)
SELECT
    co.id,
    co.amount,
    u.city,
    p.category
FROM completed_orders co
JOIN users    u ON u.id = co.user_id
JOIN products p ON p.id = co.product_id;
7

LATERAL JOIN / CROSS APPLY

A LATERAL join allows a subquery in the FROM clause to reference columns from preceding table expressions, enabling row-dependent subqueries such as top-N per group.

  • ✓LATERAL (PostgreSQL/standard SQL) and CROSS APPLY (SQL Server) allow a FROM subquery to reference columns from tables to its left.
  • ✓The subquery executes once per row of the left table — making it the right tool for top-N per group with LIMIT.
  • ✓JOIN LATERAL drops left rows that produce zero subquery rows; LEFT JOIN LATERAL preserves them (like OUTER APPLY).
  • ✓Window function ROW_NUMBER() is often equivalent but scans all rows before filtering; LATERAL LIMIT can stop early.
  • ✓LATERAL is also used to call set-returning functions per row in PostgreSQL.
  • ✓ON TRUE is the conventional join condition when no filtering beyond the subquery's WHERE is needed.
SQL — LATERAL top-3 orders per user, LEFT JOIN LATERAL
-- Top 3 orders by amount per user using LATERAL (PostgreSQL)
SELECT
    u.id        AS user_id,
    u.name,
    top_orders.id        AS order_id,
    top_orders.amount,
    top_orders.created_at
FROM users u
JOIN LATERAL (
    SELECT id, amount, created_at
    FROM orders o
    WHERE o.user_id = u.id          -- references left-side column u.id
    ORDER BY amount DESC
    LIMIT 3
) top_orders ON TRUE;               -- ON TRUE: always join (no filter condition)
-- For each user, the subquery runs once and returns at most 3 rows.

-- LEFT JOIN LATERAL: preserve users who have no orders at all
SELECT u.id, u.name, top_orders.amount
FROM users u
LEFT JOIN LATERAL (
    SELECT id, amount
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY amount DESC
    LIMIT 3
) top_orders ON TRUE;
-- Users with no orders appear with NULL amount (not dropped).
8

Derived Tables

A derived table is a subquery in the FROM clause that acts as an inline, unnamed view, enabling multi-level aggregation and intermediate filtering without creating a permanent object.

  • ✓A derived table is a subquery in the FROM clause and must have an alias — omitting the alias is a syntax error.
  • ✓Use a derived table to perform multi-level aggregation (average of sums) that cannot be done in a single GROUP BY.
  • ✓Pre-filtering rows inside a derived table before a join can significantly reduce the number of rows the join processes.
  • ✓Unlike a CTE, a derived table cannot be referenced more than once in the same query — use a CTE for reuse.
  • ✓The planner typically inlines derived tables, so performance is usually the same as an equivalent CTE.
  • ✓Prefer CTEs over deeply nested derived tables (more than 2 levels) for readability.
SQL — derived table for double aggregation (avg of sums by city)
-- Goal: average order value per city
-- (average of each user's total spend — not a simple AVG of all amounts)

-- Anti-pattern: AVG of all order amounts (misleading for users with many orders)
SELECT u.city, AVG(o.amount) AS naive_avg
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.city;
-- This over-represents high-volume users.

-- Correct: derived table computes per-user total first, outer query averages
SELECT city, AVG(user_total) AS avg_user_order_value
FROM (
    SELECT u.city, o.user_id, SUM(o.amount) AS user_total
    FROM orders o
    JOIN users u ON u.id = o.user_id
    WHERE o.status = 'completed'
    GROUP BY u.city, o.user_id
) per_user_totals                   -- mandatory alias
GROUP BY city
ORDER BY avg_user_order_value DESC;
Learn this free with Aria, your AI tutor → AiCanCode.org/learn/sql