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?
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
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?
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
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).
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 words:
This error is caused by the fact that you basically generate a new table with your subquery for the
That’s what a
derived table is, and as such, it needs to have an
alias (actually a name reference to it).
So 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
So, 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
So, then with the initial
SELECT from that table we finally select the
key1 from the