MySQL common table expressions

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A common table expression gives a query a name, at the top, before the query that uses it. The result is the same as a derived table, and it reads in the order you think: define the pieces, then use them. Open the client with mysql -u root -p sakila before you start.

How a CTE works

WITH CTE_NAME AS (
  SELECT COLUMN_LIST FROM TABLE_NAME
)
SELECT COLUMN_LIST FROM CTE_NAME;

Replace CTE_NAME with a name for the intermediate result, COLUMN_LIST with the columns you want, and TABLE_NAME with the table the inner query reads. The main query then reads CTE_NAME as if it were a table. A CTE lasts for 1 statement. It is not stored, and the next query knows nothing about it.

Examples

1) One named step

WITH counts AS (
  SELECT rating, COUNT(*) AS films FROM film GROUP BY rating
)
SELECT rating, films FROM counts WHERE films > 200 ORDER BY rating;

+--------+-------+
| rating | films |
+--------+-------+
| PG-13  |   223 |
| NC-17  |   210 |
+--------+-------+
2 rows in set (0.00 sec)

The same rows the derived table lesson returns, with the counting lifted out of FROM and given a name.

2) Several steps, each built on the last

Separate CTEs with a comma, and a later one may read an earlier one. That is what a derived table cannot do without repeating itself.

WITH counts AS (
  SELECT rating, COUNT(*) AS films FROM film GROUP BY rating
),
busiest AS (
  SELECT MAX(films) AS most FROM counts
)
SELECT c.rating, c.films
FROM counts AS c
INNER JOIN busiest AS b ON c.films = b.most;

+--------+-------+
| rating | films |
+--------+-------+
| PG-13  |   223 |
+--------+-------+
1 row in set (0.00 sec)

busiest reads counts, and the main query reads both. Written as derived tables, the counting query would appear twice.

What a CTE gives you that a derived table does not

The rows are the same either way. 2 things differ:

A CTE carries a name, so a 3-stage query reads top to bottom. counts and busiest tell the next reader what each piece is for.
A CTE can be named more than once in the same statement. A derived table has to be written out again.

Whether the optimizer treats the 2 forms differently is a separate question, and When a MySQL CTE beats a derived table measures it.

Summary

Use WITH name AS (...) to name a query before the query that reads it.
Separate several CTEs with commas, and let a later one read an earlier one.
Expect a CTE to last for 1 statement only.
Prefer a CTE when a query has several stages or repeats a subquery.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How a CTE works

Examples

1) One named step

2) Several steps, each built on the last

What a CTE gives you that a derived table does not

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How a CTE works

​Examples

​1) One named step

​2) Several steps, each built on the last

​What a CTE gives you that a derived table does not

​Summary

​See also

How a CTE works

Examples

1) One named step

2) Several steps, each built on the last

What a CTE gives you that a derived table does not

Summary

See also