492

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?

dreftymac
  • 31,404
  • 26
  • 119
  • 182
silverkid
  • 9,291
  • 22
  • 66
  • 92
  • 20
    Couldn't you just simplify this as "select ID from TT2"? – DMKing Dec 11 '09 at 15:29
  • 8
    I got this error recently because I had an extra `)` in a query with a lot of `UNION ALL`s. – mpen Feb 17 '12 at 00:52
  • 12
    Seeing as how this is the #1 Google search... The accepted answer doesn't really answer the error 'Every derived table must have its own alias'. Look below for more info. – Daniel B. Chapman Aug 30 '12 at 16:01
  • 1
    There's an user providing a question with a proper minimal reproducable example and ppl. start complaining about b/c it could be rewritten. D: – NotX Jan 13 '23 at 14:01

4 Answers4

674

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
Paul
  • 16,285
  • 13
  • 41
  • 52
  • 33
    Correct answer for the shown sampe code but not the solution for most users looking this question up. – ToBe Sep 04 '14 at 07:48
  • 3
    @ToBe I'm curious what you meant by that? The answer holds true in any query, that if you have a derived table in your from clause you need to give it an alias. – AdamMc331 May 04 '15 at 13:11
  • 2
    Sorry, I didnt see you also fixed the original query and added the `AS` statements. I thought you only showed the shorthand. removed my downvote. – ToBe May 22 '15 at 15:46
  • I am thinking same with @ToBe. The answer is this: "Here, derived table means 'sub-query used in the FROM clause'. In the questioners's case; they are the sub-queries inside the parenthesis. If you do not indicate the alias by using keyword 'as' for those queries, the dbms query engine cannot determine which query is what without their names (or aliases) so, you must give unique names (aliases) for all of your sub-queries to make dbms query engine make it's work properly." – Bahadir Tasdemir Apr 25 '16 at 07:33
  • 2
    It would be better to clarify subquery is not necessarily a derived table: it has to be directly within a FROM clause. Statements like `SELECT...FROM...WHERE x NOT IN (subquery) AS T` will trigger an error – Nicholas Mar 16 '19 at 18:06
90

I think it's asking you to do this: 

SELECT ID
FROM (SELECT ID,
             msisdn 
      FROM (SELECT * FROM TT2) as myalias
     ) as anotheralias;

But why would you write this query in the first place?

Amarnath Balasubramanian
  • 9,300
  • 8
  • 34
  • 62
hometoast
  • 11,522
  • 5
  • 41
  • 58
  • 25
    the actual query is too long.. i have shortened it enough that people here have less time understanding it. the error on the short and long query was same. – silverkid Dec 11 '09 at 15:32
  • I understand now. I was also thinking it might have been generated by some code. It should still simplify as Paul and DMKing suggested. – hometoast Dec 11 '09 at 15:36
  • 17
    Wow, is this really the unaccepted second answer? To anyone with the problem this is the answer, MySQL requires you to label the "sub query" instead of just leaving it like many other implementations. – Daniel B. Chapman Aug 30 '12 at 16:00
23

Here's a different example that can't be rewritten without aliases ( can't GROUP BY DISTINCT).

Imagine a table called purchases that records purchases made by customers at stores, i.e. it's a many to many table and the software needs to know which customers have made purchases at more than one store:

SELECT DISTINCT customer_id, SUM(1)
  FROM ( SELECT DISTINCT customer_id, store_id FROM purchases)
  GROUP BY customer_id HAVING 1 < SUM(1);

..will break with the error Every derived table must have its own alias. To fix:

SELECT DISTINCT customer_id, SUM(1)
  FROM ( SELECT DISTINCT customer_id, store_id FROM purchases) AS custom
  GROUP BY customer_id HAVING 1 < SUM(1);

( Note the AS custom alias).

Neil Stockbridge
  • 586
  • 5
  • 6
12

I arrived here because I thought I should check in SO if there are adequate answers, after a syntax error that gave me this error, or if I could possibly post an answer myself.

OK, the answers here explain what this error is, so not much more to say, but nevertheless I will give my 2 cents, using my own words:

This error is caused by the fact that you basically generate a new table with your subquery for the FROM command.

That's what a derived table is, and as such, it needs to have an alias (actually a name reference to it).

Given the following hypothetical query:

SELECT id, key1
FROM (
    SELECT t1.ID id, t2.key1 key1, t2.key2 key2, t2.key3 key3
    FROM table1 t1 
    LEFT JOIN table2 t2 ON t1.id = t2.id
    WHERE t2.key3 = 'some-value'
) AS tt

At the end, the whole subquery inside the FROM command will produce the table that is aliased as tt and it will have the following columns id, key1, key2, key3.

Then, with the initial SELECT, we finally select the id and key1 from that generated table (tt).

Sbpro
  • 948
  • 14
  • 27