Database Fundamentals #21: Using the JOIN Operator, OUTER JOIN

The OUTER JOIN returns one complete set of data and then the matching values from the other set. The syntax is basically the same as INNER JOIN but you have to include whether or not you’re dealing with a RIGHT or a LEFT JOIN. The OUTER word, just like the INNER key word, is not required.

OUTER JOIN

Imagine a situation where you have a list of people. Some of those people have financial transactions, but some do not. If you want a query that lists all people in the system, including those with financial transactions, the query might look like this:

SELECT p.LastName,
ft.TransactionAmount,
ft.TransactionDate,
ft.TransactionTime
FROM Personnel.Person AS p
LEFT JOIN Finance.FinancialTransaction AS ft
ON p.PersonID = ft.PersonID;

Except for the addition of the LEFT key word, this query could just as easily be using the INNER JOIN operation until you see the results shown here:

While it may look like more data has been added to the Personnel.Person table, it hasn’t. What’s going on is exactly how the OUTER JOIN has been described. The OUTER JOIN returns all rows from the LEFT or RIGHT table, which are represented by the six names from that table. Then the OUTER JOIN adds in all matching rows from the second result set, which is why you see the value ‘Adolphus’ three times, because it matches three different rows in the Finance.FinancialTransaction table. If you reverse the query:

SELECT p.LastName,
ft.TransactionAmount,
ft.TransactionDate,
ft.TransactionTime
FROM Personnel.Person AS p
RIGHT JOIN Finance.FinancialTransaction AS ft
ON p.PersonID = ft.PersonID;

You are going to get back all rows from the RIGHT table, in this case, Finance.FinanacialTransaction, and matching rows from the Personnel.Person table for the results shown:

Figure 5:15: The results of the same OUTER JOIN reversed to a RIGHT JOIN

This may look like an INNER JOIN, but that’s only because the data provided looks this way. All rows from the RIGHT table, Finance.FinancialTransaction, have a corresponding row in the Personnel.Person table, and there are no duplicate values or missing values to cause the multiplication factor that you’ve already seen.

You can added more tables to the FROM clause and define OUTER JOIN criteria for them. You can also combined OUTER JOIN with INNER JOIN between distinct sets of tables. You just need to be careful that you understand fully what data will be returned as you put various types of JOINS together with multiple tables.

Finally, you need to be cautious about filtering when using OUTER JOINs. If you put the filter for an OUTER JOIN in the WHERE clause of your query, it basically breaks the OUTER JOIN, turning it into an INNER JOIN. So, while filtering should generally be done in the WHERE or HAVING clauses, in the case of an OUTER JOIN, be sure to place your filters in the ON criteria.

Conclusion

Whether you use a LEFT or RIGHT join, the fundamental behavior doesn’t change, but the logic does, so plan accordingly when writing your queries. The next post in the Fundamentals series will be on the CROSS JOIN.