derived table must have its own alias

Question

I'm struck in mysql alias this time. I don't know exactly what does this mean and how to solve it. The query is:

select * from (SELECT distinct * FROM $tableName1 
                where not exists (select NULL from $tableName2 
                                  where $tableName1.cid=$tableName2.cid)
               ) 
where location='xyz';

possible duplicate of [every derived table must have its own alias](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias) — James Z, Jul 04 '15 at 10:23
instead of solving it, you'r just marking it as duplicate. Excuse me !! If I'm posting a question, it simply means, the existing answers didn't worked my friend !! — Rajan, Jul 06 '15 at 05:58
Well at least to me it looks clearly like the derived table is missing alias, something like "... ) **as myalias** where location..." -- which the other question answers quite clearly — James Z, Jul 06 '15 at 13:03

spencer7593 · Answer 1 · 2015-07-04T14:49:56.863

Your query is essentially of the form:

  select * 
    from sometable 
   where location='xyz';

In your query, in place of a tablename (like sometable), you've used a query as a rowsource. The query you're selecting from is called an inline view; MySQL refers to it as a derived table.

The error message is saying that you need to assign a name to that derived table, in your query. You do that by following the closing paren with an identifier, called an alias. For example, we can assign a short alias v to the derived table, like this:

  select * 
    from (SELECT ... ) v
   where location='xyz'

FOLLOWUP

That alias that's assigned to the derived table is effectively the name (identifier) of the table elsewhere in the query. Just as we can assign an alias to a table, and use that alias to qualify column references:

  SELECT t.id
       , t.somecol 
    FROM sometable t
   WHERE t.location = 'xyz'

we can use the alias on the derived table to qualify references:

  SELECT v.*
    FROM (SELECT ... ) v
   WHERE v.location = 'xyz'

On an entirely different note... in terms of performance: the predicate(s) in the outermost query do not get pushed into the inline view. We can usually get better performance by copying or moving that predicate into the inline view query, for example:

  SELECT v.*
    FROM (SELECT ...
            FROM sometable t
           WHERE t.location = 'xyz' 
         ) v

derived table must have its own alias

1 Answers1