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],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,我将临时表中的最新提取与昨天存储在partioneds表中的批处理进行比较,该表还包含所有先前提取的批处理.创建表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,2)
insert into table2 (number) values (1)
insert into table1 (number,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],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],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],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],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