Conor vs. Left Outer Join Reordering

Conor vs. Left Outer Join Reordering

  • Comments 5

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

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • 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;

  • 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?

    SELECT c.CustomerID

    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

    SELECT c.CustomerID

    FROM Sales.Customer c

    LEFT JOIN Sales.StoreContact s ON c.CustomerID=s.CustomerID

    LEFT JOIN Sales.SalesOrderHeader h ON c.CustomerID=h.CustomerID

    LEFT JOIN Sales.CustomerAddress a on c.CustomerID=a.CustomerID

    SELECT c.CustomerID

    FROM Sales.Customer c

    LEFT JOIN Sales.CustomerAddress a on c.CustomerID=a.CustomerID

    LEFT JOIN Sales.SalesOrderHeader h ON c.CustomerID=h.CustomerID

    LEFT JOIN Sales.StoreContact s ON c.CustomerID=s.CustomerID

  • 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

  • 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.

    --Brad

Page 1 of 1 (5 items)