Common Table Expressions (CTE)
SQL · Extra Concepts
Theory
A Common Table Expression (CTE) is a named, temporary result set that you define at the top of a query with WITH name AS (SELECT ...), and then use in the main query as if it were a table. It exists only while that one statement runs; nothing is saved in the database.
CTEs let you break a big query into readable steps. Instead of nesting a subquery three levels deep, you write step 1 (filter high earners), give it a name, then write step 2 (count them by department) using that name. You can define several CTEs separated by commas, and later ones can refer to earlier ones, so the query reads top to bottom like a recipe. Each CTE can also be referenced more than once in the main query. CTEs are supported by PostgreSQL, SQL Server, Oracle, Snowflake, MySQL, BigQuery and SQLite. Whether the database computes a CTE once or inlines it like a subquery depends on its optimizer, so treat CTEs mainly as a readability tool.
Why we use it
Deeply nested subqueries are hard to read, debug and reuse. A CTE gives each step a meaningful name, so complex logic becomes a series of small, testable blocks.
When to use it
Use CTEs for multi-step transformations, for reusing the same intermediate result twice and for recursive queries. For a simple one-off filter a plain WHERE is enough. If many queries need the same logic, create a view instead.
How it works
- Query begins with WITH
- Define step 1: high_earners AS (SELECT ... WHERE salary > 70000)
- Need more steps? Yes: Add a CTE after a comma. No: Move to the main query.
- Main SELECT reads from the CTE names like tables
- CTEs disappear when the statement finishes
- Return the final result
Syntax
WITH cte_name AS (
SELECT ...
)
SELECT * FROM cte_name;Example
WITH high_earners AS (
SELECT * FROM employees WHERE salary > 70000
)
SELECT department, COUNT(*) FROM high_earners GROUP BY department;Key points
- Improves readability over deeply nested subqueries
- Scoped to just the one statement that follows it
- Chain several CTEs with commas; later ones can use earlier ones
- Supported by all major databases, including SQLite and MySQL
- Whether a CTE is computed once or inlined depends on the optimizer