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!

Answers


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.


Need Your Help

hadoop no data node started

hadoop hdfs

I am following this tutorial.

Error Caught in Page_Error event with no error message

asp.net iis-7 asp-classic

I'm getting hundreds of these error emails on my site a day (I get emailed if there's a 500 server error). The problem is the message isn't particularly useful, I can't seem to reproduce it or see...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.