PyCodeItPython trace & SQL practice
SQL Analytics TrackWebAssembly SQLite Playground

CTEs & Set Operators

Structure temporary query sets with WITH CTEs, and perform operations using UNION, EXCEPT, INTERSECT.

Relational Theory & Optimization Architecture

Common Table Expressions (CTEs) define temporary named result sets that exist within the execution scope of a single SQL statement. Introduced to improve query modularity and readability over nested subqueries, CTEs also support recursive definitions for hierarchical data (such as organizational charts and bill-of-materials graphs). Set operators (UNION, UNION ALL, INTERSECT, EXCEPT) combine distinct result sets horizontally.

Physical Storage & Buffer Pool Mechanics

In modern database engines (such as PostgreSQL 12+ and SQLite 3.38+), non-recursive CTEs are inlined by the optimizer by default. This means the engine treats the CTE as an inline view, folding predicates and joins into the main query plan. However, CTEs can also be materialized (using the MATERIALIZED keyword in Postgres) to serve as optimization fences, forcing the engine to compute and cache the intermediate relation.

Recursive CTE Evaluation and Set Logic

A recursive CTE consists of two parts: an anchor member (which executes once to seed the initial result set) and a recursive member (which joins against the working table produced by the previous iteration). The recursion terminates automatically when the recursive member yields zero new rows. Set operators like UNION remove duplicate rows via sorting or hashing, whereas UNION ALL preserves all rows without deduplication overhead.

Worked Query Walkthrough: Traversing an Employee Management Hierarchy

WITH RECURSIVE OrgChart AS (
    -- Anchor Member: Root CEO
    SELECT employee_id, first_name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive Member: Subordinates
    SELECT e.employee_id, e.first_name, e.manager_id, o.depth + 1
    FROM employees e
    INNER JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT employee_id, first_name, manager_id, depth
FROM OrgChart
ORDER BY depth, employee_id;

Step-by-Step Row Evaluation Matrix

IterationWorking Table Rows ProcessedNew Rows GenerateddepthCumulative OrgChart Size
0 (Anchor)manager_id IS NULLAlice (ID: 1)11 row
1manager_id = 1Bob (2), Carol (3)23 rows
2manager_id IN (2, 3)Dan (4), Eva (5)35 rows
3manager_id IN (4, 5)[None - Terminate]-5 rows (Final output)

Runtime Trace Analysis

The anchor member selects Alice (the CEO with depth 1). In iteration 1, the recursive member joins Alice's ID with the employees table, discovering managers Bob and Carol (depth 2). In iteration 2, Bob and Carol discover Dan and Eva (depth 3). In iteration 3, Dan and Eva have no reports; the working table is empty, and the recursion halts gracefully.

Common Traps, Anti-Patterns & Debugging Solutions

1. Using UNION instead of UNION ALL

Problematic Syntax (Anti-Pattern)SELECT user_id FROM web_clicks UNION SELECT user_id FROM mobile_clicks;
Optimized AlternativeSELECT user_id FROM web_clicks UNION ALL SELECT user_id FROM mobile_clicks;

UNION forces the database to sort or hash both result sets to remove duplicate rows. If duplicates are acceptable or impossible, UNION ALL eliminates this significant CPU and memory penalty.

2. Infinite Loops in Recursive CTEs

Problematic Syntax (Anti-Pattern)WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums) SELECT * FROM nums;
Optimized AlternativeWITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 100) SELECT * FROM nums;

Recursive CTEs require a strict termination condition in the recursive member's WHERE clause. Without it, the query enters an infinite loop until aborted by stack overflow or timeout limits.

3. Mismatched Column Types in Set Operations

Problematic Syntax (Anti-Pattern)SELECT id, user_name FROM users UNION ALL SELECT id, created_at FROM audit_logs;
Optimized AlternativeSELECT id, user_name FROM users UNION ALL SELECT id, CAST(created_at AS TEXT) FROM audit_logs;

Set operations require identical column counts and compatible data types in corresponding positions. Attempting to union a text column with a timestamp causes a type mismatch error.

Production Strategy & Query Engineering Best Practices

Recursive CTEs are the gold standard for hierarchical data modeling and graph traversal in SQL. Practice writing tree-traversal queries, tracking depth levels, and detecting cyclical graph loops defensively.

Interactive WebAssembly SQL Playground

Client-Side SQLite Engine
Active Relational Schema & Preloaded Tables
TableKey ColumnsSample Row CountDescription
EmployeeEmpID, Name, DeptID, Salary, ManagerID8 rowsStaff directory with salaries and reporting structure
DepartmentDeptID, DeptName, Budget8 rowsOrganizational departments and annual budgets
ProjectProjectID, ProjectName, DeptID, Deadline5 rowsActive enterprise engineering projects
Interactive Query Workspace

Run custom SQL queries against the Employee, Department, Project schema above or experiment with alternative joins, filters, and aggregations in the interactive editor below.

Frequently Asked Questions on CTEs & Set Operators

What is the primary performance difference between UNION and UNION ALL?

UNION performs an implicit sorting or hashing pass across all records to eliminate duplicates, incurring significant memory and CPU cost. UNION ALL simply concatenates result sets without deduplication.

Can a CTE be referenced multiple times in the same query?

Yes. A major benefit of CTEs is that once defined, they can be referenced repeatedly across joins, subqueries, and set operations within the same statement.

How does a recursive CTE know when to stop executing?

A recursive CTE terminates when an iteration of the recursive member returns zero rows. When the working table becomes empty, the engine ends the recursive loop.

What is an optimization fence in the context of CTEs?

An optimization fence is a barrier that prevents the query planner from pushing predicates or joins into a CTE, forcing the CTE to be evaluated and materialized independently.

Explore Other SQL Practice Tracks