Logical Joins: OUTER JOIN

In my previous post about logical joins I wrote about the most common type of SQL Server join, the INNER JOIN. Naturally the logical follow-up is to look at the OUTER JOIN. Syntax for OUTER JOIN is bit different from the INNER JOIN, as you need to define either LEFT, RIGHT or FULL keyword (more about FULL in another post). Unlike the INNER JOIN which only returns rows from the tables if there are equal values in join column, OUTER JOIN will return all the rows from either LEFT or RIGHT even if there is no matching value in the table you are joining.

And this is pretty much where I often use or see OUTER JOIN used, to find which rows in Table A don’t have matching rows in Table B. For the examples in the this post, I’ll be using the same ExampleDB schema I used with the INNER JOIN post. The first example with the INNER JOINS was to list all the customers and from which cities they were, but what if we were interested to know what cities we don’t have any customers in? Or maybe we want to validate our data, and see if we have customers missing addresses or other required data. These are the kind of questions you can get answers to with the OUTER JOIN.

Before going to the example queries, lets look at the graphical presentations below, and yes, I did dreaw these myself, thank you for asking! The left one is the LEFT OUTER JOIN and in the right there is the RIGHT OUTER JOIN (who would have guessed?)

And this is how the OUTER JOIN works.

Take all the rows from either LEFT or RIGHT
Take matching rows from the other table
Return NULL for rows that have no matches in join

Simple as that! Now to the examples.

Example 1: Finding Cities with no Customers

SELECT C.CityName
    ,C2.CustomerName
FROM Example.Cities AS C
    LEFT OUTER JOIN Example.Customers AS C2  ON C.RID = C2.PostalCityRID
WHERE C2.CustomerName IS NULL;

We could also run the same query without the WHERE clause, in which case it would return all the rows from the Example.Cities table (which is the LEFT one in this query). However when we use WHERE clause to check if the CustomerName IS NULL, we will only print out the rows where there is no customer found.

Example 2: Find Delivery Methods To Customers with no City

In the second example we’ll check if all our customers, who have delivery methods assigned also have valid address, including the city where they live in. We can get there, f.ex. by finding customers who have delivery method, but no information about the city they live in.

SELECT C2.CityName
    ,C.CustomerName
    ,DM.DeliveryMethodName
FROM Example.Customers AS C
LEFT OUTER JOIN Example.Cities AS C2
    ON C.PostalCityRID = C2.RID
INNER JOIN Example.DeliveryMethods AS DM
    ON C.DeliveryMethodRID = DM.RID
WHERE C2.CityName IS NULL;

From our Example database we can now see that we have two customers who have selected a delivery method, but have failed to provide full address.

Oh noes! Invalid customer data

Data Model Considerations

While OUTER JOINs can be useful for find missing/NULL values from your data, the data model itself can have impact on how you can go about finding them. In our Example database we are tracking customer orders, and it is very much possible that a Customer doesn’t have a single Order. However any Order in that database will always have a Customer.

And that’s it about the joins for now, hopefully you found this post helpful in understanding the basic concepts of OUTER JOIN.

SQLStarters