SQL - 联合两个表，每个表都有几个唯一的列

I'd recommend that you build up some SQL by querying your schema... this way you don't miss anything by writing things by hand. You can generate the script like so (just replace @tableName1 and @tableName2 values with the appropriate table names):

declare
 @tableName1 sysname = 'myfirsttablename'
,@tableName2 sysname = 'mysecondtablename'
,@select varchar(max) = 'select';

declare @columns table
(
     Id int identity(1,1)
    ,ColumName nvarchar(128)
    ,ExistsInTable1 bit
    ,ExistsInTable2 bit
);

-- Get a column listing with flags for their existence in each table
insert @columns
select distinct
 quotename(c.Column_Name)
,iif(c2.Table_Name is null, 0, 1)
,iif(c3.Table_Name is null, 0, 1)
from Information_Schema.Columns as c
    left join Information_Schema.Columns as c2
    on c2.Column_Name = c.Column_Name
    and c2.Table_Name = @tableName1
    left join Information_Schema.Columns as c3
    on c3.Column_Name = c.Column_Name
    and c3.Table_Name = @tableName2 
where c.Table_Name in (@tableName1, @tableName2);

-- Build the select statement for the 1sttable (using null where the column is absent)
select
 @select += char(10) + iif(c.Id = 1, ' ', ',') 
+ iif(c.ExistsInTable1 = 1, c.ColumName, 'null') + ' as ' + c.ColumName
from @columns as c
order by c.Id;

set @select += '
from ' + quotename(@tableName1) + '
union all
select';

-- Build the select statement for the 2ndtable (using null where the column is absent)
select
 @select += char(10) + iif(c.Id = 1, ' ', ',') 
+ iif(c.ExistsInTable2 = 1, c.ColumName, 'null') + ' as ' + c.ColumName
from @columns as c
order by c.Id;

set @select += '
from ' + quotename(@tableName2);

-- Print or execute your sql.
print(@select); -- or exec(@select);

生成 SQL 后，我建议您: