oracle--in与not in 语句 关于空值问题记录

drop table tmp_1;
create table  tmp_1(aa varchar2(10),bb varchar2(10));

insert into tmp_1 (bb) values('1');
insert into tmp_1 (aa) values('1');
insert into tmp_1 (aa) values('2');
insert into tmp_1 (aa) values('3');

drop table tmp_2;
create table  tmp_2(aa varchar2(10),bb varchar2(10));

insert into tmp_2 (bb) values('1');
insert into tmp_2 (aa) values('1');
insert into tmp_2 (aa) values('2');
commit;

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

select * from tmp_1 a where a.aa not in (select aa from tmp_2);---空查询结果

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

select * from tmp_1 a where a.aa not in (select aa from tmp_2 where aa is not null);---只有值3记录，空值抛弃

3、对于in查询，外表或者内表存在空值，空值均抛弃，不影响其他有值记录

一、原题
Evaluate the following SQL statement:
SQL>SELECT cust_id, cust_last_name
      FROM customers
     WHERE cust_credit_limit IN
           (SELECT cust_credit_limit
              FROM customers
             WHERE cust_city = 'Singapore');
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the subquery.
D. It ignores the NULL value and generates output for the other values produced by the subquery.

答案：C   （存在疑问）