sql - T-SQL - 左外连接 - where 子句与 on 子句中的过滤器

Question

我试图比较两个表以在每个表中查找不在另一个表中的行。表 1 有一个 groupby 列，用于在表一中创建 2 组数据。

groupby     number
----------- -----------
1           1
1           2
2           1
2           2
2           4

表 2 只有一列。

number
-----------
1
3
4

因此，表 1 在组 2 中具有值 1、2、4，而表 2 具有值 1、3、4。

加入第 2 组时，我希望得到以下结果:

`Table 1 LEFT OUTER Join Table 2`
T1_Groupby  T1_Number   T2_Number
----------- ----------- -----------
2           2           NULL

`Table 2 LEFT OUTER Join Table 1`
T1_Groupby  T1_Number   T2_Number
----------- ----------- -----------
NULL        NULL        3

我可以让它工作的唯一方法是如果我为第一次加入放置一个 where 子句:

PRINT 'Table 1 LEFT OUTER Join Table 2, with WHERE clause'
select  table1.groupby as [T1_Groupby],
        table1.number as [T1_Number],
        table2.number as [T2_Number]
from    table1
        LEFT OUTER join table2
        --******************************
        on table1.number = table2.number
        --******************************
WHERE   table1.groupby = 2
    AND table2.number IS NULL

和第二个 ON 中的过滤器:

PRINT 'Table 2 LEFT OUTER Join Table 1, with ON clause'
select  table1.groupby as [T1_Groupby],
        table1.number as [T1_Number],
        table2.number as [T2_Number]
from    table2
        LEFT OUTER join table1
            --******************************
            on table2.number = table1.number
            AND table1.groupby = 2
            --******************************
WHERE   table1.number IS NULL

谁能想出一种不在 on 子句中而是在 where 子句中使用过滤器的方法？

上下文是我在数据库中有一个暂存区，我想识别新记录和已删除的记录。 groupby 字段相当于提取的 batchid，我将临时表中的最新提取与昨天存储在分区表中的批次进行比较，该表也包含所有先前提取的批次。创建表 1 和 2 的代码:

create table table1 (number int, groupby int)
create table table2 (number int)
insert into table1 (number, groupby) values (1, 1)
insert into table1 (number, groupby) values (2, 1)
insert into table1 (number, groupby) values (1, 2)
insert into table2 (number) values (1)
insert into table1 (number, groupby) values (2, 2)
insert into table2 (number) values (3)  
insert into table1 (number, groupby) values (4, 2)  
insert into table2 (number) values (4)  

编辑:

更多的上下文 - 根据我放置过滤器的位置，我会得到不同的结果。如上所述，where 子句在一种状态下给出了正确的结果，而在另一种状态下给出了 ON。我正在寻找一种一致的方式来做到这一点。

在哪里 -

select  table1.groupby as [T1_Groupby],
        table1.number as [T1_Number],
        table2.number as [T2_Number]
from    table1
        LEFT OUTER join table2
            --******************************
            on table1.number = table2.number
            --******************************
WHERE   table1.groupby = 2 
    AND table2.number IS NULL

结果:

T1_Groupby  T1_Number   T2_Number
----------- ----------- -----------
2           2           NULL

在 -

select  table1.groupby as [T1_Groupby],
        table1.number as [T1_Number],
        table2.number as [T2_Number]
from    table1
        LEFT OUTER join table2
            --******************************
            on table1.number = table2.number
            AND table1.groupby = 2
            --******************************
WHERE   table2.number IS NULL

结果:

T1_Groupby  T1_Number   T2_Number
----------- ----------- -----------
1           1           NULL
2           2           NULL
1           2           NULL

哪里(这次是表2)——

select  table1.groupby as [T1_Groupby],
        table1.number as [T1_Number],
        table2.number as [T2_Number]
from    table2
        LEFT OUTER join table1
            --******************************
            on table2.number = table1.number
            AND table1.groupby = 2
            --******************************
WHERE   table1.number IS NULL

结果:

T1_Groupby  T1_Number   T2_Number
----------- ----------- -----------
NULL        NULL        3

在 -

select  table1.groupby as [T1_Groupby],
        table1.number as [T1_Number],
        table2.number as [T2_Number]
from    table2
        LEFT OUTER join table1
            --******************************
            on table2.number = table1.number
            --******************************
WHERE   table1.number IS NULL
    AND table1.groupby = 2

结果:

T1_Groupby  T1_Number   T2_Number
----------- ----------- -----------
(0) rows returned

Answer 1

如果您在 WHERE 子句中过滤左外连接表，那么您实际上是在创建内连接

另请参阅此维基页面:WHERE conditions on a LEFT JOIN