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);
   
        以上两个     查询结果集相同