Combine two tables in SQL server 2008

I need to ask something is there any way combine two tables different count of columns like:

Select a,b,c, from x

 union 

Select d,开发者_Python百科 e from y

you need to do something like this

Select a,b,c from x

 union all  -- ALL doesn't filter dups and is faster

Select d, e, '' as f from y

I used '' but you might want to use NULL or 0, NULL will be compatible will all data types, '' will not be

I also used UNION ALL not UNION since it will perform better because it doesn't have to do a sort operation to get rid of dups

the alias f is not needed here either because the top query determines the name of the columns in the resultset

Note that

select a, b, c from x
union
select d, e, '' as f from y;

and

select d, e, '' as f from y
union
select a, b, c from x;

will yield different results i.e. the attribute names from the first-appearing table will be used.

Perhaps better to be unequivocal by explicitly renaming the columns in the second table e.g.

select a, b, c from x
union
select d AS a, e AS b, '' as c from y;

select col1, col2, col3, col4
from mytable1
union all
select col5, col6, null as col7, '' as col8
from mytable2

first table 
id
name
second table 
name 
seatno

if you want to join on name & there are some duplicate names in both table the use ROW_NUMBER in join query!