SQL Common Table Expressions (CTEs)
intermediateadvanced
2 min read
What are CTEs?
A Common Table Expression (CTE) is a temporary, named result set defined with the WITH keyword. It exists only for the duration of the query and makes complex queries more readable.
Syntax
WITH cte_name AS (
SELECT ...
)
SELECT * FROM cte_name;Multiple CTEs:
WITH cte1 AS (...),
cte2 AS (...)
SELECT * FROM cte1 JOIN cte2 ON ...;When to Use
- Breaking a complex query into logical steps
- Recursive queries (tree traversal, hierarchies)
- Reusing the same subquery multiple times
- Improving readability of multi-step transformations
Key Points
- Readability — CTEs name intermediate steps, making the query self-documenting.
- Multiple CTEs — Chain several CTEs separated by commas.
- Recursive CTE — Use
WITH RECURSIVEfor hierarchical data. - Materialization — In PostgreSQL 12+, CTEs may be inlined by the optimizer. Use
MATERIALIZEDhints if needed. - Scope — A CTE is visible only to the query that follows it.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL Common Table Expressions (CTEs) Challenge
Write a query that solve this task: use a CTE to find high-spending students.
Expected result
Students who have spent more than 500, with their total.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are CTEs?
Initializing database...Each run starts from fresh sample data.
More Examples
Recursive CTE for employee hierarchy
Traverse an employee-manager tree.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Are CTEs faster than subqueries?
Not necessarily. In PostgreSQL 12+, CTEs can be inlined like subqueries. Use them primarily for readability.
What is a recursive CTE?
A recursive CTE references itself. It has a base case and a recursive step joined with UNION ALL. PostgreSQL evaluates it iteratively until no new rows are produced.