技术性散分：当 not in 遇到 null

Question

上午调一段代码死活调不过，耽误了一上午的时间，终于知道是哪儿错了。如果早点来CSDN上问下或许可以省点时间~_~
先说一个简单的情况：
select 1 where 1 in (1,null)
select 1 where 1 not in (2,null)按我原来的理解，两条语句都会返回1，但第2句话实际返回的是空集(这儿我非常不理解，虽然sql server就是这么规定的~_~ )
更复杂的情况：标记b处的句子不能满足我的原意(返回空集而非ID为1的记录)，必须按c处的写法才可以declare @t1 table(id int)
declare @t2 table(id int)insert into @t1 values (1)
insert into @t1 values (2)
insert into @t1 values (3)insert into @t2 values (2)
insert into @t2 values (3)
insert into @t2 values (null) b: select * from @t1 where id not in (select distinct id from @t2)
select * from @t1 where not id in (select distinct id from @t2)c: select * from @t1 where id not in (select distinct id from @t2 where id is not null)
select * from @t1 where not id in (select distinct id from @t2 where id is not null)

Answer 1

null是指值未知不等于空白或者0的

Answer 2

将null值与任何其它数值相比均返回未知，这是因为每个null值均为未知。

Answer 3

楼上3连了 如果还想说我就帮你顶下先~ 也是帮我自己顶哈不过只有 not in才有这个问题，in 就不会：)

Answer 4

好像有个结论：
 select ... from ... where id not in (...)只要(...)中包含null，必然返回空集合

Answer 5

in() 是OR關系
not in() 是AND關系

Answer 6

我也经常碰到过
不过我加了where id is not null就解决了
select * from @t1 where id not in (select distinct id from @t2 where id is not null)
and id is not null

Answer 7

null 不代表一个值，逻辑上不太好理解，我理解为null的用法比较特殊，为了避免这种错误，null的判断应该使用sql server自带的 is not null来判断

Answer 8

应该等价于
select 1 from (1=null) or (1=2)
select 1 from (1<>null) and (1<>2)显然：
true OR null =true
true and null=null当然结果就是楼主看到的了

Answer 9

不过还真的很容易出错，这就是null比较贱的地方，呵呵

Answer 10

字段允许null,查询时肯定要过滤掉它!
接分!~

Answer 11

養成對允許null的字段加isnull(col,0)/isnull(col,'') 是個不錯的習慣:)

Answer 12

好象我都用is not null 就没怎么注意了~

Answer 13

is not null是可以解決問題的...

Answer 14

养成习惯：对有可能产生NULL的位置或字段或要求不为空的条件
加IS NOT NULL OR <>''

Answer 15

null是未知，not in null 也就是不在未知中这样理解确实返回null

Answer 16

set ansi_nulls off
select 1 where 1 not in (2,null)
set ansi_nulls on默认情况下，ANSI_NULLS选项是ON的，此时，根据SQL-92标准的规定：
与NULL的比较均返回为Unknown，不能确定。
如果设置ANSI_NULLS为OFF，则强制不使用SQL-92标准，此时与NULL的比较和与其他值的比较是一样的。

Answer 17

null 用的是 is 或 not is
值 用的是 = 或 <>in 或 not in谓词的作用跟后者差不多，只是多了集合的枚举罢了，自然对null用in或not in谓词操作会导致失败并不是什么新鲜玩意把？

Answer 18

yangyangxie(sally) ( ) 信誉：100    Blog  2006-12-6 15:06:30  得分: 0  
 
 
   
我也经常碰到过
不过我加了where id is not null就解决了
select * from @t1 where id not in (select distinct id from @t2 where id is not null)
and id is not null
正解
  
 

Answer 19

select 1 from dual where  (1 not in (null,2))
无
select 1 from dual where not (1 in (null,2))
无
select 1 from dual where not (1 in (null,1))
无
select 1 from dual where (1 in (null,1))
有

Answer 20

lzhs(快乐至上)的说法，我喜欢

Answer 21

支持：lzhs(快乐至上)的说法
 本身Transact-SQL 支持在与空值进行比较，允许比较运算符返回 TRUE 或 FALSE。但必须通过设置 ANSI_NULLS OFF 将此选项激活。
当 SET ANSI_NULLS 为 ON 时，如果比较中有一个或多个表达式为 NULL，则既不输出 TRUE 也不输出 FALSE，而是输出 UNKNOWN。这是因为未知值不能与其他任何值进行逻辑比较。(参考SQLSERVER帮助文档)
SET ANSI_NULLS off
select 1 where 1 in (1,null)
select 1 where 1 not in (2,null)
这时结果都会返回1

Answer 22

可以考虑用nullif 进行判断

Answer 23

只有 not in才有这个问题，in 就不会in() 是OR關系 not in() 是AND關系NOT IN 取反

Answer 24

我的数据库内的所有字段不允许为NULL，因为这是麻烦的起因。

Answer 25

如果有NULL值,不管NULL與什麼進行什麼運算,結果都是NULL.為了計算不至於產生錯誤,最好不要字段的值為NULL..如果有NULL,在使用時一定要用ISNULL(XX,'')將其換成空串或其它來處理.這樣不容易出錯