Common Table Expressions (WITH)
Refactor nested queries into readable WITH clauses, chain CTEs, and learn the materialisation rules in PostgreSQL 12+.
What Is a CTE?
A Common Table Expression (CTE) is a named subquery defined with WITH:
WITH paid_orders AS (
SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, COUNT(*)
FROM paid_orders
GROUP BY user_id;Why Use CTEs?
Three big wins:
- Readability — break a 200-line query into named steps
- Reuse — reference the same intermediate result multiple times
- Recursion — only CTEs support recursive queries (next lesson)
All lessons in this course
- Scalar, Row, and Table Subqueries
- Correlated vs Non-Correlated Subqueries
- Common Table Expressions (WITH)
- Recursive CTEs for Hierarchies