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.
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.