In May 2016, we expanded on the topic of joins (and left outer joins) in a series of posts on SQL for super users.
While we’ve left this original post in place (below), we also recommend that you check out the series for a more detailed exploration of the topic. Here are the links:
- SQL for Super Users: A 7-Part Series
- SQL for Super Users Part 1: Basic Terminology
- SQL for Super Users Part 2: SELECT Statements
- SQL for Super Users Part 3: Adding Another Table Using JOINs
- SQL for Super Users Part 4: The Multi-Column JOIN
- SQL for Super Users Part 5: The Left Outer Join
- SQL for Super Users Part 6: Aggregates and Having vs. Where
- SQL for Super Users Part 7: Date vs. Date-Time
You’ve taken on the challenge of writing reports—whether in Crystal Reports, Access or any similar tool. Once you’ve managed to get a basic report out, one of the first “intermediate” tasks you’ll need to understand is the “Left Outer Join.”
As every report writer understands, any given report requires multiple tables. For example when you create a list of vendor invoices from the invoice table, you’ll want to get the vendor name from the vendor table. That’s a simple example—if your system is working, for every invoice you have you expect to have a vendor record located somewhere.
But what happens if you’re not certain whether a certain piece of data exists? You use a Left Outer Join. Here’s an example (based on a recent project for a customer which will provided the basis for a future post on null values in Crystal Reports):
Imagine you’re a toy distributor. You stock many popular games: Sorry!, Othello, Connect 4 and Monopoly. So you have a basic table with all your items and the current Stock on Hand (SOH). This “Item Master” data might look something like this:
Simple enough. But what if your buyer, in looking through this quick inventory list, wants to know the next date that you can expect more inventory in? When will you be able to start selling Othello again?
In a standard ERP system, that information is most likely stored in a purchase order line file, which may look something like this:
If you created a report combining the SOH from the “item master” and the expected date from the line file, this would be your result:
What happened to Sorry!? Sure, there’s no PO open for Sorry!, but you still wanted to see the SOH.
In every basic reporting system, the default join is called an “Inner Join”, meaning that you need to have data in every table you use. But that doesn’t always happen – sometimes there is no data in a given field.
If you switch the option to a “Left Outer Join” that will tell the system that you want EVERY record from your item master with whatever records may or may not exist in your purchase order line file. And when you switch the option to Left Outer Join you get the following output:
There are different ways to specify the join in the various systems. But if you are always going to have data in one table, and you may or may not have data in another table, you need to do a Left Outer Join.