Mastering SQL Common Table Expressions
CTE Core Mechanics
Beyond the Subquery
As your data questions become more complex, your SQL queries often follow suit. You've likely found yourself nesting subqueries—queries inside of other queries—to filter and shape data in multiple steps. While functional, this approach can quickly become difficult to read and debug. The logic is buried deep inside nested parentheses, forcing you to read from the inside out.
-- Find employees in departments with more than 10 people
-- who earn above the average salary for their department.
SELECT
first_name,
last_name,
salary
FROM employees
WHERE department_id IN (
-- Subquery 1: Get departments with > 10 employees
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10
) AND salary > (
-- Subquery 2: Get average salary for the employee's department
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = employees.department_id
);
Trying to follow the logic here involves jumping between different parts of the query. There’s a better way to organize these steps: Common Table Expressions, or CTEs.
A CTE is a temporary, named result set that you can reference within a
SELECT,INSERT,UPDATE, orDELETEstatement. Think of it as creating a temporary view that exists only for the duration of a single query.
The WITH Clause
You define a CTE using the WITH clause at the beginning of your query. This clause allows you to break down a complex problem into a series of simple, logical steps. The structure is straightforward: you give your temporary result set a name, followed by AS, and then the query that defines it, wrapped in parentheses.
WITH large_departments AS (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10
),
department_avg_salaries AS (
SELECT
department_id,
AVG(salary) as avg_dept_salary
FROM employees
GROUP BY department_id
)
SELECT
e.first_name,
e.last_name,
e.salary
FROM employees e
JOIN large_departments ld ON e.department_id = ld.department_id
JOIN department_avg_salaries das ON e.department_id = das.department_id
WHERE e.salary > das.avg_dept_salary;
This version is much easier to follow. The logic flows from top to bottom. First, we define large_departments. Then, we define department_avg_salaries. Finally, we use both of these named result sets in our main query. Each step is a self-contained, readable block.
Chaining and Scope
One of the most powerful features of CTEs is the ability to build them on top of each other. A CTE can reference any CTE defined before it in the same WITH clause. This allows you to create a data transformation pipeline within a single query.
WITH employees_in_usa AS (
-- Step 1: Filter for employees in the US
SELECT employee_id, department_id, salary
FROM employees
WHERE country = 'USA'
),
sales_department_employees AS (
-- Step 2: From the US employees, find those in Sales
SELECT employee_id, salary
FROM employees_in_usa
WHERE department_id = 5 -- Assuming 5 is the Sales department ID
)
-- Step 3: Calculate the average salary of US-based sales staff
SELECT AVG(salary) as average_sales_salary
FROM sales_department_employees;
Notice how sales_department_employees references employees_in_usa. This chaining makes the logic exceptionally clear. Each CTE performs one specific task, passing its results to the next step.
It's also important to understand a CTE's scope. The named result set is only available within the statement it's defined in. Once the final query finishes running, the CTEs (employees_in_usa and sales_department_employees) vanish. You cannot reference them in a subsequent, separate query without redefining them.
What is the primary advantage of using a Common Table Expression (CTE) over a complex nested subquery?
Which SQL keyword is used to begin the definition of one or more CTEs?
CTEs provide a cleaner, more maintainable way to write complex SQL. By breaking down your logic into named, sequential steps, you make your queries easier for others—and your future self—to understand and modify.