T-SQL - 左外连接 - where 子句与 on 子句中的过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2930033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL - Left Outer Joins - Filters in the where clause versus the on clause
提问by Greg Potter
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.
我试图比较两个表以在每个表中查找不在另一个表中的行。表 1 有一个 groupby 列，用于在表一中创建 2 组数据。
groupby number
----------- -----------
1 1
1 2
2 1
2 2
2 4
Table 2 has only one column.
表 2 只有一列。
number
-----------
1
3
4
So Table 1 has the values 1,2,4 in group 2 and Table 2 has the values 1,3,4.
因此，表 1 在组 2 中具有值 1、2、4，而表 2 具有值 1、3、4。
I expect the following result when joining for Group 2:
加入第 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
The only way I can get this to work is if I put a where clause for the first join:
我可以让它工作的唯一方法是如果我为第一次加入放置一个 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
and a filter in the ON for the second:
和第二个 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
Can anyone come up with a way of not using the filter in the on clause but in the where clause?
谁能想出一种不在 on 子句中而是在 where 子句中使用过滤器的方法？
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:
上下文是我在数据库中有一个暂存区，我想识别新记录和已删除的记录。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)
EDIT:
编辑：
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.
更多的上下文 - 根据我放置过滤器的位置，我会得到不同的结果。如上所述，where 子句在一种状态下给出了正确的结果，而在另一种状态下给出了 ON。我正在寻找一种一致的方式来做到这一点。
Where -
在哪里 -
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
Result:
结果：
T1_Groupby T1_Number T2_Number
----------- ----------- -----------
2 2 NULL
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
AND table1.groupby = 2
--******************************
WHERE table2.number IS NULL
Result:
结果：
T1_Groupby T1_Number T2_Number
----------- ----------- -----------
1 1 NULL
2 2 NULL
1 2 NULL
Where (table 2 this time) -
哪里（这次是表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
Result:
结果：
T1_Groupby T1_Number T2_Number
----------- ----------- -----------
NULL NULL 3
On -
在 -
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
Result:
结果：
T1_Groupby T1_Number T2_Number
----------- ----------- -----------
(0) rows returned
回答by SQLMenace
If you filter the left outer joined table in the WHERE clause then you are in effect creating an inner join
如果您在 WHERE 子句中过滤左外连接表，那么您实际上是在创建内连接
See also this wiki page: WHERE conditions on a LEFT JOIN
另请参阅此 wiki 页面：LEFT JOIN 上的 WHERE 条件
回答by KM.
with LEFT OUTER JOINS, you must filter in the ON clause or use this:
使用 LEFT OUTER JOINS，您必须在 ON 子句中进行过滤或使用：
WHERE
(LeftJoinTable.ID IS NULL OR LeftJoinTable.Col1=YourFilter)
if you just filter in the WHERE:
如果你只是在 WHERE 中过滤：
WHERE
LeftJoinTable.Col1=YourFilter
you will discard the parent joining row whenever there isn't a LeftJoinTable.ID (making the join an INNER JOIN).
只要没有 LeftJoinTable.ID（使连接成为 INNER JOIN），您就会丢弃父连接行。
By placing the filter in the ON, you cause the elimination of the LEFT JOIN row but not the elimination of the parent joining row, that is just how it works.
通过将过滤器置于 ON 中，您会消除 LEFT JOIN 行，但不会消除父连接行，这就是它的工作原理。
EDITbase don OP's comment
the only way to filter a a LEFT OUTER JOIN table is in the ON clause, unless you want to use an OR like I show in the first code example above. There is nothing wrong filtering a LEFT OUTER JOIN in the ON clause, this is how you do it.
EDITbase don OP's comment
过滤 LEFT OUTER JOIN 表的唯一方法是在 ON 子句中，除非您想使用我在上面第一个代码示例中显示的 OR。在 ON 子句中过滤 LEFT OUTER JOIN 没有任何错误，这就是您的做法。
回答by mdma
As the query is written, it makes sense to put the join in the ON clause, since you specifically only want to join on values in group '2' from table 1.
在编写查询时，将联接放在 ON 子句中是有意义的，因为您特别只想联接表 1 中组“2”中的值。
The alternative is to prefilter table1 to the group you are interested in, like this
另一种方法是将 table1 预过滤到您感兴趣的组，如下所示
select t1Group.groupby,
t1Group.number as [T1_Number],
table2.number as [T2_Number]
from table2
LEFT OUTER join (SELECT * FROM table1 WHERE groupby=2) t1Group
on table2.number = t1Group.number
WHERE t1Group.number IS NULL
回答by angel
SELECT dbo.table1.groupby as [T1_Groupby],
dbo.table1.number as [T1_Number],
t21.number as [t21_Number]
FROM dbo.table1
LEFT OUTER join dbo.table2 t21
ON dbo.table1.number = t21.number
LEFT OUTER join dbo.table2 t22
ON dbo.table1.groupby= t22.number
WHERE t21.number is null AND t22.number is null
回答by angel
select dbo.table1.groupby as [T1_Groupby],
dbo.table1.number as [T1_Number],
t22.number as [t22_Number]
from dbo.table1 right outer join
(select dbo.table1.groupby,
dbo.table2.number as number
from dbo.table1
right OUTER join dbo.table2
on dbo.table1.number = dbo.table2.number
where dbo.table1.number is null) t22
on dbo.table1.groupby = t22.number
where dbo.table1.groupby is null
回答by KMW
I have been struggling with this myself - and at the end of the day was to select data from table with Where Clause and put it into a temp table, and then use Left outer join on the Temp Table.
我自己一直在努力解决这个问题 - 最后是从带有 Where 子句的表中选择数据并将其放入临时表中，然后在临时表上使用左外连接。
SELECT table1.GroupBy, table1.number INTO #Temp FROM table1 WHere GroupBy = 2
SELECT table2.Groupby, #temp.number From table2 LEFT OUTER JOIN #temp on table2.Groupby = #temp.Groupby
