Syntax error all expressions in a derived table must have an explicit name

In this article, we will be discussing the below points

• HOW TO FIX THE ERROR in MySQL : Every derived table must have its own alias
• Every derived table must have its own alias : JOIN

Let’s get started, but first, we will look into the data we will be using in our examples. Assume that we have a table sale_details with the below rows.

HOW TO FIX THE ERROR in MySQL : Every derived table must have its own alias

What is a derived table?  Well, the Derived table is an expression that creates a temporary table in the FROM clause’s scope in the query. It is a subquery in the SELECT statement and FROM clause. Let’s look into an example to get the average of the maximum no_products_sold for each salesperson for a department. Observe the below query.

 SELECT 
    CEIL(AVG(max_sales))
FROM
    (SELECT 
        sale_person_name, MAX(no_products_sold) AS max_sales
    FROM
        sale_details
    GROUP BY sale_person_name);

In the above query, we created a derived table from subquery “SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name,” which is within the scope of the FROM clause.

On running the query written above to get the average of the maximum no_products_sold for each salesperson per department, we get an ERROR :

Advertisements

Action Output Message: SELECT CEIL(AVG(max_sales)) FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name) LIMIT 0, 1000 Error Code: 1248. Every derived table must have its own alias 0.00025 sec

It is highlighting that an Error:1248 Every derived table must have its alias has occurred. The reason for the same is that we need to provide an alias for the sub-queries while working in MySQL (alias is just a temporary/false name). Observe the corrected query below:

 SELECT 
    CEIL(AVG(max_sales)) AS average_of_max_sales_per_salesman
FROM
    (SELECT 
        sale_person_name, MAX(no_products_sold) AS max_sales
    FROM
        sale_details
    GROUP BY sale_person_name) AS sales_alias;   

In the above query, we have added an alias sales_alias for the derived table. On running the query now, we get the desired result.

Action Output Message: SELECT CEIL(AVG(max_sales)) FROM (SELECT sale_person_name, MAX(no_products_sold) AS max_sales FROM sale_details GROUP BY sale_person_name)as sales_alias LIMIT 0, 1000 1 row(s) returned 0.00086 sec / 0.000018 sec.

Output :-

Let’s see how to work with joins while using the derived tables. We will be using the same table sale_details to get all the columns for a salesperson corresponding to the row, which shows the maximum sale for a product department wise. We need to do an inner join of the sales_details table with a derived table for the desired result. Observe the below query, it’s output, and explanation.

SELECT 
    sd1.*
FROM
    sale_details sd1
        INNER JOIN
    (SELECT 
        sale_person_name, MAX(no_products_sold) AS MaxSale
    FROM
        sale_details
    GROUP BY sale_person_name) sd2 ON sd1.sale_person_name = sd2.sale_person_name
        AND sd1.no_products_sold = sd2.MaxSale;

Action Output Message: SELECT sd1.* FROM sale_details sd1 INNER JOIN (SELECT sale_person_name, MAX(no_products_sold) AS MaxSale FROM sale_details GROUP BY sale_person_name) sd2 ON sd1.sale_person_name = sd2.sale_person_name AND sd1.no_products_sold = sd2.MaxSale LIMIT 0, 1000 4 row(s) returned 0.00097 sec / 0.000032 sec.

Output:-

Explanation:- In this query, we are using an INNER JOIN with the sales_details table and the derived table.

STEP1:  The derived table created in the sub-query “SELECT sale_person_name, MAX(no_products_sold) AS MaxSale FROM sale_details GROUP BY sale_person_name” gets the sales_person_name and maximum no_products_sold grouped by sales_person_name. The alias name given to this derived table is sd2.

STEP2: Select all the details from the sales_details table in the outer-query alias name is sd1.

STEP3: Finally, doing an INNER JOIN on the derived table and sales_details table ON sale_person_name AND ON no_products_sold from sales_details table, MAX(no_products_sold) from the derived table.

We hope this article provided a good understanding of the alias while using the derived tables. Good Luck !!!

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.

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.