Can anyone fathom a why do this or how it works correctly with this bit of SQL?

Question

I've changed the table names but I found this FROM statement in a SP I'm optimizing and I'm wondering the how this could come into being, why would you ever do anything like this, and why does it still work.

FROM         tblPezFill pf 
RIGHT OUTER JOIN tblWolveLocation stfl 
RIGHT OUTER JOIN tblDuckPez pp 
RIGHT OUTER JOIN tblChaos o 
INNER JOIN tblBillLeg bi 
INNER JOIN @Facty selFac  
ON bi.WolveId = selFac.WolveId 
ON o.ChaosID = bi.ChaosId 
LEFT OUTER JOIN vwDonald stPres 
RIGHT OUTER JOIN tblStockChaos so 
            ON stPres.DonaldId = so.DonaldId 
            ON o.StockChaosID = so.StockChaosID 
            ON pp.PezID = o.PezID 
            ON stfl.WolveLocationId = so.WolveLocationId 
            ON pf.PezFillID = o.PezFillID 
LEFT OUTER JOIN tblCheeseburger p ON bi.CheeseburgerId = p.CheeseburgerId 
LEFT OUTER JOIN tblChaosCheeseburgerShipped ops ON o.ChaosID = ops.ChaosID 
LEFT OUTER JOIN vwDonald pres ON pp.DonaldId = pres.DonaldId 
LEFT OUTER JOIN tblDuck ON pp.DuckID = tblDuck.DuckId 
LEFT OUTER JOIN vwDuck ON pp.DuckID = vwDuck.DuckId 
LEFT OUTER JOIN tblWolveLocation fl ON pf.WolveLocationId = fl.WolveLocationId

I've cleaned it up to

FROM  tblBillLeg bi 
INNER JOIN @Facty  selFac ON bi.WolveId = selFac.WolveId 
LEFT OUTER JOIN tblCheeseburger p ON bi.CheeseburgerId = p.CheeseburgerId 
LEFT OUTER JOIN tblChaos o ON o.ChaosID = bi.ChaosId 
LEFT OUTER JOIN tblDuckPez pp ON pp.PezID = o.PezID 
LEFT OUTER JOIN tblPezFill pf ON pf.PezFillID = o.PezFillID
LEFT OUTER JOIN tblChaosCheeseburgerShipped ops ON o.ChaosID = ops.ChaosID 
LEFT OUTER JOIN tblStockChaos so ON o.StockChaosID = so.StockChaosID 
LEFT OUTER JOIN tblWolveLocation stfl ON stfl.WolveLocationId = so.WolveLocationId 
LEFT OUTER JOIN vwDonald stPres ON stPres.DonaldId = so.DonaldId 
LEFT OUTER JOIN vwDonald pres ON pp.DonaldId = pres.DonaldId 
LEFT OUTER JOIN tblDuck ON pp.DuckID = tblDuck.DuckId 
LEFT OUTER JOIN vwDuck ON pp.DuckID = vwDuck.DuckId 
LEFT OUTER JOIN tblWolveLocation fl ON pf.WolveLocationId = fl.WolveLocationId

Which I believe should be equivalent, I need some testing data. I just don't get how the first version really worked at all.

Answer 1

Join expressions in SQL are constructive, just like arithmetic expressions.

That is, you can do the following:

A JOIN B ON <expr-AB>

But you can also substitute another join expression for B:

A JOIN (B JOIN C ON <expr-BC>) ON <expr-AB>

And the parens may be superfluous if the natural order of precedence would give the same result:

A JOIN B JOIN C ON <expr-BC> ON <expr-AB>

So the huge fugly query you showed is legal syntax, but it certainly could be more clear.

---

I see the query as a hierarchy like this:

so
so --left--> stfl
so --left--> stPres
so --left--> o
             o --inner-> bi
                         bi --inner-> selFac
                         bi --left--> p
             o --left--> pp
                         pp --left--> pres
                         pp --left--> tblDuck
                         pp --left--> vwDuck
             o --left--> pf
                         pf --left--> fl
             o --left--> ops

I would rewrite the query like this:

FROM tblStockChaos so 
LEFT OUTER JOIN tblWolveLocation stfl
  ON so.WolveLocationId = stfl.WolveLocationId 
LEFT OUTER JOIN vwDonald stPres
  ON so.DonaldId = stPres.DonaldId 
LEFT OUTER JOIN
  (tblChaos o 
   INNER JOIN
     (tblBillLeg bi
      INNER JOIN @Facty selFac
        ON bi.WolveId = selFac.WolveId
      LEFT OUTER JOIN tblCheeseburger p
        ON bi.CheeseburgerId = p.CheeseburgerId)
     ON o.ChaosID = bi.ChaosId 
   LEFT OUTER JOIN
     (tblDuckPez pp 
      LEFT OUTER JOIN vwDonald pres
        ON pp.DonaldId = pres.DonaldId 
      LEFT OUTER JOIN tblDuck
        ON pp.DuckID = tblDuck.DuckId 
      LEFT OUTER JOIN vwDuck
        ON pp.DuckID = vwDuck.DuckId)
     ON o.PezID = pp.PezID
   LEFT OUTER JOIN tblPezFill 
     (tblPezFill pf
      LEFT OUTER JOIN tblWolveLocation fl
        ON pf.WolveLocationId = fl.WolveLocationId)
     ON o.PezFillID = pf.PezFillID
   LEFT OUTER JOIN tblChaosCheeseburgerShipped ops 
     ON o.ChaosID = ops.ChaosID
  )

