问题描述
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 .
进一步的解释:
您的评论
需要详细说明
正如我在查询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条件之间的差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!