SQL Joins
I'm always at a loss when asked to explain the differences between
the different types of (SQL) joins. I know what I need to use but in my muddled
mind can't ever explain why. This post should help to explain the differences
for those that don't know and help remind those that do.
Joins are either inner or outer, outer can be full outer, left
outer, or right outer. Let’s start with a couple of tables to help visualise
what each join will do.
|
Table
A
|
Table
B
|
Assume
that all queries are asked to return user id (from Table A), name, and login.
Each
of the joins will return the following
Left Outer Join
The
left outer join will return all records from Table A and values from Table B
where they exist, otherwise a null is returned.
TSQL:
select
tablea.userid as userid,
name,
login
from
TableA
left outer join
TableB on
TableA.userid =
TableB.userid
Results:
|
User Id
|
Name
|
Login
|
|
1
|
Adrian Williams
|
NULL
|
|
2
|
Steve Sidwell
|
NULL
|
|
3
|
James Harper
|
jharper
|
|
4
|
Jamie Cureton
|
jcureton
|
|
5
|
Nicky Forster
|
nforster
|
|
6
|
Barry Hunter
|
bhunter
|
|
7
|
Jimmy Quinn
|
jquinn
|
Right Outer Join
The
right outer join will return all records from Table B and values from Table A
where they exist, otherwise a null is returned.
TSQL:
select
tablea.userid as userid,
name,
login
from
TableA
right outer join
TableB on
TableA.userid =
TableB.userid
Results:
|
User Id
|
Name
|
Login
|
|
3
|
James Harper
|
jharper
|
|
4
|
Jamie Cureton
|
jcureton
|
|
5
|
Nicky Forster
|
nforster
|
|
6
|
Barry Hunter
|
bhunter
|
|
7
|
Jimmy Quinn
|
jquinn
|
|
NULL
|
NULL
|
scoppell
|
|
NULL
|
NULL
|
brodgers
|
Full Outer Join
The
full outer join will return all records from Table A and Table B, where there
isn’t a corresponding record in null will be returned.
TSQL:
select
tablea.userid as userid,
name,
login
from
TableA
Full outer join
TableB on
TableA.userid =
TableB.userid
Results:
|
User Id
|
Name
|
Login
|
|
1
|
Adrian Williams
|
NULL
|
|
2
|
Steve Sidwell
|
NULL
|
|
3
|
James Harper
|
jharper
|
|
4
|
Jamie Cureton
|
jcureton
|
|
5
|
Nicky Forster
|
nforster
|
|
6
|
Barry Hunter
|
bhunter
|
|
7
|
Jimmy Quinn
|
jquinn
|
|
NULL
|
NULL
|
scoppell
|
|
NULL
|
NULL
|
brodgers
|
Inner Join
The
inner join will only return data from rows where there is a match in both Table
A and Table B.
TSQL:
select
tablea.userid as userid,
name,
login
from
TableA
inner join
TableB on TableA.userid = TableB.userid
Results:
|
User Id
|
Name
|
Login
|
|
3
|
James Harper
|
jharper
|
|
4
|
Jamie Cureton
|
jcureton
|
|
5
|
Nicky Forster
|
nforster
|
|
6
|
Barry Hunter
|
bhunter
|
|
7
|
Jimmy Quinn
|
jquinn
|
