sql - 谁能理解为什么这样做或如何在这部分SQL中正常工作？

我已经更改了表名，但是在我正在优化的SP中找到了这个FROM语句，我想知道这是如何产生的，为什么您会做这样的事情，以及为什么它仍然有效？

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

我已经清理到

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

我认为应该是等效的，我需要一些测试数据。我只是完全不了解第一个版本是如何工作的。

最佳答案

SQL中的联接表达式是构造性的，就像算术表达式一样。

也就是说，您可以执行以下操作:

A JOIN B ON <expr-AB>

但是您也可以用另一个join表达式代替B:

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

如果自然优先级顺序给出相同的结果，则可能是多余的:

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

因此，您显示的庞大笨拙查询是合法语法，但肯定可以更清楚。

我将查询视为这样的层次结构:

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

我会这样重写查询:

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
  )

我不能保证所有条件都完美。但这就是主意。

关于sql - 谁能理解为什么这样做或如何在这部分SQL中正常工作？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3631443/