in not in

exists（sql 返回结果集为真，即有值）

not exists (sql 不返回结果集为真，即为空） 

如下： 
表A 
ID NAME 
1    A1 
2    A2 
3  A3 

表B 
ID AID NAME 
1    1 B1 
2    2 B2 
3    2 B3 

表A和表B是１对多的关系 A.ID => B.AID 

SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID) 
执行结果为 
1 A1 
2 A2 
原因可以按照如下分析 
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１) 
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2) 
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3) 
--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据 

NOT EXISTS 就是反过来 
SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID) 
执行结果为 
3 A3 
=========================================================================== 
EXISTS = IN,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因 
SELECT ID,NAME FROM A　 WHERE　ID IN (SELECT AID FROM B) 

NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别 
SELECT ID,NAME FROM A WHERE　ID　NOT IN (SELECT AID FROM B) 

 

 

 

如果in和not in的子集中不存在NULL的值，但in和not in的合集则为全部数据

若not in的子集中存在有null值，则会查找不到数据的，结果为空。

 

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select deptno
  2  from dept
  3  where deptno in (10,50,null);

    DEPTNO
----------
        10

//看到使用in的时候即便有null 也是正常的 下面看一下not in

SQL> select deptno
  2  from dept
  3  where deptno not in (10,50);

    DEPTNO
----------
        20
        30
        40

//这里看起来和我们的预期挺符合的哦

SQL> select deptno
  2  from dept
  3  where deptno not in (10,50,null);

no rows selected

//怎么回事 为什么加了个null 前面的20、30、40三条数据就不显示出来了

 

IN和NOT IN本质上都是OR运算，因而计算逻辑OR时处理NULL的方式不同，产生的结果也不同。

下面我们分析一下前面的三条语句

 

SQL> select deptno
  2  from dept
  3  where deptno in (10,50,null);

这里可以等价于where deptno=10 or deptno=50 or deptno=null，由于是or相连接，那么只要有一个条件为TRUE，整个就喂TRUE了。所以deptno为10的记录显示出来了。

 

SQL> select deptno
  2  from dept
  3  where deptno not in (10,50,null);

这里等价于where not (deptno=10 or deptno=50 or deptno=null)，拿deptno=20的记录来举例吧。

not (20=10 or 20=50 or 20=null)

not(false or false or null)

not null

null

(以前只知道在where条件返回false的时候不成立，现在看来返回NULL的时候也不成立呀，下面是做的一个小实验可以证明这个猜想)

#####################

SQL> select * from dept
  2  where 1=null;

no rows selected

#####################

SQL> select deptno
  2  from dept
  3  where deptno not in (10,50);

这里等价于where not (deptno=10 or deptno=50)，依然拿deptno=20来举例。

not (20=10 or 20=50 )

not(false or false)

not false

true

注意：FALSE OR NULL=NULL ，而TRUE OR NULL=TRUE。