In an earlier section we covered how to define a standard JOIN also known as an INNER JOIN. This operator will join two tables based on a condition and return the matching results, but that’s a really limited view on the types of questions we might need to ask from a database. This is why we need to understand the SQL outer join.
There are 3 types of outer joins; the LEFT, RIGHT, and FULL OUTER JOIN. It’s possible we may come across another join type called a CROSS JOIN otherwise known as a cartesian or cartesian product. There are very limited use cases for these types of joins. In fact, inner joins and left joins are going to be used 99% of the time we write SQL queries.
Using LEFT Joins
This is the MOST common join in SQL. Get familiar, because you’ll write a lot of these if you’re being asked to write SQL scripts, views, or reports against a database. Just as the INNER keyword is options when using the JOIN operator, OUTER is also options when writing LEFT or RIGHT join in SQL.
Why use a left table join? For several reasons:
- We may want to know how many customers of at a company don’t have invoices. It’s possible that there are customers that have not yet been invoiced. Maybe our boss asks us send a report to them showing all customers that never received an invoice.
- Show me all our customers and their associated invoices in the last month. We still want to see the FULL customer list even if there are not invoices this month for that customer.
How do we write a query with a LEFT OUTER JOIN?
For the first use case, we would write it like this:
SELECT COUNT(DISTINCT C.CUSTOMERID)
FROM CUSTOMER C
LEFT JOIN INVOICE I ON I.CUSTOMERID = C.CUSTOMERID
WHERE I.CUSTOMERID IS NULL;
The first thing you may notice is that we did not include the OUTER keyword in the query. Remember the OUTER keyword is optional. There is also no such thing as a LEFT INNER JOIN in case you were wondering.
Driving Tables
Think of the table in the FROM clause as our main table or driving table. In this case the driving table would be Customer. We then left join that table to Invoice on CustomerId. This means we want all the results from Customer and the matching invoices based on the CustomerId.
Since we want to know which customers DO NOT have invoices, we put in a WHERE condition to see if there are any NULL values in the invoice table after joining. This would indicate that there was no match found in the joined table. You’ll find that as you become more familiar with SQL, there are actually multiple ways to find certain answers and one is usually more efficient/performant than the other.
We could also imagine the result set looking something like this:
With an inner join we wouldn’t have seen Best Buy or Ford since there aren’t any associated invoices. Since we used a left join they still appear and we can see the invoices are NULL. With the WHERE clause, the database would return only the two results. Since no invoices exist for these customers the values are NULL meaning there is no existing match.
In the second example, we could write the SQL script like this:
SELECT C.*, I.*
FROM CUSTOMER C
LEFT JOIN
(
SELECT * FROM INVOICE
WHERE strftime(‘%m’, INVOICEDATE) = strftime(‘%m’, CURRENT_DATE)
AND strftime(‘%Y’, INVOICEDATE) = strftime(‘%Y’, CURRENT_DATE)
) I ON I.CUSTOMERID = C.CUSTOMERID;
Show me all customers and their associated invoices in the last month.
If we ran the above query, we’d get results returned for all of the customers and any associated invoices tied to the latest month. You may also notice that there is a sub-query or “query within a query”. This is sometimes useful when we want to join to a filtered dataset and will be covered in another section since this is a bit more advanced.
Using RIGHT Joins
Joining to a table with a RIGHT JOIN is not very common and this type of SQL outer join can be avoided by re-ordering a query and using LEFT JOIN instead. In fact, if you’re using the DBeaver sample (SQLite) database, you’ll notice that RIGHT and FULL OUTER JOINs are not supported.
Regardless, we can still go over the inner workings of a RIGHT JOIN and how it can be used. In our first example of finding all customers that do not have invoices we could re-write the SQL with a RIGHT JOIN like this:
SELECT COUNT(DISTINCT C.CUSTOMERID)
FROM INVOICE I
RIGHT JOIN CUSTOMER C I ON I.CUSTOMERID = C.CUSTOMERID
WHERE I.CUSTOMERID IS NULL;
We’ve basically flipped the script. The SELECT doesn’t change and neither does the WHERE clause. With a LEFT JOIN we can think of it as a top-down approach where-as with RIGHT JOIN we can think of it as bottom-up. Using a LEFT JOIN is also a lot easier to read since we know the driving table will always be in the FROM clause. Visually we can see everything gets reversed.
Left and right joins do the exact same thing, but we hardly ever see anyone using a RIGHT JOIN in a SQL script. For readability, it’s better to stick with left joins as we even pointed out SQLite doesn’t even support RIGHT JOIN.
Using a FULL OUTER Join
We can almost think of the FULL OUTER join as a combination of the LEFT and RIGHT joins. Ultimately, we’re asking the database to return the results of two tables even if we’re unable to match based on the join condition. I’ve had to use these joins only a handful of times throughout my career. Below is a visual explanation.
But what does the output of the above actually look like? If we look at a simple example we can get a pretty good idea of how these types of joins function. Imagine we have a table that has a list of names of toys that our store carries. That table has a relationship to the brand name of the toy and connects to the brand table.
If we decided to join the two tables using a FULL OUTER JOIN on the BrandId field, we could write a query like this:
SELECT *
FROM Toys T
FULL OUTER JOIN Brand B ON B.BrandId = T.BrandId;
And the output would look like this:
We can see NULL (or empty) values returning from both sides of the table since there are no matches on each end. We also see an issue in one case where a brand was deleted. Now BrandId #7 doesn’t have a match on the Toys table. This scenario happens more often than one would like to think. Also, since there aren’t any toys with the Marvel or Russell brand, the Toy table outputs return NULL.
Final Thoughts
When writing a SQL outer join remember that the joined table will return NULL results if there are no matches based on the join condition. It’s also important to understand which table is returning the full result-set when using a LEFT or RIGHT join. This can be a lot to digest on a first pass. Don’t be afraid to review these topics until they become second nature.