(opens in a new tab)

MySQL Error 1248: Derived Table Must Have Alias

November 3, 2026 by Chat2DB Team

You wrap a working query in another SELECT, and MySQL complains:

SELECT * FROM (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
);

ERROR 1248 (42000): Every derived table must have its own alias

A derived table is a subquery in the FROM clause. MySQL treats it like a table for the rest of the statement, and every table in FROM needs a name. The fix is usually a single word after the closing parenthesis:

SELECT * FROM (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS t;

+-------------+--------+
| customer_id | total  |
+-------------+--------+
|           1 | 110.00 |
|           2 |  15.00 |
|           3 | 230.00 |
+-------------+--------+

The rest of this guide covers the cases that are harder to spot: nested subqueries, joins, UNION, VALUES, JSON_TABLE, and statements that update or delete rows. It also covers the errors that appear right after you add the alias, and the differences from other databases. Every statement ran on MySQL 8.4.11 against this sample data:

CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT,
  amount DECIMAL(10,2), status VARCHAR(10)
);
INSERT INTO orders VALUES
  (1, 1, 40, 'paid'), (2, 1, 70, 'paid'), (3, 2, 15, 'paid'),
  (4, 3, 200, 'open'), (5, 3, 30, 'paid');
 
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Sam');

Why MySQL Needs the Alias

The columns of a derived table have to be referred to somehow, and with no name the outer query has no way to write t.total. The SQL standard requires a correlation name here too, so MySQL is applying the rule strictly. It applies even when the outer query never uses the name, as in SELECT * or SELECT COUNT(*).

The word AS is optional, so ) t and ) AS t mean the same thing. The name itself is arbitrary, but a meaningful one helps the next reader. It only has to be unique among the tables at the same query level.

An alias belongs to the derived table and not to anything inside it. The inner query may have its own aliases on columns or tables, but they do not satisfy the rule, because the alias must come after the closing parenthesis.

Where the Rule Does Not Apply

Only subqueries in the FROM clause are derived tables. A subquery anywhere else returns values rather than a table, so it needs no alias. These both ran without one:

SELECT id FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 100);
 
SELECT id,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS n
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

This is why the mistake is so common. A subquery in WHERE works with no name, so it feels natural to write one in FROM the same way.

Common Cases

Counting Rows of a Subquery

This is probably the most frequent trigger, since COUNT(*) over a subquery feels complete without a name:

SELECT COUNT(*) FROM (SELECT DISTINCT customer_id FROM orders);

ERROR 1248 (42000): Every derived table must have its own alias

SELECT COUNT(*) FROM (SELECT DISTINCT customer_id FROM orders) AS d;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

Joining to a Subquery

In a join, the alias goes before ON. Without it, the query also has no way to qualify columns:

SELECT c.name, t.total
FROM customers c
JOIN (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) ON customer_id = c.id;

ERROR 1248 (42000): Every derived table must have its own alias

SELECT c.name, t.total
FROM customers c
JOIN (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS t ON t.customer_id = c.id;

Nested Subqueries

Every level needs its own alias. It is easy to add one to the outer level and miss the inner one, and the error does not say which is missing:

SELECT * FROM (
  SELECT * FROM (SELECT id FROM orders) WHERE id > 1
) AS o;

ERROR 1248 (42000): Every derived table must have its own alias

SELECT * FROM (
  SELECT * FROM (SELECT id FROM orders) AS i WHERE id > 1
) AS o;

To find the missing one in a long query, format it so that each closing parenthesis sits on its own line, then look for any ) of a FROM subquery with no name after it. The free SQL Formatter (opens in a new tab) makes this quick.

UNION Inside a Subquery

The whole UNION is one derived table, so it takes one alias, placed after the outer parenthesis:

SELECT COUNT(*) FROM (
  SELECT id FROM orders
  UNION ALL
  SELECT id FROM customers
) AS u;

+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+

DELETE and UPDATE

These often use a derived table to get around error 1093, which forbids selecting from the table you are changing. That extra layer needs an alias too:

DELETE FROM orders
WHERE id IN (
  SELECT id FROM (SELECT id FROM orders WHERE amount < 20)
);

ERROR 1248 (42000): Every derived table must have its own alias

DELETE FROM orders
WHERE id IN (
  SELECT id FROM (SELECT id FROM orders WHERE amount < 20) AS x
);

VALUES as a Table

MySQL 8.0.19 added the VALUES statement, which can serve as a row source. It needs an alias as well, and you will usually want to name its columns at the same time:

SELECT * FROM (VALUES ROW(1, 'a'), ROW(2, 'b')) AS v (id, label);

+----+-------+
| id | label |
+----+-------+
|  1 | a     |
|  2 | b     |
+----+-------+

LATERAL Derived Tables

A LATERAL subquery can refer to columns of earlier tables, and it is still a derived table, so it needs a name too. MySQL 8.0.14 and later support it:

SELECT c.name, t.total
FROM customers c,
LATERAL (
  SELECT SUM(amount) AS total
  FROM orders o
  WHERE o.customer_id = c.id
) AS t;

+------+--------+
| name | total  |
+------+--------+
| Ada  | 110.00 |
| Lin  |  15.00 |
| Sam  | 230.00 |
+------+--------+

Views

A view definition follows the same rule. CREATE OR REPLACE VIEW v1 AS SELECT * FROM (SELECT id FROM orders); fails with error 1248 at creation time, so a migration script can stop on it even though no data is involved.

JSON_TABLE

JSON_TABLE is not a derived table, but it has the same rule with its own error number:

SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS (v INT PATH '$'));

ERROR 3667 (42000): Every table function must have an alias

Add AS jt after the closing parenthesis to fix it.

Errors That Appear After You Add the Alias

Fixing 1248 often reveals the next problem, because MySQL can now resolve the derived table's columns.

Duplicate column names. The columns of a derived table must be unique, even if a plain SELECT would accept the same list:

SELECT * FROM (
  SELECT o.id, c.id
  FROM orders o JOIN customers c ON c.id = o.customer_id
) AS j;

ERROR 1060 (42S21): Duplicate column name 'id'

The query inside runs happily on its own, returning two columns named id, which is what makes this confusing. Alias the columns:

SELECT * FROM (
  SELECT o.id AS order_id, c.id AS customer_id
  FROM orders o JOIN customers c ON c.id = o.customer_id
) AS j
LIMIT 1;

Unnamed expression columns. A column such as SUM(amount) is awkward to refer to from the outer query. Either alias it inside, or use a derived column list, which MySQL 8.0.19 and later support:

SELECT * FROM (
  SELECT customer_id, SUM(amount)
  FROM orders
  GROUP BY customer_id
) AS t (cust, total);

+------+--------+
| cust | total  |
+------+--------+
|    1 | 110.00 |
|    2 |  15.00 |
|    3 | 230.00 |
+------+--------+

Inner aliases are not visible outside. After the alias, refer to columns through the new name. Writing o.amount in the outer query fails when o was defined inside the subquery, with error 1054. Use the derived table's alias, as in t.amount.

Two derived tables with one name. Aliases must be unique at each query level. Copying a subquery and leaving the name as it was gives a different error:

SELECT * FROM (SELECT id FROM orders) AS t, (SELECT id FROM customers) AS t;

ERROR 1066 (42000): Not unique table/alias: 't'

When the Query Comes from a Framework

Often nobody typed the failing SQL. Query builders and pagination plugins wrap your query to count its rows, and the wrapper needs a name:

SELECT COUNT(*) FROM (<your query>) AS tmp_count;

If a library generates this without an alias, upgrade it, because mature libraries all include one. In your own code, look for string concatenation that adds parentheses around a query, and add the alias there. Query builders have a place for the name: Laravel's fromSub($query, 't') takes it as an argument, and SQLAlchemy's .subquery('t') does the same. With a raw SQL expression, the name must be in the string, as in DB::raw('(SELECT ...) AS t').

Reporting tools do this as well. Many business intelligence products wrap a pasted query in an outer SELECT to apply filters, so a statement that runs fine alone may fail there for other reasons, such as the duplicate column names shown above. Giving each output column a unique alias avoids that.

Does the Derived Table Affect Performance?

The alias has no cost, but the derived table may. MySQL merges simple derived tables into the outer query, so they behave as though the subquery were not there. EXPLAIN makes the difference visible. This simple subquery is merged, and it shows one SIMPLE row:

EXPLAIN SELECT * FROM (SELECT id, amount FROM orders) AS t WHERE amount > 100;

+----+-------------+--------+------+-------------+
| id | select_type | table  | type | Extra       |
+----+-------------+--------+------+-------------+
|  1 | SIMPLE      | orders | ALL  | Using where |
+----+-------------+--------+------+-------------+

A subquery with GROUP BY cannot be merged, so MySQL materializes it into an internal temporary table first, and the plan has a <derived2> row:

EXPLAIN SELECT * FROM (
  SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
) AS t WHERE total > 100;

+----+-------------+------------+------+-----------------+
| id | select_type | table      | type | Extra           |
+----+-------------+------------+------+-----------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL            |
|  2 | DERIVED     | orders     | ALL  | Using temporary |
+----+-------------+------------+------+-----------------+

I removed some columns from those plans so they fit. If you filter a materialized derived table, try to push the condition inside the subquery, so that it reduces the rows before grouping.

Use a CTE Instead

If you have several levels of nesting, a common table expression is often clearer. Each step has a name up front, and there is no parenthesis to miss:

WITH t AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM t WHERE total > 100;

+-------------+--------+
| customer_id | total  |
+-------------+--------+
|           1 | 110.00 |
|           3 | 230.00 |
+-------------+--------+

CTEs need MySQL 8.0 or later. They also help when a result is used more than once, since you define it a single time. See CTE vs subquery vs view to compare the options.

How Other Databases Differ

SQL that runs elsewhere may fail on MySQL, which is a common source of this error during a migration.

I confirmed the PostgreSQL 16+ behavior on PostgreSQL 18.6, where SELECT * FROM (SELECT 1 AS n); returned a row. So a query written for a current PostgreSQL server can fail when you move it to MySQL or to an older PostgreSQL. Adding an alias is harmless everywhere, so always include one in portable SQL. The free PostgreSQL to MySQL Converter (opens in a new tab) can help with other syntax differences.

Checklist

1. Find each subquery in a FROM or JOIN.
2. Make sure each closing parenthesis is followed by a name, at every nesting level.
3. In a join, put the alias before ON.
4. For UNION, use one alias for the whole group.
5. After adding the alias, fix duplicate column names and outer references.
6. Consider a CTE if the nesting has grown hard to read.

Spot the Missing Alias Faster

Long generated queries are where this error hides. In Chat2DB (opens in a new tab) you can format a query to reveal its nesting, run each subquery on its own, and ask the AI assistant to rewrite a nested query as a CTE. You can also use it in the browser at app.chat2db.ai (opens in a new tab).