Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A subquery in FROM fails with: Every derived table must have its own alias

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

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

  • Applies to MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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);
  2. 2Match what you see, then jump to its fix

    1. 1The closing parenthesis of the subquery is followed by nothing, or by a clause keyword.The derived table has no aliasFix
    2. 2Error 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. 3The subquery returns expressions such as 1, 2 or SUM(price) with no name.You want the columns renamed, not just the tableFix

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.