select * from a where a.id not in (1, 2, 3) 选出a.id为除1,2,3外的其他数据,包括null。 但 select * from a where a.id in (1, 2, 3) 只选出a.id为1,2,3的,null不会选中。 =1、 <>1 都不会选中null的。
demo@XIAOXIAO>select 'True' from dual where 1 in (null, 1, 2 ) ;'TRU ---- Truedemo@XIAOXIAO> demo@XIAOXIAO>select 'True' from dual where 1 in (null, 2 ) ;未选定行demo@XIAOXIAO> demo@XIAOXIAO>select 'True' from dual where null in (null, 1, 3 ) ;未选定行demo@XIAOXIAO> demo@XIAOXIAO>select 'True' from dual where null in (1, 3 ) ;未选定行demo@XIAOXIAO> demo@XIAOXIAO>select 'True' from dual where 1 not in (null, 1, 2 ) ;未选定行demo@XIAOXIAO> demo@XIAOXIAO>select 'True' from dual where 1 not in (null, 2 ) ;未选定行demo@XIAOXIAO> demo@XIAOXIAO>select 'True' from dual where null not in (null, 1, 3 ) ;未选定行demo@XIAOXIAO> demo@XIAOXIAO>select 'True' from dual where null not in (1, 3 ) ;未选定行总结: (1)null和任何值(包括null本身) 比较的结果都是null (2)in 只包含 in后面的数据 (3)not in 和not exists 不等价,不相同的情况出现在 not in 后的结果集中出现null值的时候
to Eric_1999(╙@^@╜) :
select * from a where a.id not in (1, 2, 3) 选出a.id为除1,2,3外的其他数据,包括null。---------------------------- not in 并不会选择出null 的数据,下面的例子可以说明这个:demo@XIAOXIAO>create table testf (id varchar2(10), name varchar2(10));表已创建。demo@XIAOXIAO>insert into testf values(null, 'null');已创建 1 行。demo@XIAOXIAO>insert into testf values('1', '1');已创建 1 行。demo@XIAOXIAO>insert into testf values('2', '2');已创建 1 行。demo@XIAOXIAO>insert into testf values('3', '3');已创建 1 行。demo@XIAOXIAO>select * from testf where id not in ('1','2','3') 2 /未选定行
from a
where a.id not in (1, 2, 3)
选出a.id为除1,2,3外的其他数据,包括null。
但
select *
from a
where a.id in (1, 2, 3)
只选出a.id为1,2,3的,null不会选中。
=1、 <>1 都不会选中null的。
----
Truedemo@XIAOXIAO>
demo@XIAOXIAO>select 'True' from dual where 1 in (null, 2 ) ;未选定行demo@XIAOXIAO>
demo@XIAOXIAO>select 'True' from dual where null in (null, 1, 3 ) ;未选定行demo@XIAOXIAO>
demo@XIAOXIAO>select 'True' from dual where null in (1, 3 ) ;未选定行demo@XIAOXIAO>
demo@XIAOXIAO>select 'True' from dual where 1 not in (null, 1, 2 ) ;未选定行demo@XIAOXIAO>
demo@XIAOXIAO>select 'True' from dual where 1 not in (null, 2 ) ;未选定行demo@XIAOXIAO>
demo@XIAOXIAO>select 'True' from dual where null not in (null, 1, 3 ) ;未选定行demo@XIAOXIAO>
demo@XIAOXIAO>select 'True' from dual where null not in (1, 3 ) ;未选定行总结: (1)null和任何值(包括null本身) 比较的结果都是null
(2)in 只包含 in后面的数据
(3)not in 和not exists 不等价,不相同的情况出现在 not in 后的结果集中出现null值的时候
select *
from a
where a.id not in (1, 2, 3)
选出a.id为除1,2,3外的其他数据,包括null。----------------------------
not in 并不会选择出null 的数据,下面的例子可以说明这个:demo@XIAOXIAO>create table testf (id varchar2(10), name varchar2(10));表已创建。demo@XIAOXIAO>insert into testf values(null, 'null');已创建 1 行。demo@XIAOXIAO>insert into testf values('1', '1');已创建 1 行。demo@XIAOXIAO>insert into testf values('2', '2');已创建 1 行。demo@XIAOXIAO>insert into testf values('3', '3');已创建 1 行。demo@XIAOXIAO>select * from testf where id not in ('1','2','3')
2 /未选定行