not in 与not exists区别

1、对于not exists查询，内表存在空值对查询结果没有影响；对于not in查询，内表存在空值将导致最终的查询结果为空。

2、对于not exists查询，外表存在空值，存在空值的那条记录最终会输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。

eg:    select * FROM customer a where   not exists (select customer_no from bank_customer b  where a.customer_no=b.customer_no );


     select * from customer where customer_no        is not null       and customer_no  not in (select customer_no from bank_customer     where     customer_no is not null);

    以上两个     查询结果集相同

not in查询，内表存在空值将导致最终的查询结果为空。

例子
创建测试表
CREATE TABLE test_aa (
id INT ,
aname VARCHAR(20)
);
CREATE TABLE test_bb (
id INT ,
bname VARCHAR(20)
)

表中数据
SELECT * FROM test_aa
id aname
—— ——–
1 AAA
2 BBB
3 CCC
4 GQQ
5 GRQ
SELECT * FROM test_BB

有NULL值的not in 和 not EXISTS 的查询
not in

not exists的查询

全部测试语句

---

create table test_aa (
id int ,
aname varchar(20) 
);
create table test_bb (
id int ,
bname varchar(20) 
)

select * from test_aa

INSERT INTO test_BB SELECT * FROM test_aa

SELECT * FROM test_BB

INSERT INTO test_BB VALUES(NULL,NULL) 

SELECT *  FROM test_BB WHERE bname IS NULL
--  对于not in查询，内表存在空值将导致最终的查询结果为空。
SELECT * FROM TEST_AA A WHERE (A.ID ,A.aname) NOT IN(SELECT ID ,BNAME FROM test_BB  )

SELECT * FROM TEST_AA A WHERE   NOT EXISTS (SELECT 1 FROM test_BB B WHERE B.bname =A.ANAME AND A.ID =B.ID  )

对于上述 in 和exists 不存在此问题

即便有空值，下面2条语句结果是一致的。

SELECT * FROM TEST_AA A WHERE (A.ID ,A.aname)  IN(SELECT ID ,BNAME FROM test_BB  )--  对于not in查询，内表存在空值将导致最终的查询结果为空。

SELECT * FROM TEST_AA A WHERE     EXISTS (SELECT 1 FROM test_BB B WHERE B.bname =A.ANAME AND A.ID =B.ID  )

后记

(本次使用的是MySQL)，对于oracle ” 和null 是一致的，mysql 的” 和NULL 不是一致的。