“Every derived table must have its own alias” Error in MySQL

 

Jun 29, 2020 | 1 comment

Are you writing a query in MySQL and getting an error of “1248: Every derived table must have its own alias”? If so, it’s a simple error to fix.

 

How To Fix The “Every derived table must have its own alias” Error in MySQL

So you’ve got this error in MySQL. How do you fix it?

The short answer is you need to give your subqueries an alias in your SELECT statement. Add an alias after the closing bracket of the FROM clause subquery.

In other SQL vendors, this is not required, but MySQL requires you to alias your subqueries.

What does this mean?

Let’s take a look at an example.

 

Example of a Derived Table

A derived table is a SELECT subquery within the FROM clause of your main query. It’s treated like a table because it has columns and returns rows. It can be used in place of a table in the FROM clause, for example. It’s often called an inline view or just a subquery.

Here’s an example of a derived table:

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
  SELECT o.order_id,
  o.customer_city,
  o.order_amount
  FROM orders o
  INNER JOIN customer c ON o.customer_id = c.customer_id
)
GROUP BY customer_city;

This query finds the city, number of orders, and the sum of the order amounts from the orders and customer tables.

Let’s break this query down.

The query has an outer query and an inner query. The inner query is used to get data from orders and customers:

SELECT o.order_id, o.customer_city, o.order_amount
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_id

This gets data from two tables, joining on a common field. This is the “derived table”.

The outer query selects a few columns from this subquery. Rather than using a table, the data comes from the results of this subquery.

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
...
)
GROUP BY customer_city;

It shows the customer_city, two aggregate functions, and groups by the city.

This is the entire query again:

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
  SELECT o.order_id,
  o.customer_city,
  o.order_amount
  FROM orders o
  INNER JOIN customer c ON o.customer_id = c.customer_id
)
GROUP BY customer_city;

What happens if you run this query in MySQL?

You’ll get this error:

Error 1248: Every derived table must have its own alias

How do you resolve this?

 

Solution to “Every derived table must have its own alias”

The reason you get this error is that in MySQL, every derived table (subquery that is a SELECT query) needs to have an alias after it.

The query example here did not have an alias for the subquery. The alias for a subquery is the same as a column alias. It goes after the closing brackets for the FROM clause for the subquery.

So, in this example, all we need to do to resolve this error is to add an alias after the closing bracket.

The alias can be almost anything you like: a single letter, a word, a few letters. I often use the word “sub” if it’s a small query (for subquery), or something more descriptive if it’s a longer query.

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
  SELECT o.order_id,
  o.customer_city,
  o.order_amount
  FROM orders o
  INNER JOIN customer c ON o.customer_id = c.customer_id
) sub
GROUP BY customer_city;

Notice how the word “sub” is added after the closing bracket on the second last line? That’s the alias for the subquery or derived table. This alias is required in MySQL but not other vendors.

Running this query should work and you should not get the “Every derived table must have its own alias” error anymore.

 

Optional: Add the AS Keyword

You could add the AS keyword, as this is an optional part of adding an alias, but it’s up to you. This will also work:

SELECT customer_city,
COUNT(*) AS order_count,
SUM(order_amount) AS order_sum
FROM (
  SELECT o.order_id,
  o.customer_city,
  o.order_amount
  FROM orders o
  INNER JOIN customer c ON o.customer_id = c.customer_id
) AS sub
GROUP BY customer_city;

So, that’s how you can resolve this derived table alias error. Add an alias to your subquery.