T-SQL Union with Dissimilar 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 merge 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 produced a view that merged the NaabCode and BangsNumber into one column. A book that I have on regular SQL suggested UNION CORRESPONDING syntax like so:

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

But when I type this SQL Server says "Incorrect syntax near 'CORRESPONDING'."

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

Answer 1

You can just 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 might be wrong.
Anyway, this query will select null for the BangsNumber column for the males, and for the NaabCode column for the females, while selecting everything else correctly.

Answer 2

Just do the union explicitly listing 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 should use union all instead of union (assuming that no single animal is both male and female). union incurs a performance overhead to remove duplicates.