Dan K5,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.
Steven Parker177,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.