189: CTEs, Recursive Queries, UNION/INTERSECT/EXCEPT, and Query Composition

Learning outcomes

By the end of this lesson, you should be able to:

explain what a common table expression is and apply one in a realistic implementation;
explain how a recursive CTE works and apply one in a realistic implementation;
choose between UNION and UNION ALL based on the required duplicate semantics;
use INTERSECT to express a shared-membership or reconciliation query;
use EXCEPT to compare sets for data quality, migration, or authorization work.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before you start, retrieve one concrete example from a previous project in which you had to compose queries, traverse a hierarchy, or compare two collections of records. That retrieval matters because the goal is not to memorize SQL vocabulary. The goal is to make a defensible choice in a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency are connected.

Terminology

Common table expressions: WITH gives a name to an intermediate query result. It is a tool for expressing query structure and meaning, not just a way to avoid repeating text.
Recursive CTEs: A recursive query combines a non-recursive seed with a recursive term. The recursive term expands from the rows already found until the query reaches its stopping condition and produces no additional rows.
UNION and UNION ALL: UNION combines compatible result sets and removes duplicate rows, which requires additional work. UNION ALL concatenates the result sets and keeps duplicates.
INTERSECT: INTERSECT returns rows that occur in both result sets. It is a concise way to ask which records are shared by two independently produced sets.
EXCEPT: EXCEPT returns rows from the first query that do not occur in the second. It is useful for data-quality comparisons, migration checks, and permission audits.
Composable stages: A complex report can be divided into named stages such as scoped_orders, item_totals, refunds, and final_metrics. Each stage should have a clear grain and a reason for existing.

Mental model

Treat CTEs, Recursive Queries, UNION/INTERSECT/EXCEPT, and Query Composition as a design problem with observable inputs, outputs, invariants, and failure modes. A CTE or set operation is valuable when it makes the set logic easier to read and verify. It is not automatically valuable merely because it makes a query look shorter. If the intermediate relation has no clear meaning, the query may be hiding uncertainty rather than removing complexity.

For each stage, be able to answer: What does one row represent? Which rows are allowed in the input? What must be true about the output? What happens when the input is empty, duplicated, stale, or malformed? A strong implementation makes those assumptions visible, narrows uncertainty at each boundary, and leaves evidence such as tests, types, constraints, metrics, or diagrams showing why the design is safe.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement directly to a library call or a familiar SQL pattern. First state what must remain true. Then choose the query mechanism that expresses or enforces that invariant. For example, “show active customers, including those with no orders” is a different requirement from “show active customers who placed an order,” even though both can start with the same tables.

Deep dive

1. Common table expressions

When a query contains several logically distinct steps, putting every join and filter into one large SELECT makes it difficult to check the grain of each step. WITH names an intermediate query so that the next stage can refer to it directly. That name should describe the relation's meaning, not merely its implementation detail.

Modern PostgreSQL may inline eligible CTEs. In other words, you should not assume that every CTE is an optimization barrier or that naming a stage necessarily forces it to be materialized. In cases where materialization matters, PostgreSQL allows it to be requested or forced explicitly. Inspect the execution plan when performance is important rather than relying on an assumption about how the planner will treat the CTE.

Decision rule: Use a common table expression deliberately when it makes the contract, row grain, or invariant easier to prove. If it only reduces typing while hiding an assumption about duplicates, filtering, or ordering, prefer a design that makes that assumption explicit.

2. Recursive CTEs

Hierarchies and graph-like data create a different problem: one row points to another row, which may point to another row, and the number of steps is not known in advance. A recursive query has two parts. The non-recursive seed selects the starting rows, and the recursive term joins from the rows already discovered to the next level. Expansion continues until no new rows satisfy the recursive term's conditions.

The stopping condition is part of the design, not an afterthought. Use explicit depth limits and cycle protection for hierarchical or graph-like data. Without those controls, a bad relationship or an unexpected cycle can cause unbounded work or prevent the query from terminating. Also decide whether the result should preserve traversal depth, path information, or only the distinct nodes reached.

Decision rule: Use recursive CTEs deliberately when they make the traversal contract or invariant easier to prove. If the relationship is fixed-depth or can be represented more clearly by a different model, do not use recursion simply because the database supports it.

3. UNION and UNION ALL

Both operators combine compatible result sets, but they do not mean the same thing. UNION removes duplicate rows across the combined result, so it must do extra work to identify those duplicates. UNION ALL appends the rows and preserves multiplicity. That difference is semantic before it is a performance consideration.

Use UNION when the requirement says the output is a set of unique rows and duplicates from the inputs have no independent meaning. Use UNION ALL when each occurrence represents an event, source record, or countable contribution. If you are unsure which one to use, inspect what one output row means and whether duplicate rows can legitimately carry information.

Decision rule: Use UNION or UNION ALL deliberately when the chosen duplicate behavior makes the output contract easier to prove. If it only follows habit, stop and state whether duplicates should be removed or retained.

4. INTERSECT

INTERSECT returns the rows present in both result sets. This is often clearer than joining two independently written queries when the actual question is “which complete rows are shared?” The participating queries must produce compatible columns, and the default set operation removes duplicate result rows.

