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 不是一致的。