SQL CTEs — The WITH Clause Explained

A single CTE: name your intermediate step

The simplest use of WITH is to name a query, then use it in a single follow-up SELECT. Read it top to bottom: define active_students as the program records whose status is active, then select from it. The CTE is scoped to this one statement; nothing about your schema changes.

You could write the same logic as a subquery in the FROM clause. The CTE form is preferred when the intermediate step is conceptually meaningful, when you want to reference it more than once, or when nesting subqueries would force the reader to scan inside-out to understand the query.

A CTE can SELECT, but it can also INSERT, UPDATE, or DELETE in PostgreSQL — useful for atomically moving rows between tables. For learning, focus on the SELECT form; the others build on the same syntax.

-- Name the intermediate step, then use it
-- (stpr_status: A = Active, G = Graduated)
WITH active_students AS (
  SELECT students_id, stpr_gpa
  FROM ods_stu_acad_levels
  WHERE stpr_status = 'A'
)
SELECT
  COUNT(*)                AS active_count,
  ROUND(AVG(stpr_gpa), 2) AS average_gpa
FROM active_students;

-- active_count  average_gpa
-- 126           2.95

Chaining CTEs with commas

You can define multiple CTEs in one WITH clause, separated by commas. Each CTE can reference any CTE defined before it. This lets you build a query as a sequence of clearly named transformations, each one operating on the output of the previous one.

Read the example below as a pipeline: first count each student's Spring 2026 registrations on ods_stu_course_sec, then bucket students into light / standard / heavy loads, then aggregate the buckets. Trying to write the same query as nested subqueries pushes the most important logic to the deepest indentation level — exactly where it is hardest to read.

A practical rule: if you find yourself writing a subquery inside a subquery inside a subquery, refactor to a chain of CTEs. The performance is usually similar; the readability difference is large.

-- Three steps: count registrations, bucket, aggregate
WITH course_load AS (
  SELECT scs_student, COUNT(*) AS course_count
  FROM ods_stu_course_sec
  WHERE scs_term = '2026SP'
  GROUP BY scs_student
),
buckets AS (
  SELECT
    scs_student,
    CASE
      WHEN course_count <= 2 THEN 'light'
      WHEN course_count <= 4 THEN 'standard'
      ELSE 'heavy'
    END AS load_bucket
  FROM course_load
)
SELECT load_bucket, COUNT(*) AS students
FROM buckets
GROUP BY load_bucket
ORDER BY students DESC;

-- load_bucket  students
-- standard     60
-- heavy        40
-- light        18

CTE vs. subquery — when to pick which

CTEs and subqueries can usually express the same thing. The differences are practical: CTEs read top-to-bottom, subqueries read inside-out. CTEs let you name the step; subqueries do not. CTEs can be referenced more than once in the outer query; an inline subquery cannot.

Reach for a subquery when the intermediate result is small, used exactly once, and the logic is simple — for example, a WHERE id IN (SELECT ... FROM small_table) or a scalar subquery in the SELECT list. The inline form is concise.

Reach for a CTE when the intermediate result is reused, when naming it makes the query self-documenting, or when the alternative would push core logic three or four levels of nesting deep. Performance is rarely the deciding factor in modern PostgreSQL — write for the next person who reads the query, which is often you.

-- Same question, two ways: who has taken BIOL-101?
-- 1) Inline subquery: fine when the result is used once
SELECT first_name, last_name
FROM ods_person
WHERE id IN (
  SELECT scs_student
  FROM ods_stu_course_sec
  WHERE scs_course_name = 'BIOL-101'
);

-- 2) CTE: better once the step is named and joined back to
WITH biol_students AS (
  SELECT DISTINCT scs_student
  FROM ods_stu_course_sec
  WHERE scs_course_name = 'BIOL-101'
)
SELECT p.first_name, p.last_name, t.term_desc
FROM biol_students b
JOIN ods_person p         ON p.id = b.scs_student
JOIN ods_stu_course_sec e ON e.scs_student = b.scs_student
                         AND e.scs_course_name = 'BIOL-101'
JOIN ods_terms t          ON t.terms_id = e.scs_term
ORDER BY p.last_name, t.term_start_date;

Recursive CTEs — walking hierarchies

A recursive CTE has two parts joined by UNION ALL: an anchor query that produces the starting rows, and a recursive query that references the CTE itself to produce the next layer. PostgreSQL keeps applying the recursive part until it returns no new rows.

This is how you walk a hierarchy: prerequisite chains, organizational reporting lines, term-over-term cohorts, ancestry trees. The non-recursive alternative is repeated self-joins, which only works if you know the depth in advance.

Two habits worth keeping. First, every recursive CTE should have a clear stopping condition — below, the recursion ends naturally when the calendar runs out at Fall 2027, but the depth < 10 guard means a malformed graph cannot loop forever. Second, find the "next" row by a real ordering key. Term codes like 2026FA and 2026SP sort alphabetically, not chronologically, so the step uses term_start_date and a LATERAL subquery to pick exactly one successor.

-- Walk forward from Fall 2025, one term at a time
WITH RECURSIVE term_walk AS (
  -- Anchor: start at Fall 2025
  SELECT terms_id, term_desc, term_start_date, 0 AS depth
  FROM ods_terms
  WHERE terms_id = '2025FA'

  UNION ALL

  -- Recursive step: the single next term after the current one
  SELECT nxt.terms_id, nxt.term_desc, nxt.term_start_date, w.depth + 1
  FROM term_walk w
  JOIN LATERAL (
    SELECT terms_id, term_desc, term_start_date
    FROM ods_terms
    WHERE term_start_date > w.term_start_date
    ORDER BY term_start_date
    LIMIT 1
  ) nxt ON true
  WHERE w.depth < 10   -- safety guard
)
SELECT terms_id, term_desc, depth
FROM term_walk
ORDER BY depth;

-- 2025FA  Fall 2025    0
-- 2026SP  Spring 2026  1
-- 2026FA  Fall 2026    2
-- 2027SP  Spring 2027  3
-- 2027FA  Fall 2027    4

Common pitfalls

A CTE only exists for the statement it lives in. You cannot define a CTE in one query and reference it in the next; for that you would need a view or a temp table. If you find yourself wanting cross-query reuse, lift the CTE into a CREATE VIEW.

Older articles will tell you that PostgreSQL "always materializes" CTEs and they are slower than subqueries. As of PostgreSQL 12, the planner inlines a CTE by default when it is referenced only once and has no side effects (it is a plain SELECT, not recursive, and calls no volatile function); a CTE referenced two or more times is still computed once and reused. WITH ... AS MATERIALIZED and AS NOT MATERIALIZED override the default either way. Treat performance claims about CTEs from before 2020 with skepticism.

Forgetting to alias columns with the same name across CTEs is a frequent source of "column reference is ambiguous" errors. Be liberal with aliases — l.students_id, e.scs_student — especially once you start chaining.

-- Force materialization (rarely needed) — opt-in keyword
WITH expensive_step AS MATERIALIZED (
  SELECT scs_student, COUNT(*) AS n
  FROM ods_stu_course_sec
  GROUP BY scs_student
)
SELECT * FROM expensive_step WHERE n > 10;

Practice these on QueryU

CTEs come into their own on the hard tier. Each challenge below opens with WITH — start with H07 and work up to H20, the recursive CTE. Grading compares the rows you return, so a subquery that produces the same result passes too.