T-SQL union with heterogeneous columns

Question

I have two tables with the following data:

[Animals].[Males] DataID HerdNumber HerdID NaabCode e46fff54-a784-46ed-9a7f-4c81e649e6a0 4 'GOLDA' '7JE1067' fee3e66b-7248-44dd-8670-791a6daa5d49 1 '35' NULL [Animals].[Females] DataID HerdNumber HerdID BangsNumber 987110c6-c938-43a7-a5db-194ce2162a20 1 '9' 'NB3829483909488' 1fc83693-9b8a-4054-9d79-fbd66ee99091 2 'NATTIE' 'ID2314843985499' 

I want to combine these tables into a view that looks like this:

 DataID HerdNumber HerdID NaabCode BangsNumber e46fff54-a784-46ed-9a7f-4c81e649e6a0 4 'GOLDA' '7JE1067' NULL fee3e66b-7248-44dd-8670-791a6daa5d49 1 '35' NULL NULL 987110c6-c938-43a7-a5db-194ce2162a20 1 '9' NULL 'NB3829483909488' 1fc83693-9b8a-4054-9d79-fbd66ee99091 2 'NATTIE' NULL 'ID2314843985499'` 

When I used the UNION keyword, SQL Server created a view that combined NaabCode and BangsNumber into one column. The book I have in regular SQL suggested the syntax of UNION CORRESPONDING , for example:

 SELECT * FROM [Animals].[Males] UNION CORRESPONDING (DataID, HerdNumber, HerdID) SELECT * FROM [Animals].[Females]` 

But when I type this SQL Server, "The syntax is incorrect next to" CORRESPONDING. "

Can someone tell me how to achieve the desired result and / or how to use UNION CORRESPONDING in T-SQL?

Answer 1

You can simply do:

 SELECT DataID, HerdNumber, HerdID, NaabCode, NULL as BangsNumber FROM [Animals].[Males] UNION ALL SELECT DataID, HerdNumber, HerdID, NULL as NaabCode, BangsNumber FROM [Animals].[Females] 

SQL Fiddle

I don’t remember that SQL Server supports the corresponding syntax, but I could be wrong.
In any case, this query will select null for the BangsNumber column for males and for the NaabCode column for females if everything else is selected correctly.

Answer 2

Just make union explicitly enumerate the columns:

 select DataID, HerdNumber, HerdID, NaabCode, NULL as BangsNumber from Animals.Males union all select DataID, HerdNumber, HerdID, NULL, BangsNumber from Animals.Females; 

Note: you must use union all instead of union (provided that no animal is male or female). union incurs performance overhead to remove duplicates.

Answer 3

 SELECT DataID, HerdNumber, HerdID, NaabCode, '' asBangsNumber FROM [Animals].[Males] UNION ALL SELECT DataID, HerdNumber, HerdID, '' as NaabCode, BangsNumber FROM [Animals].[Females] 

Answer 4

You need to specify columns in each selection.

 SELECT DataID, HerdNumber, HerdID FROM [Animals].[Males] UNION SELECT DataID, HerdNumber, HerdID FROM [Animals].[Females]