After this lesson you can break a long query into named steps with WITH, so that each part is small enough to read and test on its own.
Naming a subquery
A common table expression, usually called a CTE, gives a subquery a name before the main query starts. The main query then uses the name as if it were a table. Here is the department average salary example from the subqueries lesson, rewritten with a CTE:
WITH dept_pay AS (
SELECT department_id, ROUND(AVG(salary), 0) AS average_salary
FROM employees
GROUP BY department_id
)
SELECT d.name AS department, p.average_salary
FROM dept_pay p
INNER JOIN departments d ON d.id = p.department_id
WHERE p.average_salary > 80000
ORDER BY p.average_salary DESC;+-------------+----------------+
| department | average_salary |
+-------------+----------------+
| Engineering | 108333.0 |
| Finance | 92500.0 |
| Sales | 86400.0 |
| Marketing | 80667.0 |
+-------------+----------------+Read the query from the top: first define dept_pay, then use it. The result is the same as before, but the pieces are in the order you think about them, instead of the aggregation being buried inside a FROM. The CTE is just a name. It is not saved anywhere, and disappears when the statement finishes.
Chaining several CTEs
You can define more than one CTE. Write WITH once and separate the definitions with commas. A later CTE may use an earlier one. Suppose you want, for each department, its headcount and the budget of its projects, compared:
WITH headcount AS (
SELECT department_id, COUNT(*) AS employees
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
),
budgets AS (
SELECT department_id, SUM(budget) AS total_budget
FROM projects
GROUP BY department_id
)
SELECT d.name AS department,
COALESCE(h.employees, 0) AS employees,
COALESCE(b.total_budget, 0) AS total_budget
FROM departments d
LEFT JOIN headcount h ON h.department_id = d.id
LEFT JOIN budgets b ON b.department_id = d.id
ORDER BY d.id;+-------------+-----------+--------------+
| department | employees | total_budget |
+-------------+-----------+--------------+
| Engineering | 6 | 1050000 |
| Sales | 5 | 200000 |
| Marketing | 3 | 120000 |
| Finance | 2 | 300000 |
| Support | 3 | 0 |
| Research | 0 | 0 |
+-------------+-----------+--------------+Each CTE does one small job, and the final SELECT just stitches them together. Left joins keep Research and Support in the result even though one has no employees and the other no projects, and COALESCE turns the resulting NULLs into zeros. Because each CTE produces one row per department before the join, nothing is multiplied.
A CTE that builds on another
A second CTE can read the first. Here the first step finds the hours each employee has logged in total, and the second averages those totals for each department:
WITH employee_hours AS (
SELECT e.id, e.department_id, SUM(a.hours) AS total_hours
FROM employees e
INNER JOIN assignments a ON a.employee_id = e.id
GROUP BY e.id, e.department_id
),
department_hours AS (
SELECT department_id, COUNT(*) AS people, ROUND(AVG(total_hours), 1) AS average_hours
FROM employee_hours
GROUP BY department_id
)
SELECT d.name AS department, dh.people, dh.average_hours
FROM department_hours dh
INNER JOIN departments d ON d.id = dh.department_id
ORDER BY dh.average_hours DESC;+-------------+--------+---------------+
| department | people | average_hours |
+-------------+--------+---------------+
| Engineering | 5 | 460.0 |
| Sales | 5 | 210.0 |
| Finance | 2 | 175.0 |
| Marketing | 3 | 160.0 |
+-------------+--------+---------------+Doing this with nested subqueries means putting one inside another inside a third. With CTEs you read it top to bottom: hours per employee, then the average per department, then names.
Using one CTE twice
A CTE name can appear more than once in the main query, which saves repeating the same subquery. Show each department's payroll next to the company-wide payroll and its share of the total:
WITH payroll AS (
SELECT department_id, SUM(salary) AS total
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
)
SELECT d.name AS department,
p.total AS payroll,
(SELECT SUM(total) FROM payroll) AS company_payroll,
ROUND(100.0 * p.total / (SELECT SUM(total) FROM payroll), 1) AS percent_of_total
FROM payroll p
INNER JOIN departments d ON d.id = p.department_id
ORDER BY p.total DESC;+-------------+---------+-----------------+------------------+
| department | payroll | company_payroll | percent_of_total |
+-------------+---------+-----------------+------------------+
| Engineering | 650000 | 1706000 | 38.1 |
| Sales | 432000 | 1706000 | 25.3 |
| Marketing | 242000 | 1706000 | 14.2 |
| Support | 197000 | 1706000 | 11.5 |
| Finance | 185000 | 1706000 | 10.8 |
+-------------+---------+-----------------+------------------+The name payroll is used in the FROM and twice in the SELECT list. Whether the database computes the CTE once or each time is up to its optimiser, and differs between systems, so do not rely on a CTE as a way to force a result to be cached.
CTE or subquery?
A CTE and a derived table in FROM usually do the same job. Choose a CTE when:
- the query has several steps and you want to read them in order;
- you use the same intermediate result more than once;
- you want to test a step by running its
SELECTalone; - you need recursion, which is the next lesson.
A short subquery in WHERE is often just as clear in place. Do not wrap everything in WITH out of habit. Remember, too, that a CTE is a temporary name for one query. To reuse a query in many statements, store it as a view, which comes later in the course.
Differences between databases
- CTEs work in MySQL 8.0 and newer, PostgreSQL, SQLite and SQL Server. MySQL 5.7 and older do not support them, and you must use a derived table instead.
- In SQL Server, if a
WITHstatement is not the first in a batch, the statement before it must end with a semicolon. Ending every statement with;avoids the problem. - Some databases choose whether to compute a CTE once or inline it each time it is used, and the rules vary, so test before depending on it for speed.
For the MySQL version, see Common table expressions in MySQL.
Common mistakes
- Expecting a CTE to persist. It only lives for the one statement that follows. Use a view to keep a query.
- Putting a comma after the last CTE. The commas go between CTEs only, not before the main
SELECT. - Writing WITH twice. Use one
WITH, then commas. - Using a CTE name before it is defined. A CTE can only use CTEs defined above it.
- Assuming a CTE is automatically faster. It is mostly a readability tool.
Exercise
Write one query with two CTEs. The first, project_hours, gives the total hours per project. The second, project_owner, gives each project with its owning department's name. Join them to show the project, its owning department and the total hours, ordered by hours, with the most hours first.
Show one possible answer
WITH project_hours AS (
SELECT project_id, SUM(hours) AS total_hours
FROM assignments
GROUP BY project_id
),
project_owner AS (
SELECT p.id, p.name AS project, d.name AS department
FROM projects p
INNER JOIN departments d ON p.department_id = d.id
)
SELECT o.project, o.department, h.total_hours
FROM project_owner o
INNER JOIN project_hours h ON h.project_id = o.id
ORDER BY h.total_hours DESC;+-------------------+-------------+-------------+
| project | department | total_hours |
+-------------------+-------------+-------------+
| Customer Portal | Engineering | 1160 |
| Mobile App | Engineering | 1100 |
| Q3 Sales Push | Sales | 1050 |
| Spring Campaign | Marketing | 480 |
| Billing Migration | Finance | 390 |
+-------------------+-------------+-------------+Key points
- A common table expression (CTE) names a query with WITH name AS (...) so the main query can use it like a table.
- Several CTEs can be chained with commas, and a later one can use an earlier one.
- A CTE only exists for the one statement that follows it. It is not saved in the database.
- CTEs make long queries readable, and let you refer to the same intermediate result more than once.
- Support: MySQL 8.0 and newer, PostgreSQL, SQLite and SQL Server. SQL Server needs a semicolon before WITH.