(see here for more information on this )

– set it all up:

create table A (id int primary key, descr varchar(100))
create table B (id int references A(id), seq int, value money, primary key (id,seq))
create table C (id int references A(id), seq int, value money, primary key (id,seq))

go


insert into A
select number, 'Name ' + convert(varchar(10), number)
from master..spt_values
where type='P' and number between 1 and 20


insert into B
select x1.number, x2.number, x1.number * x2.number
from master..spt_values x1
cross join master..spt_values x2
where
    x1.type = 'P' and x1.number between 1 and 20 and x1.number not in (4,7,12,18) and
    x2.type = 'P' and x2.number between 1 and 6 and x2.number % 2 = 0


insert into C
select x1.number, x2.number, x1.number * x2.number
from master..spt_values x1
cross join master..spt_values x2
where
    x1.type = 'P' and x1.number between 1 and 20 and x1.number not in (2,5,19) and
    x2.type = 'P' and x2.number between 1 and 5 and x2.number % 2 = 1

–  Now, test the efficiency of the following two SELECT statements:

– FULL OUTER JOIN:

select x.id, a.descr, x.seq, x.b, x.c
from
(
  select coalesce(b.id, c.id) as id, coalesce(b.seq,c.seq) as seq, coalesce(b.value,0) as b, coalesce(c.value,0) as c
  from b
  full outer join c on b.id = c.id and b.seq = c.seq
) x
inner join a on x.id = a.id
order by x.id, x.seq

– UNION ALL:

select x.id, max(a.descr) as descr, x.seq, sum(x.b_value) as b, sum(x.c_value) as c
from
(
    select id, seq, value as b_value, 0 as c_value
    from b
    union all
    select id, seq, 0, value
    from c
) x
inner join a on x.id = a.id
group by x.id, x.seq
order by x.id, x.seq