sql中使用not in容易犯错的一个地方

在TERADATA中使用not in来做过滤条件的时候需要谨慎。如果要过滤的字段C存在null值，而我们想要的结果不想把这个字段为null的记录过滤掉，条件我们就要写成（C is  null or c not in （'ss','dd'....）），在实际情况中我们经常漏掉C is  null导致我们的数据不全。例子如下：

 

先建立临时表和插入一些数据

 

CREATE VOLATILE MULTISET TABLE A ,NO LOG
(
A VARCHAR(5)
, B VARCHAR(5)
)
PRIMARY INDEX (A)
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE MULTISET TABLE B ,NO LOG
(
B VARCHAR(5)
, C VARCHAR(5)
)
PRIMARY INDEX (B)
ON COMMIT PRESERVE ROWS;

insert into A values('a1','b1');
insert into A values('a2','b2');
insert into A values('a3','b3');
insert into A values('a4','b4');

insert into B values('b3','c3');
insert into B values('b4','c4');
insert into B values('b5','c5');
insert into B values('b6','c6');

现在做

select * from A left join B on A.B= B.B

where B.C is null or B.C not in ('c3')

和

select * from A left join B on A.B= B.B

where B.C not in ('c3')

自然会发现问题所在。