A derived table is a subquery in the FROM clause. MySQL treats its result like a temporary table that exists only for this query. It must have an alias.
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(50) NOT NULL
);
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
ordered_on DATE NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO customers (name, city) VALUES
('Maya Shah', 'Pune'),
('Rohan Verma', 'Delhi'),
('Sana Ali', 'Pune'),
('Tara Nair', 'Kochi'),
('Uday Singh', 'Delhi');
INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
(1, '2026-05-02', 680.00, 'delivered'),
(1, '2026-05-20', 1299.00, 'delivered'),
(2, '2026-05-21', 145.00, 'shipped'),
(3, '2026-06-01', 450.00, 'delivered'),
(3, '2026-06-05', 80.00, 'cancelled'),
(3, '2026-06-18', 999.00, 'shipped'),
(2, '2026-06-20', 220.00, 'pending');A summary table, then a filter on it
SELECT customer_id, order_count, spent
FROM (
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
GROUP BY customer_id
) AS per_customer
WHERE spent > 1000
ORDER BY spent DESC;+-------------+-------------+---------+
| customer_id | order_count | spent |
+-------------+-------------+---------+
| 1 | 2 | 1979.00 |
| 3 | 3 | 1529.00 |
+-------------+-------------+---------+
2 rows in setThe inner query makes one row per customer. The outer query treats it as a table and filters it. (You could also use HAVING. A derived table shines when you need to filter, join or aggregate again.)
Aggregate of aggregates
“What is the average number of orders per customer?” needs a count first, then an average:
SELECT ROUND(AVG(order_count), 2) AS avg_orders_per_customer
FROM (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) AS counts;+-------------------------+
| avg_orders_per_customer |
+-------------------------+
| 2.33 |
+-------------------------+
1 row in setYou cannot nest aggregate functions directly (AVG(COUNT(*)) is an error). The derived table solves it.
Join a summary to detail rows
SELECT c.name, o.id AS order_id, o.total, s.avg_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN (SELECT customer_id, ROUND(AVG(total), 2) AS avg_total FROM orders GROUP BY customer_id) AS s ON s.customer_id = c.id
WHERE o.total > s.avg_total
ORDER BY c.name, o.id;+-------------+----------+---------+-----------+
| name | order_id | total | avg_total |
+-------------+----------+---------+-----------+
| Maya Shah | 2 | 1299.00 | 989.50 |
| Rohan Verma | 7 | 220.00 | 182.50 |
| Sana Ali | 6 | 999.00 | 509.67 |
+-------------+----------+---------+-----------+
3 rows in setThe orders above that customer’s own average. The summary is computed once per customer, not once per order.
Rank or number rows, then filter
Window functions cannot be used in WHERE. A derived table lets you filter on them (Window Functions):
SELECT customer_id, id AS order_id, total
FROM (
SELECT customer_id, id, total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM orders
) AS ranked
WHERE rn = 1
ORDER BY customer_id;+-------------+----------+---------+
| customer_id | order_id | total |
+-------------+----------+---------+
| 1 | 2 | 1299.00 |
| 2 | 7 | 220.00 |
| 3 | 6 | 999.00 |
+-------------+----------+---------+
3 rows in setEach customer’s largest order.
Alias rules
Every derived table needs an alias, and every column in it needs a name:
SELECT * FROM (SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id);ERROR 1248 (42000): Every derived table must have its own aliasThe error says “Every derived table must have its own alias”. With the alias, also give the column a name if it is an expression:
SELECT t.customer_id, t.n FROM (SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id) AS t ORDER BY t.customer_id;+-------------+---+
| customer_id | n |
+-------------+---+
| 1 | 2 |
| 2 | 2 |
| 3 | 3 |
+-------------+---+
3 rows in setMerging vs materialization
MySQL has two ways to run a derived table:
- Merge: the derived table’s query is merged into the outer query, as if you had written it as a join. This is possible when the derived table has no aggregation,
LIMITorDISTINCT. It is the fastest. - Materialize: MySQL runs the inner query, stores the result in a temporary table, and reads from that. This is needed for
GROUP BY,LIMIT,UNIONand so on. MySQL 8 can add an index to the temporary table when it helps the join.
You rarely need to decide this. Look at EXPLAIN if a query with a derived table is slow: <derived2> appears as a table when it is materialized.
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id) AS t WHERE spent > 500;+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | NULL |
| 2 | DERIVED | orders | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in setDerived table vs CTE
A CTE (WITH name AS (...)) is the same idea with the query written before the main query, so it reads from top to bottom and can be reused:
WITH per_customer AS (
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS spent FROM orders GROUP BY customer_id
)
SELECT customer_id, order_count, spent FROM per_customer WHERE spent > 1000 ORDER BY spent DESC;+-------------+-------------+---------+
| customer_id | order_count | spent |
+-------------+-------------+---------+
| 1 | 2 | 1979.00 |
| 3 | 3 | 1529.00 |
+-------------+-------------+---------+
2 rows in setUse a CTE when the query has several steps or uses the same derived table twice (CTEs).
Derived table vs view
A view is a named, stored derived table (Views). Use a view when several queries need the same one.