Sponsored Links

SQL Tutorial - An Overview of SQL Joins

This article will expand on that knowledge and introduce some of the more intricate commands that can be used.

The following commands will be introduced, clicking an item below will skip directly to that section:

All of the examples in this artilce will use data from the following tables:

Employees

FirstName Surname DepartmentCode Age Salary
Andrew Jones 322 28 49816
John Sullivan 704 44 102000
Keith Angler 452 34 72540
Sarah Growler 452 41 81235
Elizabeth Armitage 452 22 29400

Employees_Archive

FirstName Surname DepartmentCode Age Salary
Andrew Jones 322 28 49816
John Sullivan 452 44 102000

Department

DepartmentCode Description DepartmentHead
322 Marketing Anthony Quinn
452 Development Beth Grogan
212 Finance Ian McTavish

INNER JOIN

The SQL INNER JOIN is probably the most common join and returns only the results which have a common record in both of the requested tables. This can be shown in a diagram as:

SQL Inner Join

The full syntax for a INNER JOIN is:

SELECT
   FirstName, Surname, Age, Description
FROM
   Employees
INNER JOIN
   Department
ON
   Employees.DepartmentCode = Department.DepartmentCode

This could be written in a slightly shortened form as:

SELECT
   FirstName, Surname, Age, Description, DepartmentHead
FROM
   Employees, Department
ON
   Employees.DepartmentCode = Department.DepartmentCode

The returned results would be:

FirstName     Surname      Age     Description     DepartmentHead
---------     -------      ---     -----------     --------------
Keith         Angler       34      Development     Beth Grogan
Elizabeth     Armitage     22      Development     Beth Grogan
Sarah         Growler      41      Development     Beth Grogan
Andrew        Jones        28      Marketing       Anthony Quinn

NB: The John Sullivan record would be omitted as there is no department matching his DepartmentCode value.

FULL OUTER JOIN

A FULL OUTER JOIN returns all of the records from both tables regardless as to whether there is a match in the other table.  If there is a match in the corresponding table then the value is displayed other wise the 'null' value is shown. The FULL OUTER JOIN can be shown in a diagram as:

SQL Inner Join

The SQL for the FULL OUTER JOIN would be as follows:

SELECT
   FirstName, Surname, Age, Description, DepartmentHead
FROM
   Employees
FULL OUTER JOIN
   Department
ON
   Employees.DepartmentCode = Department.DepartmentCode

The following results are returned:

FirstName     Surname      Age      Description     DepartmentHead
---------     -------      ---      -----------     --------------
Keith         Angler       34       Development     Beth Grogan
Elizabeth     Armitage     22       Development     Beth Grogan
Sarah         Growler      41       Development     Beth Grogan
Andrew        Jones        28       Marketing       Anthony Quinn
John          Sullivan     28       null            null
null          null         null     Finance         Ian McTavish

LEFT JOIN / LEFT OUTER JOIN

A LEFT OUTER JOIN or just LEFT JOIN returns all of the records from first table specified regardless as to whether there is a match in the other table. If there is a match in the corresponding table then the value is displayed other wise the 'null' value is shown. The LEFT JOIN for the Employees/Department example can be shown in a diagram as:

SQL Inner Join

The SQL for the LEFT JOIN would be as follows:

SELECT
   FirstName, Surname, Age, Description, DepartmentHead
FROM
   Employees
LEFT OUTER JOIN
   Department
ON
   Employees.DepartmentCode = Department.DepartmentCode

Returns the following results:

FirstName     Surname      Age      Description     DepartmentHead
---------     -------      ---      -----------     --------------
Keith         Angler       34       Development     Beth Grogan
Elizabeth     Armitage     22       Development     Beth Grogan
Sarah         Growler      41       Development     Beth Grogan
Andrew        Jones        28       Marketing       Anthony Quinn
John          Sullivan     28       null            null

RIGHT JOIN / RIGHT OUTER JOIN

A RIGHT OUTER JOIN or RIGHT JOIN works in exactly the same way as the LEFT JOIN except it is the records from second table specified which are returned. If there is a match in the first table then the value is displayed other wise the 'null' value is shown. The RIGHT JOIN for the Employees/Department example can be shown in a diagram as:

SQL Inner Join

The SQL for the RIGHT JOIN would be:

SELECT
   FirstName, Surname, Age, Description, DepartmentHead
FROM
   Employees
RIGHT OUTER JOIN
   Department
ON
   Employees.DepartmentCode = Department.DepartmentCode

The resultset produced would be:

FirstName     Surname      Age      Description     DepartmentHead
---------     -------      ---      -----------     --------------
Keith         Angler       34       Development     Beth Grogan
Elizabeth     Armitage     22       Development     Beth Grogan
Sarah         Growler      41       Development     Beth Grogan
Andrew        Jones        28       Marketing       Anthony Quinn
null          null         null     Finance         Ian McTavish

Back To SQL

Options