When querying multiple tables using UNION ALL the AS keyword only works for the first table

Question

I have a query:

 (SELECT col1 AS table1 FROM table1 WHERE col3 IS NOT NULL)
    UNION ALL
    (SELECT col1 AS table2 FROM table2 WHERE  col3 IS NOT NULL)
    UNION ALL
    (SELECT col1 AS table3 FROM table3 WHERE col3 IS NOT NULL)

However when I process this using PDO and the fetchAll(PDO::FETCH_ASSOC); command, the keys to the array generated all come out as table1 irrespective of the table they are actually from.

Is my syntax incorrect? Thanks!

Answer 1

Your query returns a single column. A single column can only have one name/alias. In a UNION query, the first subquery defines the resulting set's column names.

If you want to specify which table each value has come from, add another column, e.g. like this:

(SELECT col1, 'table1' AS src FROM table1 WHERE col3 IS NOT NULL)
UNION ALL
(SELECT col1, 'table2'        FROM table2 WHERE col3 IS NOT NULL)
UNION ALL
(SELECT col1, 'table3'        FROM table3 WHERE col3 IS NOT NULL)

Answer 2

That's the SQL specification: The column names of the result set are taken from the first select.
It's just the way it is.

When you think about it, having different column names in subsequent selects makes no sense, because a column name can't change part way through a result set - column names are defined (once) for the (entire) result set.