ON 与 WHERE 中的 SQL 外连接过滤条件

Question

为什么以下查询不同？我想要一个 LEFT OUTER 连接，但需要用条件过滤子项。我认为这些查询本质上是相同的（只是语法不同），但是如果我将条件放在 ON 与 WHERE 中，我会得到不同的结果：

-- Query 1: Filter in WHERE
SELECT  p.ID, p.Name, c.ID, c.Name, c.ParentID
FROM    @Parent p
  LEFT OUTER JOIN @Child c
    ON (p.ID = c.ParentID)
WHERE   c.ID IS NULL OR c.Name = 'T';

-- Query 2: Filter in ON
SELECT  p.ID, p.Name, c.ID, c.Name, c.ParentID
FROM    @Parent p
  LEFT OUTER JOIN @Child c
    ON (p.ID = c.ParentID AND c.Name = 'T');

我从 Query 开始2 但它在结果中显示了所有父项，而不是具有匹配子项的子集，因此我切换到查询 1。下面是一个示例：

DECLARE @Parent TABLE (
     ID           int           IDENTITY(1, 1) PRIMARY KEY
  ,  Name         nvarchar(40)  NOT NULL
);

DECLARE @Child TABLE (
     ID           int           IDENTITY(1, 1) PRIMARY KEY
  ,  Name         nvarchar(40)  NOT NULL
  ,  ParentID     int               NULL
);

-- Parents
INSERT  @Parent (Name)
VALUES  ('A'), ('B'), ('C'), ('D')
;

-- Children: permutations to parents.
-- NOTE: 'D' has no children
INSERT  @Child (Name, ParentID)
VALUES  ('T', 1)
    ,             ('U', 2)
    ,   ('V', 1), ('V', 2)
    ,                       ('W', 3)
    ,   ('X', 1),           ('X', 3)
    ,             ('Y', 2), ('Y', 3)
    ,   ('Z', 1), ('Z', 2), ('Z', 3)
;

-- Query 1: Filter in WHERE
SELECT  p.ID, p.Name, c.ID, c.Name, c.ParentID
FROM    @Parent p
  LEFT OUTER JOIN @Child c
    ON (p.ID = c.ParentID)
WHERE   c.ID IS NULL OR c.Name = 'T';

-- Query 2: Filter in ON
SELECT  p.ID, p.Name, c.ID, c.Name, c.ParentID
FROM    @Parent p
  LEFT OUTER JOIN @Child c
    ON (p.ID = c.ParentID AND c.Name = 'T');

查询 1：结果

ID	名称	ID	名称	ParentID
1	A	1	T	1
4	D	NULL	NULL	NULL

查询 2：结果

ID	名称	ID	名称	ParentID
1	A	1	T	1
2	B	NULL	NULL	NULL
3	C	NULL	NULL	NULL
4	D	NULL	NULL	NULL

我以为查询会返回相同的结果，但当它们没有返回时，我感到很惊讶。我更喜欢查询 2 的样式（并且我认为它更优化），但我认为查询会返回相同的结果。

（注意：带有数据的 SQL 示例是后来添加的，目的是为了澄清为什么这个问题不是另一个问题的重复，并使其符合当前的问题标准。示例结果使其更加重要更清楚的是，查询 1 返回具有 1 个或多个匹配子项的父项，而查询 2 返回所有父项，但仅返回匹配的子项。显然，我现在了解查询之间的区别。）

编辑/摘要：

这里提供了一些很好的答案。我很难选择将答案授予谁。我决定选择 mdma，因为这是第一个答案，也是最清晰的答案之一。根据提供的答案，这是我的总结：

可能的结果：

• A：没有孩子的父母
• B：有孩子的父母
• |-> B1：有孩子的父母，没有孩子符合过滤器
• \-> B2：有 1 个或多个与过滤器匹配的孩子的父母

查询结果：

• 查询 1 返回 (A, B2)
• 查询 2 返回 (A, B1, B2)

查询 2 始终返回父母，因为左侧加入。在查询 1 中，WHERE 子句在左连接之后执行，因此排除了其子级均不与过滤器匹配的父级（情况 B1）。

注意：B1情况下仅返回父信息，B2情况下仅返回与过滤器匹配的父/子信息。

HLGEM 提供了一个很好的链接（现已失效，因此使用 archive.org）：

Answer 1

是的，存在巨大差异。当您将过滤器放置在 LEFT JOIN 的 ON 子句中时，过滤器将在结果连接到外部表之前应用。当您在 WHERE 子句中应用过滤器时，它会在应用 LEFT JOIN 之后发生。

简而言之，第一个查询将排除存在子行但子行描述不等于过滤条件的行，而第二个查询将始终返回父行的行。

Answer 2

第一个查询将返回父级没有子级或某些子级符合过滤条件的情况。具体来说，父级有一个子级但不符合过滤条件的情况将被忽略。

第二个查询将为所有父母返回一行。如果过滤条件不匹配，则 c 的所有列都将返回 NULL。这就是为什么您在查询 2 中获得更多行的原因 - 具有与过滤条件不匹配的子级的父级将使用 NULL 子值输出，而在第一个查询中它们将被过滤掉。

Answer 3

将条件放在 where 子句中会将其转换为内部联接（除非您使用的是 where id 为 null 的内容，这会为您提供不在表中的记录）
有关更完整的解释，请参阅此：

http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN

Answer 4

对于此记录集：

parent

id
1

child

id    parent filter
1     1      OtherCondition
2     1      OtherCondition

，第一个查询将返回 0 记录，而第二个查询将返回 1 记录：

WITH    parent (id) AS
        (
        SELECT  1
        ),
        child (id, parent, condition) AS
        (
        SELECT  1, 1, 'OtherCondition'
        UNION ALL
        SELECT  2, 1, 'OtherCondition'
        )
SELECT  *
FROM    parent
LEFT JOIN
        child
ON      child.parent = parent.id   

/* The children are found, so no fake NULL records returned */

1   1   1   OtherCondition
1   2   1   OtherCondition

现在添加 WHERE 子句：

WITH    parent (id) AS
        (
        SELECT  1
        ),
        child (id, parent, condition) AS
        (
        SELECT  1, 1, 'OtherCondition'
        UNION ALL
        SELECT  2, 1, 'OtherCondition'
        )
SELECT  *
FROM    parent
LEFT JOIN
        child
ON      child.parent = parent.id       
WHERE   child.id IS NULL OR child.condition = 'FilterCondition'

WHERE 子句过滤上一步返回的记录，没有记录符合条件。

而这个：

WITH    parent (id) AS
        (
        SELECT  1
        ),
        child (id, parent, condition) AS
        (
        SELECT  1, 1, 'OtherCondition'
        UNION ALL
        SELECT  2, 1, 'OtherCondition'
        )
SELECT  *
FROM    parent
LEFT JOIN
        child
ON      child.parent = parent.id       
        AND child.condition = 'FilterCondition'

1   NULL    NULL    NULL

返回一条假记录。

Answer 5

我注意到一些差异可能会导致结果有所不同。在第一个查询中，您有 LEFT OUTER JOIN Child c ON (p.ID = c.ParentID)
然后在第二个查询中您有 LEFT OUTER JOIN Child c
ON (p.ID = c.ParentID AND c.Description = 'FilterCondition') 这使得第二个查询返回所有有孩子的父母满足您的条件，而第一个条件也将返回没有孩子的父母。还要查看 join 条件和 where 条件的优先级。

Answer 6

仅具有 description != 'FilterCondition' 子级的父级不会出现在查询 1 中，因为 WHERE 子句是在行连接后计算的。

Answer 7

第一个查询返回的行数较少，因为它仅返回没有子项或具有与筛选条件匹配的子项的行。

WHERE 子句排除其余部分（那些确实有子项但不匹配过滤条件的子句。）

第二个查询显示上述所有三个条件。