CODEWITHSUNDEEP
mysqlsql
S.I.
S.I.

Reputation: 3375

Select from multiple tables with UNION

I've trying to select records from 3 tables in one database with UNION but I've got error:

#1248 - Every derived table must have its own alias

This is the query that I'm trying:

SELECT * from
  (SELECT column1, 'table1' from table1
UNION
   SELECT column1, 'table2'  from table2
UNION
   SELECT column1, 'table3'  from table3) 
WHERE column1 not like 'abr%' and length(column1) < 8;

What exactly mean that error and how can I fix it and show proper results?

Upvotes: 0

Views: 3350

Answers (7)

shA.t
shA.t

Reputation: 16958

You have to add an alias for your columns in an inner select or derived table and also for itself, like this:

SELECT * 
FROM
    (SELECT column1, 'table1' as t from table1
     UNION ALL
     SELECT column1, 'table2' as t from table2
     UNION ALL
     SELECT column1, 'table3' as t from table3
    ) As dt
WHERE 
    column1 NOT LIKE 'abr%' 
  AND 
    length(column1) < 8;

Edited :
I change UNION to UNION ALL as a performance issue and there will not be any duplicates ;).

Upvotes: 3

Joy Acharya
Joy Acharya

Reputation: 680

SELECT Alis.column1 
  FROM (SELECT column1, 'table1' as which from table1
  UNION ALL
      SELECT column1, 'table2' from table2
  UNION ALL
      SELECT column1, 'table3' from table3
 ) Alis
WHERE column1 not like 'abr%' and length(column1) < 8;

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269513

As others have noted, the problem is simple -- you need a table alias after the last parentheses. You also need a column alias for the second column.

More important for performance reasons, is that you should use UNION ALL. UNION incurs the overhead of removing duplicates, and that is totally unnecessary in this case:

SELECT t.* 
FROM (SELECT column1, 'table1' as which from table1
      UNION ALL
      SELECT column1, 'table2' from table2
      UNION ALL
      SELECT column1, 'table3' from table3
     ) t
WHERE column1 not like 'abr%' and length(column1) < 8;

For performance in MySQL, I think you want to move the WHERE clause to the subqueries and create an index on column1 in all three tables:

SELECT t.* 
FROM (SELECT column1, 'table1' as which from table1 WHERE column1 not like 'abr%' and length(column1) < 8
      UNION ALL
      SELECT column1, 'table2' from table2 WHERE column1 not like 'abr%' and length(column1) < 8
      UNION ALL
      SELECT column1, 'table3' from table3 WHERE column1 not like 'abr%' and length(column1) < 8
     ) t

Upvotes: 2

Samvel Avanesov
Samvel Avanesov

Reputation: 422

You should add aliases to the tables which you use in subqueries. Here's the correct version:

USE test2;

SELECT * from
  (SELECT column1, 'table1' from table1 as t1
UNION
   SELECT column1, 'table2'  from table2 as t2
UNION
   SELECT column1, 'table3'  from table3) as t3
WHERE column1 not like 'abr%' and length(column1) < 8;

Upvotes: 1

edornd
edornd

Reputation: 461

The error is pretty self-explainatory, you just need to name the derived table you created with the nested selections and unions. It should be sufficient adding the alias to fix our error, something like this:

  SELECT * from
  (SELECT column1, 'table1' from table1
   UNION
   SELECT column1, 'table2'  from table2
   UNION
   SELECT column1, 'table3'  from table3) as youralias 
   WHERE column1 not like 'abr%' and length(column1) < 8;

Upvotes: 1

Sachu
Sachu

Reputation: 7766

you should give a name for the derived table as below

SELECT * from
  (SELECT column1, 'table1' from table1
UNION
   SELECT column1, 'table2'  from table2
UNION
   SELECT column1, 'table3'  from table3) as new_table
WHERE column1 not like 'abr%' and length(column1) < 8;

Upvotes: 1

Christian Barron
Christian Barron

Reputation: 2755

You need to give the derived table an alias like it says:

SELECT * from
  (SELECT column1, 'table1' from table1
UNION
   SELECT column1, 'table2'  from table2
UNION
   SELECT column1, 'table3'  from table3) dtAlias
WHERE column1 not like 'abr%' and length(column1) < 8;

Upvotes: 2

Related Questions