MySQL derived tables

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

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

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

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.

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;

+------------------+--------+-----------------+
| 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 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 lesson answers the same question a row at a time. This shape computes the averages once instead.

The alias is not optional

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

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 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 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.

Getting Started

Server Development

Using Extensions

Creating Extensions

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

How a derived table works

What it makes possible

The alias is not optional

Derived table or CTE

Summary

See also

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

​How a derived table works

​What it makes possible

​The alias is not optional

​Derived table or CTE

​Summary

​See also

How a derived table works

What it makes possible

The alias is not optional

Derived table or CTE

Summary

See also