结合 Mybatis，探讨 Oracle 中 in 与 not in 的陷阱

2021-10-04 22:37:54

一、Oracle 中的 in 与 not in

1. 正常情况下的使用

查询语句使用 in 和 not in 的例子

--表：T_USER，USER_ID 有 1,2,3,4,5,6
--查询 userId 中包含1,2,3的数据
--查询结果：1,2,3
SELECT * FROM T_USER T WHERE T.USER_ID IN (1, 2, 3);

--查询 userId 中不包含1,2,3的数据
--查询结果：4,5,6
SELECT * FROM T_USER T WHERE T.USER_ID NOT IN (1, 2, 3);

2. 数据为空时的使用

数据为空时，是最容易产生理解错误和查询报错。

数据为“空”表现为，没有数据或者数据为 null 。如以下两种情景：

数据为空，例如

SELECT * FROM T_USER T WHERE T.USER_ID IN ();

这时候查询语句会报错，“缺失表达式”。

数据为 null，例如

SELECT * FROM T_USER T WHERE T.USER_ID IN (NULL);

这时候查询结果为空，即没有任何数据。

而此种情况下容易产生理解错误的是以下两个查询语句：

SELECT * FROM T_USER T WHERE T.USER_ID IN (NULL);

和

SELECT * FROM T_USER T WHERE T.USER_ID NOT IN (NULL);

很多人以为这两个查询语句的查询结果应该是相反的，即 IN (NULL) 查不出数据，而 NOT IN (NULL) 查出所有数据，或者反过来。

但实际的查询结果是，两个语句查询结果都为空，即都没有数据。

二、结合 Mybatis，探讨 Oracle 中 in 与 not in 的陷阱

1. 常用场景：

在 Mybatis 中使用 in 或者 not in 的方式如下：

<if test="null != list and list.size > 0 ">
    AND T.USER_ID IN
    <foreach collection="list" item="userID" open="(" separator="," close=")">
        #{userID}
    </foreach>
</if>

这种方式可以有效避免因为出现 IN ( ) 而导致的缺失表达式异常。

但是这个表达式所使用的场景是，有 userID 集合时，查询出集合中 userID 数据；没有 userID 集合时，查询出所有 userID 的数据。

2. 特殊场景：当没有 userID 集合时，不查任何数据

偶尔会出现这样的使用场景，当没有 userID 集合时，不查任何数据。

这时候就有人会把 if 条件去掉，即采用如下的查询方式

AND T.USER_ID IN
<foreach collection="list" item="userID" open="(" separator="," close=")">
    #{userID}
</foreach>

很容易理解为什么有人会这么写。list 有值时，in 就有数据，查询也就有数据；list 为空时，in 就没有数据，查询也就得不到数据。

理想很丰富，现实很骨感。

如果你这么写的时候，你们当list为空时，就会出现如下的查询语句

AND T.USER_ID IN

直接导致缺失表达式异常。

我的一个解决办法是，在 list 中增加一个数据库中没有的 userID，例如 userID = 0 的数据。

那么当你需要的 list 为空时，list 中仍然保留有 0 这个值。于是，当你查询时会出现如下语句

AND T.USER_ID IN (0)

码农公寓