Table Joins – SQL LEFT OUTER JOIN

What is a LEFT OUTER JOIN?

Like the INNER JOIN, a LEFT OUTER JOIN allows you to connect multiple tables in the same query. However, the LEFT OUTER JOIN returns all rows from the left table (first table listed) where the joining key matches a row in the second table listed. This is represented by the following Venn diagram:

An example will quickly clarify the difference between a LEFT OUTER JOIN and an INNER JOIN. Suppose we want to return the FirstName and LastName column for all rows of the Person.Person table and the JobTitle column from HumanResources.Employee if the BusinessEntityID matches. Essentially, if the ID matches to an employee’s ID then return their job. To complete this request, we use the query:

SELECT P.FirstName, P.LastName, E.JobTitle
FROM Person.Person P
LEFT OUTER JOIN HumanResources.Employee E
ON E.BusinessEntityID = P.BusinessEntityID

Notice in the results that the vast majority of the rows contain a NULL in the JobTitle column. This is because most rows in the Person.Person table do not match up to a row in the HumanResources.Employee table. But, this is exactly what we wanted to happen and this is why we use the LEFT OUTER JOIN.

As another example, let’s join multiple tables together to find, for each row in Sales.SalesOrderHeader, what the sales person’s name was. This requires multiple joins. We want to return every row from Sales.SalesOrderHeader regardless of whether or not a salesperson was associated with that sale. Restrict the Sales.SalesOrderHeader table to only rows where the OrderDate was between January 1, 2006 and June 30, 2006. Also, return the SalesOrderNumber and TotalDue columns from Sales.SalesOrderHeader. To complete this request, we use the query:

SELECT SOH.SalesOrderNumber, SOH.TotalDue, P.FirstName AS [Salesperson First Name], P.LastName AS [Salesperson Last Name]
FROM Sales.SalesOrderHeader SOH
LEFT OUTER JOIN Sales.SalesPerson SP
ON SP.BusinessEntityID = SOH.SalesPersonID
LEFT OUTER JOIN Person.Person P
ON SP.BusinessEntityID = P.BusinessEntityID
WHERE SOH.OrderDate BETWEEN '1/1/2006' AND '6/30/2006'

Our results show plenty of NULL values in the “Salesperson First Name” and “Salesperson Last Name” columns. This is good and expected, since we wanted to return rows whether or not the sale was associated with a salesperson.

Practice Problems

Having learned what makes the LEFT OUTER JOIN different from the INNER JOIN that we learned about in the last section, try to complete the practice problems below!

1. Find, for each row in Sales.SalesOrderHeader with an OrderDate between January 1, 2006 and June 30, 2006, the salesperson’s name and what their job title is. You will use the same query that was used in the last example of this lesson except you must complete one more join to the HumanResources.Employee table.
2. Using a LEFT OUTER JOIN, find the territory name associated with each sale in Sales.SalesOrderHeader. Use the Sales.SalesTerritory table.
3. Find each product’s subcategory regardless of whether or not the product has a product subcategory.