I tend to write my queries using LEFT JOIN. Why? Because logically I see it in my head like this:
Give me all the rows from this table and only those rows that match from the other table.
But, wouldn’t this logic work just as well:
Give me only the rows in this table that match the rows from this other table where I’m selecting all of them.
I know. If I worked on it some more I could make that a better sentence, but I’m pretty sure the logic is still sound. Only matching rows from one data set, all the rows from another data set. In short, RIGHT JOIN.
I read recently that we ought to be making everything into a LEFT JOIN because it performs better. I suspect someone had a bad day, wrote the JOIN criteria poorly, and a new bit of cargo cult wisdom was discovered. Because, well, let’s look at the queries:
SELECT *
FROM Sales.SalesOrderHeader AS soh
LEFT JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = soh.SalesOrderID;
SELECT *
FROM Sales.SalesOrderDetail AS sod
RIGHT JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID = sod.SalesOrderID;
Yeah, I know. Not exactly the height of query optimization and elegance. But, the point is made pretty easily. When these queries run, both return 121317 rows. They have almost identical performance on my system at 2.8 seconds and 2.7 seconds with identical reads and CPU. One run. You can run it a million times if you want. I’m not going to and here’s why:
Those execution plans are the same. Just because they look the same? How do I know that for sure? Because they have the same query_plan_hash value:Â 0x857588F9A4394676.
Now, if we changed the logic so that SalesOrderDetail was on the “all” side of the query, of course that would change things, but, that changes to logic, not just the JOIN. The query optimizer is able to deal with what is basically just syntactic sugar. No. LEFT JOIN is not better than RIGHT JOIN. It’s just different logic that is dealt with by the optimizer.
Want to talk query tuning and execution plans? Let’s get together for an all day seminar at Connections this year. Check it out here.
[…] Is Performance Better With LEFT JOIN or RIGHT JOIN? – Grant Fritchey (Blog|Twitter) […]
Actually you write LEFT OUTER JOIN because you come from a culture that reads left to right. And I bet your loops were “1 to n by 1” and “n to 1 by -1” back in the procedural language days. I will also bet that you wrote “IF THEN ELSE ENDIF;, but my Chinese students wrote “IF NOT THEN ELSE ENDIF;”
That makes perfect sense actually. And so does the syntax flexibility. Thanks. I love the perspective you bring to these things.
Hi
Thanks for the cultural perspective Joe on something computer logic.
A good insight that could not have come from a manual.
Isn’t there a bigger issue here? If some developers are writing left join and others are writing right join, does the chance of a deadlock increase?
Ron,
It really depends on what the optimizer does. In the example (granted, simplistic) the JOIN order as interpreted by the optimizer is identical despite the differences in the query. I would suspect in most cases this would be true (but certainly not all). It’s probably something to keep an eye out for if you are experiencing deadlocks.
Could it be that left joins were invented for left handed people and right joins for right handed?
I’ve seen (not used) a vendor that we work with use both Right and Left joins in some of their database Views, that is, the same view uses both left and right. From what I can tell, the view _name_ implied table A, but they then wanted to right join to a main table B (e.g. a transaction table), and then left join B to reference table C, etc. That does allow for nulls in table A columns referenced in the view, but the vendor was fine with that. That mixed join convention gave me some pause for thought about performance, but that wasn’t the end of it. They also made each view Select Top 100% so they could order the rows. Hmm – now I _really_ wonder about performance.
There might be a little more about this than what we see here.
For a little query like this the optimizer has not problem finding the “best” plan, but for a very complicated query, involving tables with several indexes, a lot of joins, etc. the optimizer will return a “good enough” plan. That means that the optimizer did not test all possibilities. That might give a slight edge to the permutations that the optimizer tests first. IF the optimizer tests first joining tables in a way that favors LEFT join that could give it an edge against RIGHT joins. Of course that is a big IF, but should be considered for very complex queries.
Nate,
That’s a pretty common trick that people do to be able to ORDER a view. I wouldn’t say it’s good or bad without seeing what the optimizer is doing. As to the mixing of the LEFT & RIGHT JOINs, again, logically confusing, but I’m pretty sure that under most circumstances it doesn’t matter to the optimizer.
Filipe,
Based on everything I’ve seen with how the optimizer works, if we’re getting “Good Enough Plan Found” as the Reason for Early Termination on the plan, then the optimizer has dealt with those JOINs appropriately. Now, if you’re getting “Timeout” as the reason, you’ve got a case there that it’s possible that making them all LEFT or all RIGHT could influence the plan outcome.
Maybe you’re not looking at the right performance indicators. LEFT JOIN might give you more lines of code per minute than RIGHT JOIN. Of course, that’s only metrics for developers and code reviewers.
Seems to me that left and right must be completely interchangeable.
Either can be represented graphically, completely and unambiguously, using table boxes with little pointy arrows from a column in one to a column in the other, with the heads of the arrows determined by the relative positions onto which you happened to drop the boxes, and whether it’s a left or right join.
If they weren’t equivalent, just think of the unexplored optimization opportunities – up outer joins, down outer joins. In and out outer joins. To and fro. Past and future outer joins! The mind boggles!