13 February 2013

SQL Joins



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

User Id
Name
1
Adrian Williams
2
Steve Sidwell
3
James Harper
4
Jamie Cureton
5
Nicky Forster
6
Barry Hunter
7
Jimmy Quinn
Table B

User Id
Login
3
Jharper
4
Jcureton
5
nforster
6
Bhunter
7
Jquinn
8
Scoppell
9
brodgers

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