> ## 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 derived tables

> How a subquery in the FROM clause becomes a table you can query, why MySQL insists it has an alias, and what it lets you do that a plain query cannot.

<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 subquery in `WHERE` supplies a value. A subquery in `FROM` supplies a whole
table, which the outer query then reads as if it were real. That is a derived
table, and it is how you query the result of a query.

Open the client with `mysql -u root -p sakila` before you start.

## How a derived table works

```sql theme={null}
SELECT COLUMN_LIST
FROM (SELECT COLUMN_LIST FROM TABLE_NAME) AS ALIAS_NAME;
```

Replace `COLUMN_LIST` with the columns you want, `TABLE_NAME` with the table the
inner query reads, and `ALIAS_NAME` with a name for the result. The inner query's column names,
including any aliases, become the derived table's columns.

The server builds the inner result first, then runs the outer query over it.

## What it makes possible

A `GROUP BY` replaces the detail rows with 1 row per group, so a grouped query
cannot show you the rows themselves. A derived table keeps both: group in the
inner query, then join the result back to the detail.

This lists films longer than the average for their own rating, and shows that
average beside each one.

```sql theme={null}
SELECT f.title, f.length, r.mean_for_rating
FROM film AS f
INNER JOIN (
  SELECT rating, AVG(length) AS mean_for_rating
  FROM film
  GROUP BY rating
) AS r ON r.rating = f.rating
WHERE f.length > r.mean_for_rating
ORDER BY f.rating, f.title
LIMIT 5;
```

```text theme={null}
+------------------+--------+-----------------+
| title            | length | mean_for_rating |
+------------------+--------+-----------------+
| AFFAIR PREJUDICE |    117 |        111.0506 |
| AFRICAN EGG      |    130 |        111.0506 |
| ALAMO VIDEOTAPE  |    126 |        111.0506 |
| ATLANTIS CAUSE   |    170 |        111.0506 |
| BAKED CLEOPATRA  |    182 |        111.0506 |
+------------------+--------+-----------------+
5 rows in set (0.00 sec)
```

Neither `WHERE` nor [HAVING](/docs/tutorial/having) can write this on its own.
`HAVING` filters groups and would have thrown the titles away; `WHERE` runs
before any average exists. The 5 averages are worked out once in the inner
query, and the join attaches the right one to every film.

The [Correlated subqueries](/docs/tutorial/correlated-subqueries) lesson answers the
same question a row at a time. This shape computes the averages once instead.

## The alias is not optional

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

```text theme={null}
ERROR 1248 (42000): Every derived table must have its own alias
```

`ERROR 1248` always means this, and the fix is always the same: put
`AS SOME_NAME` after the closing bracket. The name can be anything, and it is
worth making it describe the rows, because a reader meets it before they read
the query that produced it.

## Derived table or CTE

A [common table expression](/docs/tutorial/cte) does the same job with the subquery
lifted out to the top and given a name. The 2 forms produce the same rows, and
the next lesson covers the difference.
[When a MySQL CTE beats a derived table](/docs/guides/ctes-in-mysql) measures which
one the optimizer treats differently.

## Summary

* Use a derived table to query the result of a query.
* Give it an alias, because `ERROR 1248` is what you get without one.
* Expect the inner query's column aliases to become the derived table's columns.
* Reach for it when you need to filter or join on a computed column.
* Prefer a CTE once the query has several stages or repeats the same subquery.

## See also

* [Common table expressions](/docs/tutorial/cte) — the same idea, written at the top
* [HAVING](/docs/tutorial/having) — filtering groups without a derived table
* [Subqueries](/docs/tutorial/subqueries) — a subquery used as a value instead
* [When a MySQL CTE beats a derived table](/docs/guides/ctes-in-mysql) — when the optimizer treats the 2 differently