34,591
社区成员
发帖
与我相关
我的任务
分享关于 not in的疑问
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 3
select * from t1 where col not in (select * from tb)
这个为什么会没有值啊
t1表中的 col = 3 这条记录明明符合要求的啊
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 3
select * from t1 where col not in (select * from tb)
这个为什么会没有值啊
t1表中的 col = 3 这条记录明明符合要求的啊
...全文
请发表友善的回复…
发表回复
love灵灵 2012-08-10
- 打赏
- 举报
真是学习了,谢谢
jamk 2010-08-27
- 打赏
- 举报
受教了~！
hokor 2010-07-09
- 打赏
- 举报
不看不知道。。。
rmljoe 2010-05-24
- 打赏
- 举报
学习了
使我做你和平之子 2010-05-24
- 打赏
- 举报
null确实是一个很狗屎的东西，比如count(某一列）不包括null值，但count(*)却有包括，c.j.date的说法确实有一定的道理，问题是在实践中有时用null确实比较方便
feilniu 2010-05-23
- 打赏
- 举报
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处理。
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处理。
feilniu 2010-05-23
- 打赏
- 举报
关于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。
暂时就想到这些。
理论上的争论且不管。但在实践中，一定要知道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。
暂时就想到这些。
gw6328 2010-05-23
- 打赏
- 举报
学习.
wangxianshou 2010-05-23
- 打赏
- 举报
[Quote=引用 4 楼 feilniu 的回复:]
提示：
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与别的值进行+-*/等运算操作后，结果是NU……
[/Quote]
借楼主宝地，也学习下
提示：
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与别的值进行+-*/等运算操作后，结果是NU……
[/Quote]
借楼主宝地，也学习下
永生天地 2010-05-23
- 打赏
- 举报
是null的影响
qifeifei 2010-05-23
- 打赏
- 举报
提示：
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处理
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处理
feilniu 2010-05-23
- 打赏
- 举报
提示：
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处理。
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处理。
FlySQL 2010-05-23
- 打赏
- 举报
顶1楼
feilniu 2010-05-23
- 打赏
- 举报
select * from t1 where col not in (select * from tb where id is not null)
这样是可以出结果的。
这样是可以出结果的。
feilniu 2010-05-23
- 打赏
- 举报
当NOT IN后面括号中的值列表中包含NULL时，结果会出人意料。
三值逻辑(3VL, Three-valued Logic)绝对是SQL修炼中的一个紧要关卡，值得特别注意，闭关静修。待冲破这一关卡之后，SQL中的NULL与NOT NULL将别无二致。
三值逻辑(3VL, Three-valued Logic)绝对是SQL修炼中的一个紧要关卡，值得特别注意，闭关静修。待冲破这一关卡之后，SQL中的NULL与NOT NULL将别无二致。
