Common Table Expressions
Overview
Subqueries can become incredibly messy. If you nest a subquery inside a subquery inside a JOIN, the SQL becomes completely unreadable (the 'spaghetti code' of SQL). A Common Table Expression (CTE) fixes this. Introduced via the WITH keyword, a CTE allows you to define a temporary, named result set at the absolute top of your file. It acts like a temporary view that exists only for the duration of the query, allowing you to break massive, complex logic into clean, readable, modular blocks.
Syntax
-- Syntax: WITH cte_name AS (query)
-- 1. Define the CTE at the top of the file
WITH HighValueOrders AS (
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 5000
)
-- 2. Use the CTE in the main query just like a normal table!
SELECT u.name, u.email, hvo.total_spent
FROM users u
JOIN HighValueOrders hvo
ON u.id = hvo.user_id;Common Pitfalls
- Assuming CTEs act as indexed, materialized tables. In many databases, a CTE is purely 'syntactic sugar' for a subquery. The database engine literally copies and pastes the CTE code into your main query under the hood. If you reference a heavy CTE 5 times in your main query, the database might execute that heavy calculation 5 separate times. (PostgreSQL 12+ optimizes this, but be careful).
- Forgetting the comma when chaining CTEs. You only write the
WITHkeyword once. If you need multiple CTEs, you must separate them with commas.
Interview Questions
Readability and reusability. CTEs allow developers to structure SQL sequentially (top-to-bottom), whereas nested subqueries are read inside-out. Furthermore, a single CTE can be referenced multiple times within the main query, whereas a subquery must be completely re-written each time.
Real-World Example
Chaining two CTEs together to calculate the percentage of total company revenue each department generates.
WITH DepartmentTotals AS (
SELECT department_id, SUM(salary) as dept_total
FROM employees
GROUP BY department_id
),
CompanyTotal AS (
-- You can reference PREVIOUS CTEs inside new CTEs!
SELECT SUM(dept_total) as grand_total
FROM DepartmentTotals
)
SELECT
d.dept_id,
d.dept_total,
(d.dept_total / c.grand_total) * 100 AS percentage_of_company
FROM DepartmentTotals d
CROSS JOIN CompanyTotal c;Check Your Knowledge
Test your understanding of Common Table Expressions with these quick questions.