Conor Cunningham’s blog on SQL Server, data-driven applications, and pretty much whatever other random stuff he decides to post.
A question from two of the MVPs:
Are these two queries conceptually the same?
select a.id, b.id, c.id
left join C on A.id = C.id
left join B on A.id = B.id;
left join B on A.id = B.id
left join C on A.id = C.id;
Answer: YES, they are equivalent.
Does SQL Server consider reordering these expressions? Also “Yes”, though you may need to do some more work to build a larger query where the order difference would matter. So, SQL Server does not strictly execute those non-full outer joins in the order you pass them syntactically. (This matches the general SQL Server policy of trying to make it not matter how you write the query for most normal formulations)
Similar to this, but not re-ordering. Is there a difference between using a joined-COLUMN and the original COLUMN? That is:
-- Join to the joined COLUMN
SELECT a.*, b.*, c.*
FROM a, b, c
WHERE b.a = a.a AND c.a = b.a;
-- Join to the original COLUMN
WHERE b.a = a.a AND c.a = a.a;
Conor, I have to admit I'm very confused. SQL Server DOES execute the full outer joins in the order provided... at least it does in the sense that the FROM table and the first outer-joined table are always linked together.
For example, the execution plans for the following 3 queries are all different (yet the only difference between them is the order of the LEFT JOINs)... your blog post seems to imply that they would all produce the same plan (and I think they SHOULD produce the same plan, but they do not)... or did I misunderstand what you were saying?
FROM Sales.Customer c
LEFT JOIN Sales.SalesOrderHeader h ON c.CustomerID=h.CustomerID
LEFT JOIN Sales.StoreContact s ON c.CustomerID=s.CustomerID
LEFT JOIN Sales.CustomerAddress a on c.CustomerID=a.CustomerID
Please see the following for some examples as to how the order of JOINs (and/or WHERE predicates) can make a huge difference:
While we may not reorder left outer joins in all cases, you should not assume that they are never reordered.
The Optimizer has complex logic to do reorderings, often with LOJ and other operators. Additionally, some LOJs can be simplified. For example, try adding "WHERE s.somecolumn = 5" to the comment query. This will reduce the LOJ to an inner join and allow more reorderings.
The advice I give to people is to not assume that there are no reorderings because you haven't seen it. If it is legal, there is likely a transformation in the optimizer that will do it, either now or in the future when it becomes important to the performance of enough applications to model.
Now I am going to go figure out why I am not getting mail when people post comments to my blog anymore :(.
"Now I am going to go figure out why I am not getting mail when people post comments to my blog anymore :(."
It's too bad that there's no option for commenters to receive emails when follow-up comments are posted. I didn't realize that you answerd my April question in July until now (November).
Thanks for the response. I'll look further into what happens when adding the predicate you suggested to see how things differ.
A small variation of your original query. Added one more join condition. Are the following two queries equivalent ?
and B.id = C.id
and C.id = B.id
I think not. My concern is, on what criteria the query processor may decide that the queries you mentioned(in your post) are the same and try both the plans, when one query is submitted.