Databases Querying Relational Databases Set Operations Union Operations

Dan K
Dan K
5,429 Points

Column (Name) in Second Table is Gone?

When I combine two data sets from two tables by using UNION SELECT statement, the second table's column's name is gone. For example, please look at the code below:

SELECT MakeID, MakeName FROM Make WHERE MakeName < "D" UNION SELECT ForeignMakeID, MakeName FROM ForeignMake WHERE MakeNAme < "D" ORDER BY MakeName ASC;

Here is the result of this query:

MakeID MakeName 6 Acura 3 Audi 1 BMW 8 BMW 2 Chevy

There is a "ForeignMakeID" column in the second table (ForeignMake) but the name "ForeignMakeID" is gone when its data is integrated or combined with the first table using UNION SELECT. There is only "MakeID" column. Am I right to assume that the data is queried but its column name just disappears when you use the UNION SELECT statement? Thank you.

2 Answers

Steven Parker
Steven Parker
177,843 Points

You should only use UNION on two result sets that have the same columns. The first set determines the column names for the entire UNION, and it is assumed that the second one will return more of the same.

Other restrictions to be aware of with UNION is that the result sets must have:

  • the same number of column expressions
  • columns of the same data type
  • columns in the same order

Also, note that when you do a UNION you are stacking columns on top of each other since you are not joining side by side rather top to bottom based on your SQL statement precedence.