A few co-workers asked me to explain some of the nuances of different join types (outer joins in particular). I've always reached for Venn diagrams in the past, but I was trying to think of a better way to get the information across. Whether or not I succeeded, I'll let you be the judge. I'm not going to do much in the way of explaining each of the diagrams, but I will label each diagram. I also included a diagram at the end of a common trick for inserting only rows which don't already exist in the target table (using a left outer join, and checking where the right values are null).
Please feel free to use this code and the diagrams if you think they're useful. If you do, it would be nice to credit me with them, but I'm hardly going to get mad if you don't. I'd much rather there just be good training materials out there.
The Code
if object_id('tempdb.dbo.#Cards') is not null drop table #Cards
create table #Cards
(
NumericValue tinyint not null,
Value as case NumericValue when 1 then 'Ace'
when 11 then 'Jack'
when 12 then 'Queen'
when 13 then 'King'
else cast(NumericValue as varchar(10)) end,
Suit varchar(10) not null,
Color as iif(Suit in ('Hearts', 'Diamonds'), 'Red', 'Black')
primary key clustered (NumericValue, Suit)
)
insert into #Cards (NumericValue, Suit)
select
NumericValue = (n % 13) + 1,
Suit = choose((n % 4) + 1, 'Hearts', 'Clubs', 'Diamonds', 'Spades')
from (select top 52 n = row_number() over (order by (select null))
from sys.all_objects) a
if object_id('tempdb.dbo.#LeftSet') is not null drop table #LeftSet
select c.*
into #LeftSet
from #Cards c
inner join (select 1 union all select 1 union all select 3 union all select 4) b (val)
on c.NumericValue = b.val
where c.Suit = 'Hearts'
if object_id('tempdb.dbo.#RightSet') is not null drop table #RightSet
select *
into #RightSet
from #Cards
where (NumericValue = 1 and Suit = 'Clubs')
or (NumericValue = 2 and Suit = 'Spades')
or (NumericValue in (11, 12) and Suit = 'Diamonds')
if object_id('tempdb.dbo.#MultiValueRightSet') is not null drop table #MultiValueRightSet
select *
into #MultiValueRightSet
from #Cards
where (NumericValue = 1 and Suit = 'Clubs')
or (NumericValue = 2 and Suit = 'Spades')
or (NumericValue in (3, 12) and Suit = 'Diamonds')
The Setup
The setup is pretty simple. Each card has a few properties (i.e. columns) including
Each card can be uniquely identified by its
Suit and
Value (i.e, the primary key fields).
Inner Join (Single Field)
This is probably the most common and easy to understand join. It returns a set of ordered pairs (L, R) everywhere teat L.Value is the same as r.Value.
select
LeftCard = l.Value + ' of ' + l.Suit,
RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
inner join #RightSet r
on l.Value = r.Value
Inner Join (Multiple Fields)
This just illustrates a join when multiple conditions are at play. You can see at least one condition matches for quite a few of the query below, but only one matches both.
select
LeftCard = l.Value + ' of ' + l.Suit,
RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
inner join #MultiValueRightSet r
on l.Value = r.Value
and l.Color = r.Color
Left Outer Join
Outer join in general can be though of in a couple ways. One way is that it alwasy return everything from the left table, whether it matches or not. If it doesn,t match, the right value is null. Alternatively, you can think of a left outer join as an inner join UNIONed with the unpaired values from the left table.
select
LeftCard = l.Value + ' of ' + l.Suit,
RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
left outer join #RightSet r
on l.Value = r.Value
Right Outer Join
Right Outer Join is the same as a left outer join, just in reverse, It's all the paired values plus all the unpaired values from the right table.
select
LeftCard = l.Value + ' of ' + l.Suit,
RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
right outer join #RightSet r
on l.Value = r.Value
Full Outer Join
If a left outer join is an inner join unioned with unpaired values from the left table and a right outer join is an inner join unioned with unpaired values from the right table, a Full Outer Join is a combination of both. i.e.
- An inner join
- unpaired values from the left table (left outer join)
- unpaired values from the right table (right outer join)
select
LeftCard = l.Value + ' of ' + l.Suit,
RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
full outer join #RightSet r
on l.Value = r.Value
Cross Join
Cross Join, also sometimes referred to as a Cartesian Product takes no ON clause. Technically you can write one using a where clause (and in fact, back in the day, that's how joins were actually done), but in general that's not the best way to use a cross join.
A cross join pairs every element from one set with every element from another set. In the example below, we have a set with 4 elements cross joined with another set of 4 elements. You can see graphically below how that shakes out. Interestingly to note though, if you cross join a set with itself, the number of elements you'll get out are n^2 where n is the number of elements in the set.
select
LeftCard = l.Value + ' of ' + l.Suit,
RightCard = r.Value + ' of ' + r.Suit
from #LeftSet l
cross join #RightSet r
Comments
Post a Comment