DBMS

SQL Fundamentals

Subqueries and CTEs

Module 2, Chapter 3's closure property said every operation returns a relation, so any result can feed another. Subqueries are that property in SQL syntax.

JrCodex·8 min read

Jr Codex DBMS Notes

Level: Intermediate Prerequisites: Chapter 4: Aggregation and Grouping Time to complete: ~25 minutes

Table of Contents

  1. Why Queries Nest
  2. Scalar and List Subqueries
  3. Derived Tables
  4. Correlated Subqueries
  5. EXISTS and the NOT IN Trap
  6. Common Table Expressions
  7. Recursive CTEs
  8. Summary & Next Steps

1. Why Queries Nest

Module 2, Chapter 3's closure property said every operation returns a relation, so any result can feed another. Subqueries are that property in SQL syntax.

Four Places a Subquery Can Appear
─────────────────────────────────────────
  SELECT  (...)   a scalar value per row
  FROM    (...)   a derived table
  WHERE   (...)   a value, a list, or a test
  HAVING  (...)   the same, after grouping
─────────────────────────────────────────

2. Scalar and List Subqueries

-- SCALAR: returns exactly one row, one column.
SELECT name, marks,
       marks - (SELECT AVG(marks) FROM students) AS vs_average
FROM students
WHERE marks IS NOT NULL;
 
-- Also usable as a comparison operand:
SELECT name, marks FROM students
WHERE marks > (SELECT AVG(marks) FROM students);
Scalar Means EXACTLY One
─────────────────────────────────────────
  If the subquery returns more than one row, the
  query fails at runtime — not at parse time.

  So it can pass every test and fail in production
  when the data grows a second matching row.

  Defend with LIMIT 1 plus a deterministic ORDER
  BY, or restructure as a join.
─────────────────────────────────────────

-- LIST: returns one column, many rows. Used with IN / ANY / ALL.
SELECT name FROM students
WHERE id IN (SELECT student_id FROM enrollments WHERE grade = 'A');
 
SELECT title FROM courses
WHERE credits >= ALL (SELECT credits FROM courses);      -- the maximum

3. Derived Tables

A subquery in FROM, treated as a temporary table.

-- The fan-out fix from Chapter 4, written properly.
SELECT s.name, s.city, COALESCE(x.course_count, 0) AS courses
FROM students s
LEFT JOIN (
    SELECT student_id, COUNT(*) AS course_count
    FROM enrollments
    GROUP BY student_id
) x ON x.student_id = s.id
ORDER BY courses DESC, s.name;
Why This Shape Matters
─────────────────────────────────────────
  Aggregate the many-side FIRST, to one row per
  student. THEN join.

  The join is now one-to-one, so nothing
  multiplies, and any further aggregate over
  students is correct.

  This is the single most useful subquery pattern
  in day-to-day SQL.
─────────────────────────────────────────

-- Derived tables also let you filter on a window function,
-- which cannot be done in WHERE (it runs too early).
SELECT name, city, marks
FROM (SELECT name, city, marks,
             ROW_NUMBER() OVER (PARTITION BY city ORDER BY marks DESC) AS rn
      FROM students WHERE marks IS NOT NULL) t
WHERE rn <= 2;                     -- top two per city

4. Correlated Subqueries

A subquery that references the outer query, and is therefore re-evaluated per outer row.

-- Students scoring above their own city's average.
SELECT s.name, s.city, s.marks
FROM students s
WHERE s.marks > (SELECT AVG(s2.marks)
                 FROM students s2
                 WHERE s2.city = s.city);      -- ← references the OUTER s
The Cost
─────────────────────────────────────────
  Conceptually the inner query runs once per outer
  row: 6 students ──► 6 executions. On a million
  rows, a million executions.

  Modern optimisers often rewrite correlated
  subqueries into joins automatically — but not
  always, and not predictably across databases.

  Rule: if it is hot, rewrite it yourself as a
  join or a window function and measure
  (Module 6).
─────────────────────────────────────────

-- The same result as a window function: ONE pass, no correlation.
SELECT name, city, marks FROM (
    SELECT name, city, marks,
           AVG(marks) OVER (PARTITION BY city) AS city_avg
    FROM students WHERE marks IS NOT NULL
) t
WHERE marks > city_avg;

5. EXISTS and the NOT IN Trap

-- EXISTS: true if the subquery returns ANY row. Stops at the first one.
SELECT s.name FROM students s
WHERE EXISTS (SELECT 1 FROM enrollments e
              WHERE e.student_id = s.id AND e.grade = 'A');
 
-- NOT EXISTS: the anti-join, and the correct way to express absence.
SELECT s.name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.id);
Why SELECT 1
─────────────────────────────────────────
  EXISTS cares only WHETHER a row exists, never
  what is in it. `SELECT 1` signals that to the
  reader. `SELECT *` is equally fast — the
  optimiser ignores the list — but reads as though
  the columns matter.
─────────────────────────────────────────

THE NOT IN TRAP
─────────────────────────────────────────
  SELECT name FROM students
  WHERE id NOT IN (SELECT student_id FROM enrollments);

  If ANY student_id in that subquery is NULL, this
  returns ZERO ROWS. Always. Silently.

  WHY: `id NOT IN (1, 2, NULL)` expands to
    id <> 1 AND id <> 2 AND id <> NULL
  and the last term is UNKNOWN, so the whole
  conjunction can never be TRUE.

  This is the most dangerous NULL behaviour in SQL,
  because the query looks correct and returns a
  plausible empty result.
─────────────────────────────────────────

The Rule
─────────────────────────────────────────
  For absence, use NOT EXISTS — always.

  It is NULL-safe, usually as fast or faster, and
  it expresses the intent directly.

  Reserve NOT IN for literal lists you wrote
  yourself.
─────────────────────────────────────────

6. Common Table Expressions

A CTE is a named subquery defined before the main query with WITH.

WITH assessed AS (
    SELECT * FROM students WHERE marks IS NOT NULL
),
city_stats AS (
    SELECT city, COUNT(*) AS n, AVG(marks) AS avg_marks
    FROM assessed
    GROUP BY city
)
SELECT a.name, a.city, a.marks, ROUND(c.avg_marks,1) AS city_avg
FROM assessed a
JOIN city_stats c ON c.city = a.city
WHERE a.marks > c.avg_marks
ORDER BY a.city, a.marks DESC;
CTE vs Derived Table
─────────────────────────────────────────
  Identical in power. CTEs win on READABILITY:

  - named, so the query reads top-to-bottom
  - referenced MORE THAN ONCE without repetition
  - deeply nested logic becomes a flat sequence
  - the only way to write recursion (Section 7)

  A three-level nested subquery is almost always
  clearer as three CTEs.
─────────────────────────────────────────

One Performance Caveat
─────────────────────────────────────────
  In some databases a CTE is an OPTIMISATION
  FENCE — materialised once, with predicates not
  pushed into it.

  PostgreSQL did this for every CTE before v12;
  since then it inlines them unless you write
  MATERIALIZED. If a CTE rewrite is unexpectedly
  slower, this is why — check your version's
  behaviour (Module 6).
─────────────────────────────────────────

7. Recursive CTEs

The one thing subqueries cannot do: traverse a hierarchy of unknown depth.

WITH RECURSIVE org AS (
    -- ANCHOR: where the recursion starts
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    -- RECURSIVE: joins back to the CTE itself
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
    WHERE o.level < 10                     -- GUARD against cycles
)
SELECT level, name FROM org ORDER BY level, name;
How It Runs
─────────────────────────────────────────
  1. Run the ANCHOR ──► the initial rows
  2. Run the RECURSIVE part against the rows
     produced last iteration
  3. Repeat until an iteration produces NO rows
  4. UNION ALL everything

  Always include a depth guard. A cycle in the
  data — an employee who manages their own manager
  — makes an unguarded recursive CTE run until it
  exhausts memory.
─────────────────────────────────────────

This is the answer to Module 1, Chapter 2's note that relational databases handle deep hierarchies awkwardly. It works, and it is more verbose than a graph database would need.

8. Summary & Next Steps

Key Takeaways

  • Aggregate the many-side in a derived table before joining — this is the standard fix for fan-out inflating aggregates.
  • Correlated subqueries conceptually re-run per outer row; a window function usually expresses the same thing in one pass.
  • NOT IN against a subquery containing NULL returns zero rows silently — use NOT EXISTS for absence, always.
  • CTEs match derived tables in power and beat them in readability, and are the only way to express recursion over a hierarchy.

Concept Check

  1. Why does a scalar subquery pass all your tests and then fail in production?
  2. Walk through why id NOT IN (1, 2, NULL) can never be TRUE.
  3. What does a recursive CTE do that no ordinary subquery can, and what guard must it carry?

Next Chapter

→ Chapter 6: Views, Transactions and DML

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index