Colleague:
I think I just encountered my first Right join
*shocked*
Me:
huh. weird.
a rare Right Join has appeared!
Colleague:
catch it!
Me:
You use ON. It's super effective!
Colleague:
haha
how often do you see them?
Me:
I've used RIGHT join maybe twice in 10 years.
Colleague:
lol
Me:
just makes things difficult to read.
Colleague:
yea it took me a while
it was at the bottom of the query
i kept wondering what was bringing in the extra rows till I found it
And that's why, whenever possible, avoid using RIGHT OUTER. It breaks the nice top-to-bottom flow of your complicated FROM clauses, forces you to page-split your brain around data sets, and generally makes it more difficult for the next DBA to understand.
For this overly-simplistic example, these two queries on the WideWorldImporters database are identical and have the same execution plans:
SELECT s.CustomerName , pp.FullName , ap.FullName FROM Sales.Customers AS s LEFT OUTER JOIN [Application].People AS pp ON s.PrimaryContactPersonID = pp.PersonID LEFT OUTER JOIN [Application].People AS ap ON s.AlternateContactPersonID = ap.PersonID INNER JOIN Sales.CustomerCategories AS sc ON s.CustomerCategoryID = sc.CustomerCategoryID
SELECT s.CustomerName , pp.FullName , ap.FullName FROM [Application].People AS pp RIGHT OUTER JOIN Sales.Customers AS s ON s.PrimaryContactPersonID = pp.PersonID LEFT OUTER JOIN [Application].People AS ap ON s.AlternateContactPersonID = ap.PersonID INNER JOIN Sales.CustomerCategories AS sc ON s.CustomerCategoryID = sc.CustomerCategoryID
The first query allows you to do the easily relational math as you read. The second has you flipping the dataset before moving your eye back up to the top.
There are certainly legitimate albeit complicated cases for using RIGHT OUTER, but from a readability and maintainability, I'd prefer to stick with LEFT OUTER.
