查询中JOIN和WHERE中的IS NULL条件之间的差异 [英] Difference between IS NULL criteria in JOIN and WHERE in a query

问题描述

create table #t1 (id int)
create table #t2 (id int)

insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)

insert into #t2 values (1)
insert into #t2 values (2) 

我运行了以下查询，并得到2个不同的输出.

I ran the below queries, and I get 2 different outputs.

第二是所需的输出.

我无法理解query1给出的输出原因.

I cannot understand the reason for output given by query1.

请协助我了解结果.

-- Query1

select * from #t1 a left join #t2 b on a.id = b.id and  b.id is null

-- Query2

select * from #t1 a left join #t2 b on a.id = b.id where b.id is null

推荐答案

在查询1中，联接条件中有b.id is null，这是因为在A上为LEFT JOIN会返回A的所有行，而与JOIN条件无关，因此为表A返回3行.

In query 1 we have b.id is null in join criteria, which because being LEFT JOIN on A returns all rows of A irrespective of JOIN criteria and therefore returns 3 rows for table A.

在查询2中，A上的第一个LEFT JOIN返回3行，然后在这3行上应用where b.id is null，从而留下第三行，并为id的第三行仅生成 1行 = 3.

In query 2 first LEFT JOIN on A returns 3 rows and then where b.id is null is applied on these 3 rows which leaves the third row and results in only 1 rows for the third row for id=3 .

进一步的解释:

您的评论

尽管在Query1中b.id为何为null?

Though in Query1 how come b.id is null ??

需要详细说明

正如我在查询1中所说的，对于A的每一行，在左联接中都返回了一行，而不考虑JOIN的条件

as I said in Query 1 for each row of A there is a row returned in Left join irrespective of criteria of JOIN

所以您的加入条件实际上是

so your join criteria is actually

a.id = b.id，而b.id为空

两个不能同时为真的标准 And ，好像 b.id为null 为真，则匹配a.id = null，基本上为null

a logical And between two criteria which cannot be true simultaneously as if b.id is null is true then a.id=null is being matched which is basically null

输出: http://sqlfiddle.com/#!3/c20ba/1

就像

id  | id
________
 1  |  null
 2  |  null
 3  |  null

要注意的另一点:请注意，在SQL中，id=NULL被评估为NULL.还要注意，在执行sql查询中的AND AND之类的逻辑操作时，NULL的行为非常特殊.有关此行为，请参见 msdn文档

Another point to note: Do note that in SQL id=NULL is evaluated as NULL. Also note that when doing logical operations like AND OR in sql query, NULL behaves quite peculiarly. See the msdn documentation on this behavior

null and true = null
null and false=false
null and null =null
null or null =null
null or true= true
null or false=null

这篇关于查询中JOIN和WHERE中的IS NULL条件之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！