Search code examples
mysqlsqlselectsubqueryunion

Inline queries in MySQL 5.6

Could someone please show me how to use an inline query in MySQL 5.6 I am trying to do a simple thing! I am trying to write a query in which I select from a table in database and join with an inline table that I am trying to create on the fly. It's a 1 column table and I am having trouble creating it with multiple rows.

Example inline query

select * from (
      (select 'filename1' as file_name) as t1
      union
      (select 'filename2' as file_name) as t2
);

If I run this query without the second select statement, then it works. But if I run it with both select statements and try to union them it breaks down and complains that I have an error in SQL syntax near "as t2".

Any help is greatly appreciated.

Solution

  • Obviously a really over-simplified example, but... The members of a union query do not take aliases. The derived table that your are creating with union does take one, though. So:

    select * from (
      select 'filename1' as file_name
      union all
      select 'filename2'
) t;

    Note that:

    • there is no need to surround the union members with parentheses (unless you want individual order by clauses)

    • there is no need to alias the column(s) in the second member: the aliases defined in the first query prevail anyway

    • unless you do want to remove duplicates, use union all instead of union: it is more efficient, and makes the intent explicit

    Finally: as your query stands, there is no need for the outer query. This is equivalent:

    select 'filename1' as file_name
union all 
select 'filename2'

    Bonus: in very recent versions of MySQL, you can use the values row() constructor:

    select *
from (values row('filename1'), row('filename2')) t(filename)