Jeff Smith Blog

Here is a comparison of a FULL OUTER JOIN and a CROSS JOIN to achieve the same results.   See here for more information on this.


– Prepare environment:

create table A (id int primary key, descr varchar(100))

create table seq (seq int primary key)

create table B (id int references A(id), seq int references seq(seq), value money, primary key (id,seq))

create table C (id int references A(id), seq int references seq(seq), value money, primary key (id,seq))

go


– Create sample data:

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 seq
select number from master..spt_values where type='P' and number between 1 and 6

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 and compare the two techniques shown:

– 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


– CROSS JOIN:

select a.id, a.descr, seq.seq, b.value, c.value
from a
cross join seq
left outer join b on a.id = b.id and seq.seq = b.seq
left outer join c on a.id = c.id and seq.seq = c.seq
where b.id is not null or c.id is not null
order by a.id, seq.seq
 

– Clean it up:

drop table B
drop table C
drop table seq
drop table a