It can express reconciliation checks, such as finding IDs present in both an imported feed and an existing account set. It can also express shared membership between two filtered populations without making one query's join behavior accidentally change the other's grain. As with every set operation, define the columns that identify equality before you write the query.

Decision rule: Use INTERSECT deliberately when shared membership is the requirement and the result's equality semantics are clear. If you need to retain multiplicity or attach columns from one side, a different query shape may be more explicit.

5. EXCEPT

EXCEPT returns rows in the first query that are absent from the second. The order matters: swapping the two queries changes the meaning. Like INTERSECT, it normally operates with set semantics and removes duplicate result rows.

This makes EXCEPT useful for questions such as “which imported IDs are not present in the target table?” or “which permissions are expected but missing?” For a migration or permission audit, run the comparison in both directions when you need to detect additions as well as omissions. Be explicit about whether NULL and duplicate source rows are meaningful to the comparison.

Decision rule: Use EXCEPT deliberately when a directional difference is the contract or invariant you need to verify. If the comparison requires occurrence counts or detailed mismatch reasons, use a query that exposes those details instead of hiding them behind set difference.

6. Composable stages

Break a report into named stages such as scoped_orders, item_totals, refunds, and final_metrics. A scope stage should establish which records are in the report. Later stages can aggregate or reconcile those records without quietly broadening the population again.

Give every CTE a testable grain. For example, scoped_orders might be one row per order, while item_totals might be one row per order after aggregation. Carry only the columns needed by later stages. Extra columns make reasoning harder and can increase the amount of data that must be processed, especially when a stage is materialized or prevents an efficient plan.

Decision rule: Use composable stages deliberately when named boundaries make the report's contract or invariant easier to prove. If a stage only hides a join or filter that readers still have to reconstruct mentally, make the relationship more explicit.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Begin by stating the requirement in one sentence. Then write down the input and output contracts and identify which concept owns each possible failure mode. This prevents a CTE, a set operation, or a convenient query builder from becoming a substitute for design.

The useful separation is by responsibility: parsing and input validation belong at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; and presentation rules belong in the client. Mixing these concerns can make a happy-path demo look shorter, but it makes edge cases, retries, and authorization much harder to reason about.

Here is a small query that reports active customers, including active customers who have no matching orders:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

The LEFT JOIN is doing meaningful work. A customer with no order still survives the join, and COUNT(o.id) is zero because the joined order ID is NULL; COUNT(*) would produce a different result. The GROUP BY establishes one output row per customer, and the ordering is applied only after those counts have been computed. If this became a larger report, each scope and aggregation could be named as a stage, but the row grain would still need to be checked at every boundary.

Walk the example through at least four cases: the normal path, an empty or missing value, a duplicate/retry/concurrent path where relevant, and a dependency failure. For each case, state which layer detects the problem and what the caller observes. For instance, an absent order is a valid zero-count result, while a malformed customer status or a database outage should not be silently turned into an empty report. This is the level of explanation expected in a senior code review or technical interview.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failures, stale clients, concurrent requests, malformed data, schema changes, and high-cardinality workloads. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence from an execution plan, metrics, logs, or tests.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and all network input is untrusted. A query that correctly filters rows is not, by itself, a complete authorization design unless the authorization boundary and its transaction behavior are also clear.

Guided lab

Build a category hierarchy with a recursive CTE. Include both cycle protection and a depth limit, and make the output's depth or path observable so that you can explain how the traversal stopped. Then reconcile two imported ID sets using UNION ALL, INTERSECT, and EXCEPT. Explain which operations preserve duplicates, which use set semantics, and why the direction of EXCEPT changes the answer.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing. For the SQL, inspect representative results and an execution plan where performance is relevant.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Edge cases and failure modes

Common table expressions: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Also verify the row grain of each named stage and whether planner behavior matches your performance assumption.
Recursive CTEs: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include a cycle, an over-depth branch, and a disconnected branch where those states are valid for the model.
UNION and UNION ALL: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Confirm that duplicate removal or preservation matches the business meaning of one output row.
INTERSECT: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Check the equality columns and confirm whether duplicate rows should collapse.
EXCEPT: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Test both query directions when the comparison is used as a reconciliation or audit.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct while having the wrong join direction, row grain, duplicate behavior, or authorization boundary.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and discovering the actual contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case first. Inspect the actual rows, duplicate counts, NULL behavior, and execution plan rather than guessing from the query's formatting. Trace the boundary where the invariant first becomes false: source or build, server or route, database or query, or deployment and configuration. Then fix the layer that owns the problem instead of adding a downstream patch that conceals it.

Interview questions

What problem do Common table expressions solve, and what trade-off or failure mode would make you choose a different approach?
What problem do Recursive CTEs solve, and what trade-off or failure mode would make you choose a different approach?
What problem do UNION and UNION ALL solve, and what trade-off or failure mode would make you choose a different approach?
What problem does INTERSECT solve, and what trade-off or failure mode would make you choose a different approach?
What problem does EXCEPT solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain CTEs, Recursive Queries, UNION/INTERSECT/EXCEPT, and Query Composition to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. Be prepared to explain the row grain, duplicate semantics, stopping condition, and evidence you used to verify the result.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.