Left Outer Join

Introduction
This post shows how to create a left outer join. Outer joins are often used in reporting. It also shows how to modify the outer join to create a "find unmatched" query.

Outer Joins
When you join two tables in SOL the default join type is an inner join. This means that only rows with matching key values from each table are retrieved. For example if I join customers with their orders I will not see customer names with no orders. But it is common in reporting that you want to see all rows from the "one" side of the relationship even if there is no match. In the above example I might want to see all my customer names even if they have not placed an order yet. This is accomplished with an outer join. They come in three flavors: left, right and full.

• Left - all matched rows plus unmatched from the "left" table. Note that left refers to reading order. It means the first table mentioned in the query, the one to the "left".
• Right - all unmatched plus unmatched from the right table. The unmatched from the right table would imply they are orphan records since their foreign key did not refer to an existing parent record.
• Full- not often seen. Adds unmatched from both left and right tables. 

We will present an example of the left outer join, which is the most common, below. This example uses the bookstore database which can be downloaded from box.net.

Left Outer Join Syntax
Here is the basic syntax for a left outer join. We will assume the "one" side is the left table. Note the word outer is optional.

Select columnlist
from table1
left [outer] join table2
on table1.primarykey = table2.foreignkey;

Example
Below is an example using the bookstore database.

Results
Below is a section of the result set. Note that when there is no match the missing values are filled in as nulls by SOL. In the results below the Browns have not placed any orders.

Unmatched (Negative) Left Outer Join
Suppose you want to list all customers who have not placed an order. You can do this by creating a left outer join from the customers table to the orders table. As we have seen this will produce a list of all customers along with their orders. The list will include customers that have not placed any orders since a left join includes all rows from the left table. You can restrict the result set to only those who have not placed orders. Do this by adding a where clause to test for null order numbers. The order number is the primary key in the orders table and wouldn't be in the result set as a null unless it was generated by the left join. This is a common query to test for missing information or missing records.

Syntax
select columnlist
from table1
left [outer] join table2
on table1.primarykey = table2.foreignkey
where table2.primarykey is null;

Example

Results
Below are the results using the bookstore sample database. (In reality you might not want to show the order columns).

More to follow...
There is another type of unmatched join where there is a partial match. For example: list all customers and the titles of books they bought that were written by a certain author. Include all customers even if they did not buy any books from the given author. We will cover this in a subsequent post and in our Intermediate SOL course (SQL302 & 312).