Understanding SQL Outer Joins

Oct 11, 2022 by Robert Gravelle

The Outer Join is the least understood of all the SQL Join types. Perhaps it’s because Outer Joins are required somewhat less often than other join types. In any case, there is nothing inherently strange about Outer Joins. As we’ll see in this blog article, a few examples of the Outer Join in action should be enough to clarify any misapprehensions and/or confusion you may have about them.

This blog will first describe the syntax and purpose of the Outer Join statement, which will then be followed by some illustrative examples.

The OUTER JOIN (or FULL OUTER JOIN if you like) keyword returns all records of two joined tables when there is a match in either the left (table A) or right (table B) table records. The following VEN diagram depicts the potential matches and OUTER JOIN syntax:

Hence, FULL OUTER JOIN returns unmatched rows from both tables, as well as matched rows in both tables. In other words, rows are returned by the query regardless of whether or not the join field (Clave) value is matched across both tables.

Still confused. Don’t worry, we’ll go over some examples in the next section to clear things up.

In this tutorial we will use the well-known Northwind sample database.

The following SQL statement selects all customers, and all orders:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

One of the hallmarks of a result set produced by an OUTER JOIN query is that you will see Null values in either joined columns as one may appear in one table, but not the other. We can observe that here in this screen capture of the above query and results in Navicat Premium 16:

Or course, you will never see Nulls in both table columns because a value must appear in at least one table. It should also be noted that the present of a Null in the ContactName columns is problematic because it means that an order was placed that is not associated to an existing customer. This would point to a flaw in the database design, most likely a missing Foreign Key Constraint.

Our second example fetches data from a Project Management database, namely project managers and projects. Here’s the SQL:

SELECT 
    m.name member, 
    p.title project
FROM 
    pm.members m
    FULL OUTER JOIN pm.projects p 
        ON p.id = m.project_id;

Again, we can see Null values (at least one Null)

In this case, the results indicate that Jack Daniel has no projects at the moment. Whether or not this represents an issue would depend on that organization’s particular operations. It may be perfectly reasonable for a Project Manager to be without a project, or for projects to be unnasigned, at any given time.

Hopefully today’s blog helped shed some light on the purpose and usage of Outer Joins in your queries. One final word of warning: Outer Joins can result in very large result sets, so use them sparingly, and include filtering clauses such as WHERE to minimize the number of rows returned.