Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Full Outer Join versus Cross Join

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

Print | posted on Friday, April 20, 2007 9:58 AM |

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET