A subquery in FROM fails with: Every derived table must have its own alias
ERROR 1248 (42000): Every derived table must have its own aliasShort answer
Any subquery used in a FROM or JOIN clause is a derived table, and MySQL requires it to have a name. Put an alias after the closing parenthesis: FROM (SELECT ...) AS t. If the inner SELECT returns two columns with the same name, give them distinct names too.
Fix it fast
1Find out which cause you have
Find each SELECT inside a FROM or JOIN clause. The one whose closing parenthesis is followed directly by WHERE, JOIN, ON, GROUP BY or a semicolon has no alias.
-- fails SELECT AVG(total) FROM (SELECT SUM(price) AS total FROM items GROUP BY order_id);2Match what you see, then jump to its fix
- 1
The closing parenthesis of the subquery is followed by nothing, or by a clause keyword.The derived table has no aliasFix - 2
Error 1248 comes back after you added one alias to a query with subqueries inside subqueries.The alias is there but one nested subquery still has noneFix - 3
The subquery returns expressions such as 1, 2 or SUM(price) with no name.You want the columns renamed, not just the tableFix
- 1
What does the error look like?
ERROR 1248 (42000): Every derived table must have its own alias
Cause 1 of 3
The derived table has no alias
1Check
The manual says the [AS] tbl_name part is mandatory because every table in a FROM clause must have a name. This applies to each subquery in a JOIN too.
-- fails
SELECT x.id FROM orders x
JOIN (SELECT order_id, SUM(price) AS total FROM items GROUP BY order_id) ON x.id = order_id;2Fix
Name every derived table, and refer to its columns through that name.
SELECT AVG(t.total)
FROM (SELECT SUM(price) AS total FROM items GROUP BY order_id) AS t;
SELECT x.id, i.total
FROM orders x
JOIN (SELECT order_id, SUM(price) AS total FROM items GROUP BY order_id) AS i
ON x.id = i.order_id;3Verify
Run the corrected query; it returns rows instead of error 1248.
SELECT * FROM (SELECT 1 AS a, 2 AS b) AS dt;Confirmed by dev.mysql.com
Cause 2 of 3
The alias is there but one nested subquery still has none
1Check
Each level of nesting is its own derived table. Fixing the outer one leaves an inner FROM (SELECT ...) unnamed.
-- inner derived table is still unnamed
SELECT * FROM (SELECT * FROM (SELECT 1 AS a)) AS outer_t;2Fix
Give every level a different alias.
SELECT * FROM (SELECT * FROM (SELECT 1 AS a) AS inner_t) AS outer_t;3Verify
The query runs and returns one row.
SELECT * FROM (SELECT * FROM (SELECT 1 AS a) AS inner_t) AS outer_t;Confirmed by dev.mysql.com
Cause 3 of 3
You want the columns renamed, not just the table
1Check
A derived table takes its column names from its select list. Columns without a name are awkward to reference in the outer query, and duplicate names are not allowed inside the derived table.
SELECT * FROM (SELECT 1, 2, 3, 4) AS dt;2Fix
Add an alias per column in the select list, or list the column names after the table alias. The count of names must equal the count of columns.
SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);3Verify
The result columns are called a, b, c and d.
SELECT dt.a, dt.d FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);Confirmed by dev.mysql.com
How do I stop it coming back?
- Write the alias at the moment you type the closing parenthesis of a FROM subquery.
- Name every computed column in the inner select list.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix