Common Table Expressions (CTEs)
What you'll learn: How to use WITH clauses to create named temporary result sets that make complex queries easier to read and maintain.
The Readability Problem
You've learned how to write subqueries in the FROM clause—but when you need multiple subqueries or deeply nested logic, your SQL can quickly become a tangled mess. Common Table Expressions (CTEs) solve this by letting you name and define temporary result sets before your main query, making your code flow like a clear, step-by-step recipe.
What is a CTE?
A CTE is a named temporary result set defined with the WITH keyword. Think of it like declaring a variable before you use it—instead of cramming everything into one giant query, you break it into labeled pieces.
Basic syntax:
WITH cte_name AS (
SELECT column1, column2
FROM table1
WHERE condition
)
SELECT *
FROM cte_name;
Why Use CTEs?
Readability: Instead of this nested nightmare:
SELECT * FROM (
SELECT * FROM (
SELECT id, name FROM users WHERE active = 1
) AS inner_query
WHERE name LIKE 'A%'
) AS outer_query;
You can write:
WITH active_users AS (
SELECT id, name FROM users WHERE active = 1
),
filtered_users AS (
SELECT * FROM active_users WHERE name LIKE 'A%'
)
SELECT * FROM filtered_users;
Each step has a meaningful name, and the query reads top-to-bottom.
Multiple CTEs
You can define several CTEs in one statement, separating them with commas. Each CTE can reference earlier ones, building complexity gradually.
Key Takeaway: CTEs use WITH clauses to create named, temporary result sets that replace deeply nested subqueries with readable, step-by-step logic—your future self (and teammates) will thank you.