关于 not in的疑问

Question

if object_id('tb') is not null
drop table tb
create table tb
(
id int
)
insert tb select 1
union all
select null
union all
select 2
if object_id('t1') is not null
drop table t1
create table t1 
(
col int
)
insert t1 select 1
union all
select 3select * from t1 where col not in (select * from tb)这个为什么会没有值啊 t1表中的  col = 3 这条记录明明符合要求的啊

Answer 1

当NOT IN后面括号中的值列表中包含NULL时，结果会出人意料。
三值逻辑(3VL, Three-valued Logic)绝对是SQL修炼中的一个紧要关卡，值得特别注意，闭关静修。待冲破这一关卡之后，SQL中的NULL与NOT NULL将别无二致。

Answer 2

select * from t1 where col not in (select * from tb where id is not null)这样是可以出结果的。

Answer 3

提示：1. 在逻辑上，
column IN (v1,...,vN) 等价于 column = v1 OR ... OR column = vN
column NOT IN (v1,...,vN) 等价于 column <> v1 AND ... AND column <> vN
（即使是在考虑NULL的三值逻辑情况下）2. NULL与别的值进行+-*/等运算操作后，结果是NULL。NULL与别的值进行=、>、<等比较操作后，结果是Unknown。
NOT Unknown         --> Unknown
Unknown AND Unknown --> Unknown
Unknown OR  TRUE    --> TRUE
Unknown AND TRUE    --> Unknown
Unknown OR  FALSE   --> Unknown
Unknown AND FALSE   --> FALSE
具体可查三值逻辑的真值表。3. 在where/on/having条件中，Unknown当作FLASE处理。

Answer 4

提示：1. 在逻辑上，
column IN (v1,...,vN) 等价于 column = v1 OR ... OR column = vN
column NOT IN (v1,...,vN) 等价于 column <> v1 AND ... AND column <> vN
（即使是在考虑NULL的三值逻辑情况下）2. NULL与别的值进行+-*/等运算操作后，结果是NULL。NULL与别的值进行=、>、<等比较操作后，结果是Unknown。
NOT Unknown --> Unknown
Unknown AND Unknown --> Unknown
Unknown OR TRUE --> TRUE
Unknown AND TRUE --> Unknown
Unknown OR FALSE --> Unknown
Unknown AND FALSE --> FALSE
具体可查三值逻辑的真值表。3. 在where/on/having条件中，Unknown当作FLASE处理

Answer 5

关于SQL是否应该允许NULL，在数据库领域已经近乎一个信仰式的争论。E.F.Codd认为NULL有存在的必要，但他的好友C.J.Date认为NULL完全可以取消。最终结果是，SQL标准支持NULL。理论上的争论且不管。但在实践中，一定要知道NULL的三值逻辑会带来很多困扰的问题。首先，除非必要，尽量使表中字段为NOT NULL。其次，在使用NULL时，一定要搞清楚三值逻辑和数据库引擎对NULL的处理：
（SQLServer有一个选项SET ANSI_DEFAULTS，默认为ON，即与SQL标准一致。设为OFF的效果详见文档。）1. NULL与别的值进行+-*/等运算操作后，结果是NULL（一个值，类似0,1.0,'abc'）。NULL与别的值进行=、>、<等比较操作后，结果是Unknown（比较结果，类似True,False）。2. 在where/on/having和if/case when中，Unknown当作False来处理。
where column = value：表中column为NULL的行永远不会返回，即使value是NULL；
case value when NULL then XXX when ... end：XXX永远不会执行，即使value是NULL；
if <Unknown> XXX else YYY end/case when <Unknown> then XXX else YYY end：这两种情况下，YYY会执行。3. 包含外键约束和Check约束的字段允许NULL。4. 包含唯一约束（unique index）的字段只允许一个NULL的行，再插入或更新该字段为NULL的行会报字段重复的错误。5. 聚集函数（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行。6. declare的变量，在未赋值之前为NULL。7. 与NULL处理相关的函数：ISNULL/COALESCE/NULLIF。
暂时就想到这些。

Answer 6

NULL表示一个不确定或无意义的值。LZ的这个问题可以这样理解：1 in (1,2,3,NULL) = True：可以确定1在列表中。
0 in (1,2,3,NULL) = Unknown：NULL是一个不确定的值，所以不确定0是不是在列表中。
1 not in (1,2,3,NULL) = False：in为True，not in则为False。
0 not in (1,2,3,NULL) = Unknown：in不确定，not in也不确定。结果是，只要not in后面的列表中包含NULL，结果总是当作False处理。

Answer 7

null确实是一个很狗屎的东西，比如count(某一列）不包括null值，但count(*)却有包括，c.j.date的说法确实有一定的道理，问题是在实践中有时用null确实比较方便