T-SQL 101: 107 Using other outer joins with RIGHT OUTER JOIN, FULL OUTER JOIN

After discussing LEFT OUTER JOIN, you might wonder if there is also a RIGHT OUTER JOIN. There is, and it’s the reverse of the left one.

What a RIGHT OUTER JOIN from Products to ProductGroups says is that I want at least one row for every row in ProductGroups. You can see that in the query example above. The product group Hot Food currently has no products in it. The RIGHT OUTER JOIN causes it to be returned, as similar to what happens with a LEFT OUTER JOIN, the columns from the non-matching table are returned as NULL. In this example, the product description for that group is returned as NULL.

Common?

It’s worth mentioning, that I almost never write RIGHT OUTER JOIN in a query, and I’m sure I’m not alone on that. Most times, I think in terms of left outer joins, and I can just reverse the order of the tables in the query. But I can’t always do that, so it’s useful that this option exists.

In fact, you’ll find that most database engines don’t even have a way of handling right outer joins. Internally, they just swap things around and have a single strategy for doing the processing. SQL Server used to also be like that, but nowadays you will see right outer joins appearing in the query plans.

Full Outer Join

The other type of outer join is a FULL OUTER JOIN. This is a combination of left and right outer joins. It’s often misunderstood as a cross join but it is completely different. It says that you want to return all the matching rows  from the two tables (or rowsets), and then at least one row for any other row in the first table, and at least one row for anything in the second table.

It’s basically like having a left and a right outer join at the same time.

I tend to use FULL OUTER JOIN much more frequently than I use RIGHT OUTER JOIN.

Abbreviations

Similar to the way I mentioned that INNER JOIN can just be written as JOIN, there are abbreviations for the outer joins as shown below.

LEFT OUTER JOIN -> LEFT JOIN RIGHT OUTER JOIN -> RIGHT JOIN FULL OUTER JOIN -> FULL JOIN

I tend to write them in full. I like the clarity, and some DB engines (not SQL Server) require it anyway.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-03