in 和 not in 会处理null 吗 ?
1. in 和 not in 会处理null 吗 ?
- 使用in时,忽略null值,不会查询条件为null的数据;
- 使用not in时,如果 not in后面的括号中没有null,会查询条件列中符合要求的数据,但会过滤掉条件为null的数据;
- 使用not in时,如果 not in后面的括号中有null,直接返回false,查询结果为空。
t1:
t2:
select * from t2 where teacher in ( select teacher from t1 where teacher is not null );
select * from t2 where teacher in ( select teacher from t1 ) or teacher is null;
可以看出, where a in ( b) : a存在null时, 查不出a中null的数据. ( 自动忽略null值), 如果要查出a中的null数据需要加上 or a is null
select * from (select * from t2 where teacher is not null) t where teacher in ( select teacher from t1 );
可以看出, where a in ( b) : b中存在null 时, 查不出b中null的数据. ( 自动忽略null值)
select * from t2 where teacher not in ( select teacher from t1 where teacher is not null );
可以看出, where a not in (b ), a中存在null值时, 会返回空.
select * from (select * from t2 where teacher is not null) t where teacher not in ( select teacher from t1 );
可以看出, where a not in (b ), b中存在null值时, 也返回空
钟声敲响了日落,柏油路跃过山坡,一直通向北方的是我们想象,长大后也未曾经过~