Because I feel pretty strongly about this and the entire focus of my blog is writing clear, clean and efficient SQL, I thought I'd repeat my response from a SQLTeam forum question
asks this, regarding OUTER JOINS:
Left/Right, does it matter. Is one better than the other? A few years ago consultant on a project for our company advised me to always write my queries to use Left joins. He had worked on the project to convert the original database application to MS SQL when Microsoft took it over. He claimed the design of the query engine handled Left joins more effeciently than right. I converted several queries that processed large datasets to Left join only and got quicker results. I have used Left exclusively since then. Has this concept ever been tested or written about?
Hi -- they are technically the same, but it is always clearer to use LEFT OUTER JOINS. I strongly recommend to never use RIGHT OUTER JOINS.
When you write a SQL statement, you should express your "base" in your FROM clause, and from there join to auxiliary tables. There is no SELECT statement that cannot be written in that manner, and it is a nice clear, clean way to organize your code. So, if you want ALL customers and ANY orders that match, I think we can all agree that it makes logical sense to express this as:
OUTER JOIN TO orders
Clearly, we are primarily selecting customers as our "base", and including any Orders that may or may not exist.
As a RIGHT OUTER JOIN, it becomes:
OUTER JOIN TO Customers
which doesn't make sense -- why are we selecting FROM Orders and joining TO Customers, when potentially we want to return Customers that don't have ANY orders?
Anyway, it is rare to get good advice from a consultant, but it appears that you actually did! Avoid RIGHT JOINS, and stick with LEFT JOINS. If a right outer join seems required to make your query work, you should re-write it and change your FROM clause to make it cleaner, simpler and clearer.
(by the same token, I strongly recommend to avoid FULL OUTER JOINS