Oracle语法：where条件in/not in中包含NULL时处理

发表于 |
| 字数统计: 673

在Oracle查询语句中，经常会用到in或这not in条件，如果in或not in后面的集合中的元素都是非NULL值，这种情况没什么特殊的。但如果含有null值，则可能因为不理解其机制导致预料之外的结果。本文通过具体的示例来了解其机制。

数据准备

新建一个表，并准备5条数据。

1
2
3
4
5
6
7
8
CREATE TABLE T_TEST(
   ID NUMBER(10)
);
INSERT INTO T_TEST (ID) VALUES(1);
INSERT INTO T_TEST (ID) VALUES(2);
INSERT INTO T_TEST (ID) VALUES(3);
INSERT INTO T_TEST (ID) VALUES(4);
INSERT INTO T_TEST (ID) VALUES(NULL);

示例

1.in条件中不包含NULL的情况

1
2
3
4
5
6
7
8
>select * from t_in where id in (1,3);

	ID
----------
	 1
	 3

2 rows selected.

上面的条件等价于id =1 or id = 3得到的结果正好是2；查看执行计划中可以看到 2 - filter(“ID”=1 OR “ID”=3)说明我们前面的猜测是正确的。

2.in条件包含NULL的情况

1
2
3
4
5
6
7
8
9

>select * from t_in where id in (1,3,null);

	ID
----------
	 1
	 3

2 rows selected.

上面的条件等价于id = 1 or id = 3 or id = null，我们来看下图当有id = null条件时Oracle如何处理。

从上图可以看出当不管id值为NULL值或非NULL值，id = NULL的结果都是UNKNOWN，也相当于FALSE。所以上面的查结果只查出了1和3两条记录。

查看执行计划看到优化器对IN的改写

3.not in条件中不包含NULL的情况

1
2
3
4
5
6
7
8
>select * from t_in where id not in (1,3);

	ID
----------
	 2
	 4

2 rows selected.

上面查询的where条件等价于id != 1 and id !=3，另外t_test表中有一行为null，它虽然看上去满足!=1和!=3，但根据上面的规则，NULL与其他值做=或!=比较结果都是UNKNOWN，所以也只查出了2和4。

从执行计划中看到优化器对IN的改写

4.not in条件中包含NULL值的情况

1
2
3
4

>select * from t_in where id not in (1,3,null);

no rows selected

上面查询的where条件等价于id!=1 and id!=3 and id!=null，根据上面的规则，NULL与其他值做=或!=比较结果都是UNKNOWN，所以整个条件就相当于FALSE的，最终没有查出数据。

从执行计划中查看优化器对IN的改写

总结

根据上文中的结果分析，总结出两条规律：
（1）in条件语句中始终查不到目标列包含NULL值的行。
（2）如果not in条件中包含null值，则不会返回任何结果。

所以在实际的工作中一定要注意in、not in里包含的子查询是否包含null值。

------ 本文完 ------