---

I can't guarantee I've got all the conditions perfect. But that's the idea.

Answer 2

My approach was to generate a query plan using table variables and then attempt to re-write the query in the manner it would be compiled.

If you setup with this code:

DECLARE @tblPezFill TABLE (PezFillID INT, WolveLocationID INT) -- pf / f1
DECLARE @tblWolveLocation TABLE (WolveLocationID INT) -- stfl
DECLARE @tblDuckPez TABLE (Pezid INT, DonaldID INT, DuckID INT) -- pp
DECLARE @tblChaos TABLE (StockChaosID INT, ChaosID INT, PezID INT, 
  PezFillID INT) -- o
DECLARE @tblBillLeg TABLE (WolveID INT, ChaosID INT, 
  CheeseburgerID INT) -- bi
DECLARE @Facty TABLE (WolveId INT) -- selFac
DECLARE @vwDonald TABLE (DonaldID INT) -- stPres
DECLARE @tblStockChaos TABLE (StockChaosID INT, DonaldID INT, 
  WolveLocationID INT) -- so
DECLARE @tblCheeseburger TABLE (CheeseburgerID INT) -- p
DECLARE @tblChaosCheeseburgerShipped TABLE (ChaosID INT) -- ops
DECLARE @tblDuck TABLE (DuckID INT) -- tblDuck
DECLARE @vwDuck TABLE (DuckID INT) -- vwDuck

And then run the query:

SELECT * 
FROM         @tblPezFill pf 
RIGHT OUTER JOIN @tblWolveLocation stfl 
RIGHT OUTER JOIN @tblDuckPez pp 
RIGHT OUTER JOIN @tblChaos o 
INNER JOIN @tblBillLeg bi 
INNER JOIN @Facty selFac  
ON bi.WolveId = selFac.WolveId 
ON o.ChaosID = bi.ChaosId 
LEFT OUTER JOIN @vwDonald stPres 
RIGHT OUTER JOIN @tblStockChaos so 
            ON stPres.DonaldId = so.DonaldId 
            ON o.StockChaosID = so.StockChaosID 
            ON pp.PezID = o.PezID 
            ON stfl.WolveLocationId = so.WolveLocationId 
            ON pf.PezFillID = o.PezFillID 
LEFT OUTER JOIN @tblCheeseburger p ON bi.CheeseburgerId = p.CheeseburgerId 
LEFT OUTER JOIN @tblChaosCheeseburgerShipped ops ON o.ChaosID = ops.ChaosID 
LEFT OUTER JOIN @vwDonald pres ON pp.DonaldId = pres.DonaldId 
LEFT OUTER JOIN @tblDuck tblDuck ON pp.DuckID = tblDuck.DuckId 
LEFT OUTER JOIN @vwDuck vwDuck ON pp.DuckID = vwDuck.DuckId 
LEFT OUTER JOIN @tblWolveLocation fl ON pf.WolveLocationId = fl.WolveLocationId

The beginning of the query plan looks like this:

The remainder is just a series of left joins so I left them out. My thinking is that if you can duplicate the query plan, you'll know that your new query exactly matches the logic of the original.

Here is what I came up with:

SELECT *
FROM (
  SELECT 
    a2.WolveId,
    a2.ChaosID,
    a2.CheeseburgerID,
    a2.PezFillID,
    a2.PezID,
    a2.StockChaosID,
    a3.DonaldID,
    a3.WolveLocationID
  FROM ( 
    SELECT
      selFac.WolveId,
      a1.ChaosID,
      a1.CheeseburgerID,
      a1.PezFillID,
      a1.PezID,
      a1.StockChaosID
    FROM @Facty selFac 
    INNER JOIN (
      SELECT
        bi.ChaosID,
        bi.CheeseburgerID,
        bi.WolveID,
        o.PezFillID,
        o.PezID,
        o.StockChaosID
      FROM @tblBillLeg bi
      INNER JOIN @tblChaos o
        ON bi.ChaosID = o.ChaosID
    ) a1 
      ON selFac.WolveId = a1.WolveID
  ) a2
  LEFT OUTER JOIN (
    SELECT
      stPres.DonaldID,
      so.StockChaosID,
      so.WolveLocationID 
    FROM @vwDonald stPres
    LEFT OUTER JOIN @tblStockChaos so 
      ON stPres.DonaldID = so.DonaldID
  ) a3 
    ON a2.StockChaosID = a3.StockChaosID
) a4
LEFT OUTER JOIN @tblDuckPez pp
  ON a4.PezID = pp.Pezid
LEFT OUTER JOIN @tblCheeseburger p 
  ON a4.CheeseburgerID = p.CheeseburgerID
LEFT OUTER JOIN @tblChaosCheeseburgerShipped ops
  ON a4.ChaosID = ops.ChaosID
LEFT OUTER JOIN @vwDonald pres
  ON a4.DonaldID = pres.DonaldID
LEFT OUTER JOIN @tblDuck tblDuck
  ON pp.DuckID = tblDuck.DuckID
LEFT OUTER JOIN @tblWolveLocation fl
  ON a4.WolveLocationID = fl.WolveLocationID

The only difference in my execution plan is that a couple fields had an additional step of compute scalar and that the final set of left outer joins were not in the exact same order as the original.