Does combining two queries with UNION ALL require an alias?

Question

I have two tables with identical structure, identical userid values but otherwise different data

I'm trying to do a query that will give me a combined list of results from both tables, on the basis of the userid

Table 1:

userid	time	location
18	1342	New York

Table 2:

userid	time	location
18	1800	New Jersey

And so I previously had two separate queries and would run my PHP based on the results of each separate query

SELECT * FROM table1 WHERE userid = 18
SELECT * FROM table2 WHERE userid = 18

But now, I would like to simply combine them all into one result set, since the structure between table1 and table2 are identical

So I tried the following query

SELECT * FROM (
                (SELECT * FROM table1 WHERE userid = 18)
                                 UNION ALL
                (SELECT * FROM table2 WHERE userid = 18)
              )

this results in the following error:

Every derived table must have its own alias

I guess I'm confused on why this would need an alias, since I just want the combined result.

I changed my query to:

SELECT * FROM (
                (SELECT * FROM table1 as P WHERE userid = 18)
                                 UNION ALL
                (SELECT * FROM table2 as Q WHERE userid = 18)
              ) as R

And this does seem to work, but I'm confused as to why and if this is ultimately correct or if i'm something losing or altering my data by doing that

It's correct. There is some unnecessary detail, like the `AS P` and `AS Q`, plus the `( )`s around the tables in the `UNION`. The `R` correlation name is required by the standard, but some databases may not require it. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a3a8911381d07b220b1051f40c05e3e4 I suppose this should be an anwer. — Jon Armstrong, May 28 '22 at 20:25

score 0 · Answer 1 · answered May 28 '22 at 20:50

Yes, every derived table, i.e. subquery in the FROM clause, must have an alias. Assigning an alias for a derived table does not change anything in the source table. It's only for the current query.

As a side note, your example could be written more simply:

SELECT * FROM table1 WHERE userid = 18
UNION ALL
SELECT * FROM table2 WHERE userid = 18;

In this form, the derived table and the individual table aliases are not necessary for this example, but perhaps the example in your question is simplified from your real case.

Does combining two queries with UNION ALL require an alias?

1 Answers1