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.

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