SQL Gotcha: When an OUTER JOIN Accidentally Becomes an INNER JOIN

One of the first concepts you learn when writing SQL is the difference between an INNER JOIN and an OUTER JOIN (e.g., LEFT JOIN):

• INNER JOIN: only returns records that exist on both sides of the join
• OUTER JOIN: returns all the records from one side of the join, even if there are no matching records on the other side

There are two different OUTER JOINs: a LEFT JOIN and a RIGHT JOIN.  The only difference between the two is which order the tables are listed in the SQL text. Any LEFT JOIN can be written as a RIGHT JOIN by simply switching which table is listed first.

The "Join Properties" box in Access represents these three options with radio buttons that correspond as follows:

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN

I strongly advise that you only ever use LEFT JOINs.  

Using only LEFT JOINs leads to easier-to-read queries and helps avoid the "ambiguous outer joins" error when chaining together multiple OUTER JOIN'ed tables.

When using the Query by Example (QBE) window in Access, always drag your fields from the main/master/parent table to the subordinate/detail/child table.  This way, when you create your OUTER JOINs, they should end up as LEFT JOINs.

Here There Be Dragons

The INNER JOIN and OUTER JOIN concept is pretty straightforward.

However, there is one huge gotcha that trips up both beginners and seasoned SQL vets alike: the filtered OUTER JOIN.  It's a very easy mistake to make, especially when you are dealing with more complex queries than what I'm about to show here.

To illustrate the concept, we'll keep things simple with a bit of help from the longest running series in television history, The Simpsons.

Standard LEFT JOIN

In this standard LEFT JOIN, we include all records from the Adult table and any matching records from the Kid table:

SELECT Adult.AdultID, Adult.FullName, Kid.KidID, Kid.KidName
FROM Adult LEFT JOIN Kid ON Adult.AdultID = Kid.AdultID;

This returns all of the data in our tables.  Notice that there are two rows each for Homer and Ned (as they each have two kids) and one row for Mr. Burns (even though he has none).

Standard INNER JOIN

With a standard INNER JOIN, we only include rows that have matching records in the other table.

SELECT Adult.AdultID, Adult.FullName, Kid.KidID, Kid.KidName
FROM Adult INNER JOIN Kid ON Adult.AdultID = Kid.AdultID;

Mr. Burns is conspicuously absent from this table, as he has no children.

Filtered INNER JOIN

Let's say we only want to see father-son relationships.  We'll add a WHERE clause to filter on Gender = 'M':

SELECT Adult.AdultID, Adult.FullName, Kid.KidID, Kid.KidName, Kid.Gender
FROM Adult INNER JOIN Kid ON Adult.AdultID = Kid.AdultID
WHERE (((Kid.Gender)='M'));

The 3 father-son pairs are returned.

Filtered OUTER JOIN: Simple, Easy, and Wrong

What if we want to see all of the adults, but only the male children.

You would think it would be as easy as switching the join type from our filtered INNER JOIN above:

SELECT Adult.AdultID, Adult.FullName, Kid.KidID, Kid.KidName, Kid.Gender
FROM Adult LEFT JOIN Kid ON Adult.AdultID = Kid.AdultID
WHERE (((Kid.Gender)='M'));

You would think that...but you would be wrong.

Here are the results from the above query.  I swear it's not the same screenshot as above, it just looks that way:

Not the results you were intuitively expecting are they?

If you apply a WHERE filter directly to the OUTER JOIN side of a query you will effectively turn it into an INNER JOIN.

The Workaround: OUTER JOIN to Filtered Subquery

Don't despair!  

There is a relatively easy workaround for this situation.  The key is that we need to apply any filtering conditions on the OUTER JOIN'ed side of the query to a subquery and then join on that.

Here's what the workaround looks like in SQL:

SELECT Adult.AdultID, Adult.FullName, K.KidID, K.KidName, K.Gender
FROM Adult LEFT JOIN 
 (SELECT * FROM Kid WHERE Kid.Gender = 'M') AS K
    ON Adult.AdultID = K.AdultID;

Simplified Solution: Save the Subquery

I tend to write these kinds of simple subqueries by hand (as shown above), but when you are first starting out–or if the subquery is complex–you are better off saving the subquery as a query definition and then using that in your query.

Step 1: Create a query named MaleKids

SELECT Kid.*
FROM Kid
WHERE (((Kid.Gender)='M'));

Step 2: Create a Simple OUTER JOIN Using the MaleKids query

SELECT Adult.AdultID, Adult.FullName, MaleKids.KidID, MaleKids.KidName, MaleKids.Gender
FROM Adult LEFT JOIN MaleKids ON Adult.AdultID = MaleKids.AdultID;

Notice that the Criteria row is empty in the Query-by-Example (QBE) window and there is no WHERE clause in the SQL.