T-SQL-左外部联接-在where子句和on子句中进行过滤 [英] T-SQL - Left Outer Joins - Filters in the where clause versus the on clause

问题描述

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

I am trying to compare two tables to find rows in each table that is not in the other. Table 1 has a groupby column to create 2 sets of data within table one.

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.

So Table 1 has the values 1,2,4 in group 2 and Table 2 has the values 1,3,4.

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

I expect the following result when joining for Group 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子句:

The only way I can get this to work is if I put a where clause for the first join:

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]中打开一个过滤器:

and a filter in the ON for the second:

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子句中使用过滤器的方法吗?

Can anyone come up with a way of not using the filter in the on clause but in the where clause?

这的上下文是我在数据库中有一个暂存区，并且我想标识新记录和已删除的记录. groupby字段等效于某个提取物的批次标识，我正在将临时表中的最新提取物与昨天存储在partededs表中的昨天的批次进行比较，该表也具有所有先前提取的批次.创建表1和2的代码:

The context of this is I have a staging area in a database and I want to identify new records and records that have been deleted. The groupby field is the equivalent of a batchid for an extract and I am comparing the latest extract in a temp table to a the batch from yesterday stored in a partioneds table, which also has all the previously extracted batches as well. Code to create table 1 and 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子句在一种状态下为我提供了正确的结果，而在另一种状态下为我提供了正确的结果.我正在寻找一种一致的方法.

A bit more context - depending on where I put the filter I different results. As stated above the where clause gives me the correct result in one state and the ON in the other. I am looking for a consistent way of doing this.

位置-

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

推荐答案

如果在WHERE子句中过滤左外部联接表，则实际上是在创建内部联接

If you filter the left outer joined table in the WHERE clause then you are in effect creating an inner join

另请参阅以下Wiki页面:左联接的WHERE条件

See also this wiki page: WHERE conditions on a LEFT JOIN

这篇关于T-SQL-左外部联接-在where子句和on子句中进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！