CTEs & Set Operators
Structure temporary query sets with WITH CTEs, and perform operations using UNION, EXCEPT, INTERSECT.
SQL Query Architecture & Optimization Guide
Common Table Expressions (WITH clauses) provide named temporary result sets that structure complex queries into clean, readable steps. Set operators (UNION, UNION ALL, INTERSECT, EXCEPT) combine distinct result sets vertically.
Modern SQL optimizers inline non-recursive CTEs directly into the main query execution graph. Recursive CTEs execute an anchor member once, then repeatedly execute a recursive member until an empty result set is produced.
Using UNION instead of UNION ALL introduces an expensive sorting and deduplication pass over all combined records. Always use UNION ALL when duplicates do not exist or duplicate retention is acceptable.
Interactive WebAssembly SQL Playground
Frequently Asked Questions on CTEs & Set Operators
What is the performance difference between UNION and UNION ALL?
UNION ALL concatenates datasets directly without deduplication overhead. UNION performs a sort and unique deduplication pass over all combined records, which is significantly slower on large tables.
How does a Recursive CTE terminate?
A Recursive CTE contains an anchor query and a recursive query joined by UNION ALL. The recursion loop automatically terminates when the recursive member returns zero rows.
Are CTEs materialized or inlined during query execution?
In modern database engines like PostgreSQL 12+ and SQLite 3.25+, CTEs are automatically inlined into the main query tree unless explicitly defined with WITH ... AS MATERIALIZED.