主页 / dba / 问题 / 125422
Accepted
boot4life
Asked: 2016-01-07 12:14:54 +0800 CST

ON 子句的位置实际上是什么意思？

  • 772

正常JOIN ... ON ...语法是众所周知的。但也可以将子句与其对应的ON子句分开放置。JOIN这在实践中很少见，在教程中找不到，我还没有找到任何网络资源，甚至提到这是可能的。

这是一个可以玩的脚本：

SELECT *
INTO #widgets1
FROM (VALUES (1), (2), (3)) x(WidgetID)


SELECT *
INTO #widgets2
FROM (VALUES (1, 'SomeValue1'), (2, 'SomeValue2'), (3, 'SomeValue3')) x(WidgetID, SomeValue)

SELECT *
INTO #widgetProperties
FROM (VALUES
    (1, 'a'), (1, 'b'),
    (2, 'a'), (2, 'b'))
x(WidgetID, PropertyName)


--q1
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 ON w2.WidgetID = w1.WidgetID
LEFT JOIN #widgetProperties wp ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
ORDER BY w1.WidgetID


--q2
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 --no ON clause here
JOIN #widgetProperties wp
 ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
 ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID


--q3
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN (
    #widgets2 w2 --no SELECT or FROM here
    JOIN #widgetProperties wp
    ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b')
ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID

q1 看起来很正常。q2 和 q3 有这些不寻常的ON从句定位。

这个脚本不一定有多大意义。我很难设计出一个有意义的场景。

那么这些不寻常的语法模式是什么意思呢？这是如何定义的？我注意到并非这两个ON子句的所有位置和顺序都是允许的。管理这个的规则是什么？

编写这样的查询也是一个好主意吗？

sql-server t-sql

2 个回答

  1. 它确定连接中涉及的逻辑表。

    用一个简单的例子

    SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2
         ON w2.WidgetID = w1.WidgetID
       JOIN #widgetProperties wp
         ON w2.WidgetID = wp.WidgetID
            AND wp.PropertyName = 'b'
ORDER  BY w1.WidgetID

    #widgets1左外连接到#widgets2- 结果形成一个内连接到的虚拟表#widgetProperties。谓词w2.WidgetID = wp.WidgetID意味着来自初始外连接的任何空扩展行都被过滤掉，有效地使所有连接成为内连接。

    这与 q2 不同...

    SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2 --no ON clause here
                 JOIN #widgetProperties wp
                   ON w2.WidgetID = wp.WidgetID
                      AND wp.PropertyName = 'b'
         ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

    #widgets2是内部连接到#widgetProperties。该连接产生的虚拟表是左外连接中的右侧表#widgets1

    通过使用派生表或公用表表达式可以实现相同的结果...

    WITH VT2
     AS (SELECT w2.WidgetID,
                w2.SomeValue,
                wp.PropertyName
         FROM   #widgets2 w2 
                JOIN #widgetProperties wp
                  ON w2.WidgetID = wp.WidgetID
                     AND wp.PropertyName = 'b')
SELECT w1.WidgetID,
       VT2.SomeValue,
       VT2.PropertyName
FROM   #widgets1 w1
       LEFT JOIN VT2
         ON VT2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

    ...或者，您可以重新排序虚拟表并使用 aRIGHT JOIN代替。

    SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets2 w2
       INNER JOIN #widgetProperties wp
               ON w2.WidgetID = wp.WidgetID
                  AND wp.PropertyName = 'b'
       RIGHT JOIN #widgets1 w1
               ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

    Itzik Ben Gan 在这里介绍了这一点

    ... JOIN 条件必须遵循与表顺序的交叉关系。也就是说，如果您按顺序指定表 T1、T2、T3 和 T4，并且 JOIN 条件匹配 T1 与 T2、T2 与 T3、T3 与 T4，则必须按照与表顺序相反的顺序指定 JOIN 条件， 像这样：

    FROM   T1
       <join_type> T2 T2
                  <join_type> T3 T3
                             <join_type> T4
                               ON T4.key = T3.key
                    ON T3.key = T2.key
         ON T2.key = T1.key

    要以不同的方式看待这种连接技术，给定的 JOIN 条件只能引用其正上方的表名或之前的 JOIN 条件已经引用和解析的表名。

    但这篇文章有许多不准确之处，请参阅Lubor Kollar 的后续信函。

    • 35
  2. Best Answer

    如果您查看FROM子句语法图，您会发现子句只有一个位置ON：

    <joined_table> ::= 
{
    <table_source> <join_type> <table_source> ON <search_condition> 
    ...
}

    你会感到困惑的是简单的递归，因为上面<table_source>的<joined_table> 可以是另一个<joined_table>：

    [ FROM { <table_source> } [ ,...n ] ] 
<table_source> ::= 
{
    table_or_view_name ... 
    ...
    | <joined_table> 
    ...
}

    为避免混淆，您应该在不明显的情况下（例如您的示例）使用括号将<table_sources>; 它们对于查询解析器不是必需的，但对人类有用。

    • 33

相关问题