Column names for a table formed by a UNION
Given a couple of simple tables like so:
create table R(foo text); create table S(bar text);
If I were to union them together in a query, what do I call the column?
select T.???? from ( select foo from R union select bar from S) as T;
Now, in mysql, I can apparently refer to the column of T as 'foo' -- the name of the matching column for the first relation in the union. In sqlite3, however, that doesn't seem to work. Is there a way to do it that's standard across all SQL implementations?
If not, how about just for sqlite3?
Correction: sqlite3 does allow you to refer to T's column as 'foo' after all! Oops!
Although there is no spelled rule, we can use the column names from the first subquery in the union query to fetch the union results.
Try to give an alias to columns;
select T.Col1 from ( select foo as Col1 from R union select bar as Col1 from S) as T;
or If the name of column is not necessary then T.* will be enough.