What is the error "Every derived table must have its own alias" in MySQL?

mysql subquery mysql-dependent-subquery mysql-error-1248
silverkid picture silverkid · Dec 11, 2009 · Viewed 456.3k times · Source

I am running this query on MySQL

SELECT ID FROM (
    SELECT ID, msisdn
    FROM (
        SELECT * FROM TT2
    )
);

and it is giving this error:

Every derived table must have its own alias.

What's causing this error?

Answer

Paul picture Paul · Dec 11, 2009

Every derived table (AKA sub-query) must indeed have an alias. I.e. each query in brackets must be given an alias (AS whatever), which can the be used to refer to it in the rest of the outer query.

SELECT ID FROM (
    SELECT ID, msisdn FROM (
        SELECT * FROM TT2
    ) AS T
) AS T

In your case, of course, the entire query could be replaced with:

SELECT ID FROM TT2

Related questions

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More
Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
MySQL - SELECT WHERE field IN (subquery) - Extremely slow why?

I've got a couple of duplicates in a database that I want to inspect, so what I did to see which are duplicates, I did this: SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1 This …

Read More