Oracle中NOT IN的问题
今天在开发的时候需要查询数据库中哪些学生有多个学号，写的sql如下：
???? select * from(
??select count(t1.xh) as xhs,t2.uuid,t3.xm as xsxm,t3.sfzjh, t1.xjzt as xjzt from hust_xs_jb t1
???INNER JOIN hust_xx_identity t2 on t1.sfid= t2.sfid
? ??INNER JOIN hust_xx_profile t3 on t2.uuid=t3.uuid group by t2.uuid ,t3.xm, t3.sfzjh, t1.xjzt order by t3.xm) t
??where xhs>=2 and xjzt not in('15','17','19','21','30','31','32','33','34','35','36','37','38','39','40','51','52','53','54')
查出的数据为空，但是去掉where条件中的第二个条件（也就是NOT IN条件）却能查出31条数据，且其中只有一条的数据的XJZT字段为33，其他的都为null，应该查出30条数据啊，这是为什么呢？难道NOT? IN会将null值过滤掉？于是将sql稍加修改：
???? select * from(
??select count(t1.xh) as xhs,t2.uuid,t3.xm as xsxm,t3.sfzjh, t1.xjzt as xjzt from hust_xs_jb t1
???INNER JOIN hust_xx_identity t2 on t1.sfid= t2.sfid
? ??INNER JOIN hust_xx_profile t3 on t2.uuid=t3.uuid group by t2.uuid ,t3.xm, t3.sfzjh, t1.xjzt order by t3.xm) t
??where xhs>=2 and (xjzt not in('15','17','19','21','30','31','32','33','34','35','36','37','38','39','40','51','52','53','54') or xjzt is null)
就能查出所需要的30条数据了。网上查了一下，说Oracle not in 如果返回的有null值的话，就不会有记录。
