> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL common table expressions

> How the MySQL WITH clause names a query so the main query can read it, how to define several at once, and when a CTE reads better than a derived table.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

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](/docs/tutorial/derived-tables), 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

```sql theme={null}
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

```sql theme={null}
WITH counts AS (
  SELECT rating, COUNT(*) AS films FROM film GROUP BY rating
)
SELECT rating, films FROM counts WHERE films > 200 ORDER BY rating;
```

```text theme={null}
+--------+-------+
| 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.

```sql theme={null}
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;
```

```text theme={null}
+--------+-------+
| 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](/docs/guides/ctes-in-mysql) 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.

## See also

* [Derived tables](/docs/tutorial/derived-tables) — the same idea, inline
* [Recursive CTEs](/docs/tutorial/recursive-cte) — a CTE that reads itself
* [GROUP BY](/docs/tutorial/group-by) — what the inner step usually does
* [When a MySQL CTE beats a derived table](/docs/guides/ctes-in-mysql) — how the optimizer handles them