Is Performance Better With LEFT JOIN or RIGHT JOIN?

Grant Fritchey, 2015-05-20 (first published: 2015-05-11)

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:

LeftRightJOIN

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.

The post Is Performance Better With LEFT JOIN or RIGHT JOIN? appeared first on Home Of The Scary DBA.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads