No history yet

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, or DELETE statement. 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.

Quiz Questions 1/4

What is the primary advantage of using a Common Table Expression (CTE) over a complex nested subquery?

Quiz Questions 2/4

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.