WITH Clause — Stage Your Query with Named Intermediate Results

Data used in this article — employee and department

A CTE (Common Table Expression) is a mechanism where you write WITH name AS (SELECT ...) to give a subquery a name, then reference that name in the queries that follow.

Because you can split a deeply nested subquery into stages, your code becomes much easier to read.

The material is the employee table (30 rows; the manager_id column points at the boss's emp_id, a self-referential structure) and the department table (6 rows).

You'll work through exercises that use CTEs to write multi-stage aggregations more readably.

Before starting the exercises, take a look at the column definitions and sample data of the two tables this article uses — employee and department.

① Run PRAGMA table_info(employee); and PRAGMA table_info(department); to check the column definitions of both tables.

② Run SELECT * FROM employee LIMIT 5; and SELECT * FROM department LIMIT 5; to preview the first 5 rows of each. Also look at how NULL appears in the manager_id column (employees who have no boss).

Build a named intermediate result with WITH — make multi-stage queries readable

When you write WITH name AS (SELECT ...), the result of the inner SELECT gets a name, and you can reference it from FROM or JOIN in the main query that follows.

Instead of writing the two-step process "first build an intermediate result, then use it to produce the final result" as a nested subquery, you can break it into a form that reads top to bottom.

A CTE is a temporary name that disappears once the main query finishes — it doesn't create a real table.

The syntax is WITH name AS ( ... ) SELECT ... FROM name ...;.

-- Bundle high earners into the CTE high_earner,
-- then join with department to attach the department name
WITH high_earner AS (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
)
SELECT h.name, h.salary, d.dept_name
FROM high_earner h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

In the code above, the name high_earner carves out the intermediate result "employees with salary >= 7,000,000", and the main query references it like a table with FROM high_earner.

Here's how that flow looks as a diagram.

You can write the same processing with a subquery (a SELECT nested inside the parentheses of another SELECT).

Let's rebuild the previous WITH version as a subquery instead of a CTE.

-- The same processing written as a subquery (derived table)
-- A SELECT lives inside FROM's parentheses, so you read outer → inner → outer
SELECT h.name, h.salary, d.dept_name
FROM (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
) AS h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

The result is exactly the same as the WITH version, but with the subquery version you have to dive into a SELECT nested inside FROM to figure out what the inner SELECT is doing.

The WITH version carves out the intermediate result with the name `high_earner`, so the two stages "① filter for high earners → ② attach the department name" read straight from top to bottom.

The gap grows as you add more intermediate stages — by the time you have 3 or 4 stages, deeply nested subqueries become hard to follow.

Requirement: "I want to carve out employees who have no boss (executive-level employees with manager_id = NULL) and list them alongside their department names." Solve it in two stages with a CTE. (If you run it correctly, the explanation will appear.)

① With WITH, define a named CTE executive that pulls the rows from employee where manager_id is NULL (emp_id, name, dept_id).

② LEFT JOIN that CTE with department on dept_id, and return name, dept_id, dept_name.

③ Order the result by name ascending.

Chain multiple WITH clauses — split multi-stage aggregation into one step at a time

After WITH, you can list multiple CTEs separated by commas.

The shape is WITH a AS (...), b AS (...) SELECT ..., and a later CTE can reference an earlier one.

This lets you split a multi-stage aggregation ("compute the average per department" → "pick employees above that average" → "attach the department name") into named stages one at a time, so even in a long query you can keep each stage's role separate.

-- ① dept_avg: average salary per department
-- ② top_earner: employees above their own department's average
-- ③ Main query: attach the department name and output
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
),
top_earner AS (
  SELECT e.name, e.dept_id, e.salary
  FROM employee e
  JOIN dept_avg da ON e.dept_id = da.dept_id
  WHERE e.salary > da.avg_salary
)
SELECT d.dept_name, t.name, t.salary
FROM top_earner t
JOIN department d ON t.dept_id = d.dept_id
ORDER BY d.dept_name, t.salary DESC;

Requirement: "I want to compute the average salary of each department, then list only the employees whose salary exceeds that average, with their department name attached." Solve it by chaining two CTEs.

① For the first CTE, aggregate employee by dept_id and produce the average salary per department. Exclude employees whose dept_id is NULL from the average.

② For the second CTE, join employee with the first CTE on dept_id and pick the employees (name, dept_id, salary) whose salary is greater than that department's average.

③ In the main query, join the second CTE with department on dept_id and return dept_name, name, salary. Sort by department name ascending, and within the same department by salary descending.