Conor vs. Left Outer Join Reordering

  • Article

A question from two of the MVPs:

Are these two queries conceptually the same?

select a.id, b.id, c.id

from A

left join C on A.id = C.id

left join B on A.id = B.id;

 

select a.id, b.id, c.id

from A

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)

 

Happy Querying!

 

Conor

Comments

  • Anonymous
    April 25, 2010
    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; and -- Join to the original COLUMN SELECT a., b., c.* FROM a, b, c WHERE b.a = a.a AND c.a = a.a;

  • Anonymous
    April 26, 2010
    The comment has been removed

  • Anonymous
    April 27, 2010
    Please see the following for some examples as to how the order of JOINs (and/or WHERE predicates) can make a huge difference: http://bradsruminations.blogspot.com/2010/04/looking-under-hood.html

  • Anonymous
    July 24, 2010
    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 :(.

  • Anonymous
    November 10, 2010
    "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. --Brad

  • Anonymous
    October 07, 2015
    A small variation of your original query. Added one more join condition. Are the following two queries equivalent ? select a.id, b.id, c.id from A left join C on A.id = C.id left join B on A.id = B.id              and B.id = C.id ; select a.id, b.id, c.id from A left join B on A.id = B.id left join C on A.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.