SQL Book

The SQL Outer Join - return all specified rows from one of the two tables in the join

In the SQL Inner Join we saw how a JOIN can be used to define a relationship between the columns of two different tables. We also saw that the INNER JOIN only returned rows where there was a match found in the specified join definition.

The SQL OUTER JOIN differs from the inner join in a useful way, it enables us to join data from two tables and return all specified rows from one of the two tables regardless of whether a related record is found in the other table.

Some example data

Let us use the example data that we used in our INNER JOIN article where we had an Employee table and a TrainingTaken table which were related on Employee.EmployeeID = TrainingTaken.EmployeeID.

Some example data is shown below:

Employee

EmployeeID Name Telephone StartedEmployment
1 Bob Marley 0222 00000 10/01/2005
2 John Lennon 0222 00050 02/05/2003
3 Ralph Kimball 0222 03307 01/04/2004
4 Bill Gates 0222 03307 01/04/2004

TrainingTaken

TrainingTakenID EmployeeID TrainingTitle TrainingDate
1 2 Sales Training 10/01/07
2 1 Risk Management 05/02/07
3 2 First Aid 01/03/07
4 3 Sales Training 10/01/07

We can see from the above data that Bill Gates did not take any training as there is no record in the TrainingTaken table which has an EmployeeID of 4. What happens if we want to create a report of EmployeeName, TrainingTitle and TrainingDate to show a list of ALL employees and any training they may have taken. Let us use the OUTER JOIN to achieve this:

SELECT e.Name, tt.TrainingTitle, tt.TrainingDate
FROM Employee e LEFT OUTER JOIN TrainingTaken tt
	ON e.EmployeeID = tt.EmployeeID

Returns:

Name TrainingTaken TrainingDate
Bob Marley Risk Management 05/02/07
John Lennon Sales Training 10/01/07
John Lennon First Aid 01/03/07
Ralph Kimball Sales Training 10/01/07
Bill Gates NULL NULL

Which of the two tables in our join will return all their rows?

This is determined by whether we use the LEFT or RIGHT keyword in our OUTER JOIN statement. If we use the LEFT keyword then all rows from the table on the left hand side of the JOIN keyword are returned. If we use the RIGHT keyword then all rows from the table on the right hand side of the JOIN keyword are returned.

In the above example the JOIN is defined as a LEFT outer join and the table that falls on the LEFT side of the JOIN keyword is the Employee table so all rows from the employee table will be returned. Note we are using table aliases to refer to the tables (if you are not sure what a table alias is please refer to our INNER JOIN article).

FROM Employee e LEFT OUTER JOIN TrainingTaken tt

What are the values that are returned from the other table if no match is found?

When no match is found any columns in the SELECT column list that are from the table which is not going to return all rows will contain NULL. We can see this in the last row of the resultset returned by the query where the row for Bill Gates has no related data in the TrainingTaken table. NULL takes the place of the values that would be taken from the TrainingTaken table if a match was found.

The OUTER keyword is optional in databases such as SQL Server and can be omitted if required. We shall demonstrate this along with writing the same query but with a RIGHT join instead of a LEFT join:

SELECT e.Name, tt.TrainingTitle, tt.TrainingDate
FROM  TrainingTaken tt RIGHT JOIN Employee e
	ON tt.EmployeeID = e.EmployeeID

This query will return the same results as our initial example above. Note we have switched the tables around the JOIN keyword so that the Employee table is now on the right hand side:

FROM TrainingTaken tt RIGHT JOIN Employee e

Conclusion

In this article we have demonstrated how the OUTER JOIN can be used to return all rows from one table regardless of whether any matches are found in the related table. We have seen how a LEFT or RIGHT outer join can be specified and that this will determine which of the two tables has all its rows returned. We have also seen how NULL is assigned to column values where a match could not be found.