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 Union All

(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

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

Feedback

Gravatar

# re: Full Outer Join versus Union All

Not a good example :(
10/18/2007 5:26 AM | abhi
Gravatar

# re: Full Outer Join versus Union All

Thanks for the valuable feedback, abhi !
10/18/2007 10:47 AM | Jeff
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET