在联接条件中使用筛选器的SQL左联接与在WHERE子句中使用筛选器的SQL左联接

与何处条件有很大区别 b.status is null or b.status in (10, 100) 当b.status为1且b.id=a.id时

在第一个查询中,您仍然可以从表A中得到相应的B部分为空的行,因为条件是不完全满足。 在第二个查询中,您将获得A和B表的联接中的行,这些表将在WHERE子句中丢失。

让我举个例子:

SELECT * INTO #A FROM (VALUES 
(1),(2),(3),(4)) T(id)

SELECT * INTO #B FROM (VALUES
(1,NULL),
(2,1),
(3,10)) T(id,status)

select *
from #A as a
left join #B b on a.id = b.id and b.status in (10, 100)

select *
from #A as a
left join #B b on a.id = b.id
where b.status is null or b.status in (10, 100)

结果

id          id          status
----------- ----------- -----------
1           NULL        NULL
2           NULL        NULL
3           3           10
4           NULL        NULL

id          id          status
----------- ----------- -----------
1           1           NULL
3           3           10
4           NULL        NULL

最后回应:

1. 如果状态不在(10100),则左联接应用空值
2. 如果状态为空,则左联接也应用空,谓词无效。

从逻辑上讲,您的第二个查询是 几乎 一样:

SELECT *
FROM TableA as a
LEFT JOIN TableB b
    ON a.id = b.id
WHERE b.status IN (10, 100);  -- b.status is null has been removed

所以问题归结为标准问题,过滤 ON 子句与筛选 WHERE 条款。在前一种情况下,将保留来自联接左侧的所有记录,即使 论 逻辑应该失败。在后一种情况下(即第二个查询的情况),匹配未能通过 status 条件将被删除,而不会显示在结果集中。

我说 几乎 同样,因为 b.status IS NULL 你的检查会让记录保存下来 做 在联接条件中匹配,但恰好有一个 null 价值为 地位 . 但除此之外,您的问题实际上只是 论 从句与在 在哪里? 条款。

在正常情况下, LEFT JOIN 或 LEFT OUTER JOIN 给出左表中的所有行以及两个表中匹配的行。当左表中的行在右表中没有匹配的行时,关联的结果集行包含来自右表的所有选择列表列的空值。

当我们添加一个具有左外部联接的WHERE子句时,它的行为就像一个内部联接,在ON子句之后应用过滤器,只显示那些具有

B.status is null or 10 or 100

既然是 LEFT JOIN ,不匹配 ON 条件意志 只需为右表中的列生成空值 .

另一方面,不匹配 WHERE 条款遗嘱 无论连接类型如何,都完全消除行 . 考虑这个例子:

CREATE TABLE #TableA(id INT);
INSERT INTO  #TableA VALUES
    (1),
    (2);
CREATE TABLE #TableB(id INT, status INT);
INSERT INTO  #TableB VALUES
    (1, 10),
    (2, -1);

SELECT *
FROM #TableA AS A
LEFT JOIN #TableB B ON A.id = B.id AND B.status IN (10)
/*
    a.id | b.id | status
    1    | 1    | 10
    2    | NULL | NULL
*/    

SELECT *
FROM #TableA AS A
LEFT JOIN #TableB B ON A.id = B.id
-- WHERE B.status IS NULL OR B.status IN (10)
/*
    a.id | b.id | status
    1    | 1    | 10
    2    | 2    | -1
*/

注意,我已经在第二个查询中注释掉了WHERE子句(结果已经不同了)。一旦添加,它也将删除第二行。

select * from TableA as a left join TableB b on a.id = b.id and b.status in (10, 100)

条件语句,并在联接发生之前进行计算。

select * from TableA as a left join TableB b on a.id = b.id
  where b.status is null or b.status in (10, 100)

筛选发生在表联接之后。

所以这就是为什么你会得到不同的输出。

在第一个查询中,将获取左表的所有行

但是

在第二个Where中,当您根据Where-Cluse进行筛选时,它只会给出那些完全填充Where条件的记录。