What is a common table expression (CTE)?

A Common Table Expression (CTE) is a temporary named result set defined within a WITH clause that can be referenced within the main query. It improves readability by breaking complex queries into named logical steps. WITH high_earners AS (SELECT * FROM employees WHERE salary > 100000) SELECT dept_id, COUNT(*) FROM high_earners GROUP BY dept_id. Recursive CTEs reference themselves and are used to traverse hierarchical data (organizational charts, file systems, bills of materials): WITH RECURSIVE subordinates AS .... CTEs are materialized once (in some RDBMS) or treated as an inline view — check your RDBMS documentation for optimization behavior.