pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Tuesday, December 06, 2016

Actual Chats: RIGHT OUTER joins in the wild

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.

5 comments:

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

    I haven't as yet encountered a compelling argument for using a RIGHT JOIN in nearly 20 years of writing SQL queries. Whenever I've encountered them, they seem to largely have emerged from query designers.

    ReplyDelete
    Replies
    1. I've also never actually written one, only seen them "in the wild" so to speak. Thanks for commenting, appreciate it.

      Delete
  2. The reason we put the right outer join into SQL was mostly symmetry. I honestly cannot remember who brought that before the ANSI committee after all these decades, but the basic definition of matched and unmatched rows between two tables in the join made it easy. Remember we also have full outer join and outer unions (Nobody wants to implement it)!

    My experience has been the use of left versus right outer joins depends on what your linguistic tradition is; which direction do you read? For example, when I taught a lot of Arabic students decades ago, their sorting column would be on the right-hand side of the output in procedural languages. Likewise, my Chinese students would put the exceptional cases in the IF-THEN clause and the exceptions in the ELSE clause. I also saw (0 < x) rather than the (x >0) that Latin alphabet left to right users tend to put in their code. None of this is wrong; it is just a different way of arranging data on a page.

    ReplyDelete
    Replies
    1. Great insight, thanks for commenting and contributing.

      Delete
  3. I totally agree with both Celko and you; right joins seem to make sense to people whose primary language is right-to-left, but for those of us with left-to-right languages the left join is much easier to read and follow. That's worth bearing in mind for sake of readability. Whatever your language, consistently using only one or the other in a given query is probably easier to follow.

    I haven't had broad experience reading queries written by people who prefer right-to-left; but I have seen right joins in the wild, often generated by software rather than hand-written. (Like Matt Moran, if I'm reading what he wrote correctly.)

    ReplyDelete

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.