null和子查询--not in和not exists怎么选择？

原创听见风的声音 2025-07-21

372

1 空值导致的SQL返回结果错误

数据库中空（NULL）和数学中的0一样，总是时不时的会给造成意外、麻烦或者困扰，导致意外的结果出现。今天介绍一个子查询和空值的例子。测试数据还是以学生登记系统为例，涉及的两张表如下：

1.1 测试数据

学生登记表

mysql> select * from roster_1 limit 3;
+-----------+-----------+-----------------+------------+-----------------+
| roster_id | class_num | student_name    | student_id | enrollment_date |
+-----------+-----------+-----------------+------------+-----------------+
|         1 |       101 | Mia Brown       | S90872     | 2023-04-14      |
|         2 |       104 | Donald Wright   | S20618     | 2023-07-13      |
|         3 |       102 | Andrew Gonzalez | S70574     | 2023-01-02      |
+-----------+-----------+-----------------+------------+-----------------+

课程表

mysql> select * from class limit 3;
+-----------+-----------------------------+------------------+--------------+
| class_num | class_name                  | department       | credit_hours |
+-----------+-----------------------------+------------------+--------------+
|       101 | Calculus I                  | Mathematics      |            4 |
|       102 | English Composition         | English          |            3 |
|       103 | Introduction to Programming | Computer Science |            3 |
+-----------+-----------------------------+------------------+--------------+

1.2 出问题的SQL语句

mysql> SELECT class_num, class_name
         FROM class
         WHERE class_num IN
            (SELECT class_num FROM roster_1);
+-----------+-----------------------------+
| class_num | class_name                  |
+-----------+-----------------------------+
|       101 | Calculus I                  |
|       102 | English Composition         |
|       103 | Introduction to Programming |
|       104 | General Physics             |
|       105 | World History               |
+-----------+-----------------------------+
5 rows in set (0.01 sec)

roster_1表中没有class_num为空的行，这条SQL的查询结果是正确的。将表中一行的class_num一行改为null看看会怎么样

mysql> update roster_1 set class_num=null where roster_id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from roster_1 where roster_id=1;
+-----------+-----------+--------------+------------+-----------------+
| roster_id | class_num | student_name | student_id | enrollment_date |
+-----------+-----------+--------------+------------+-----------------+
|         1 |      NULL | Mia Brown    | S90872     | 2023-04-14      |
+-----------+-----------+--------------+------------+-----------------+

再次运行上面的查询

mysql> SELECT class_num, class_name FROM class WHERE class_num not IN        (SELECT class_num FROM roster_1);
Empty set (0.00 sec)

查询的结果为空。这个查询结果显然是错误的，但是在not in子查询的逻辑上却是合理的。

2 not in 子查询的逻辑解释

在SQL标准中，上面那条语句的定义如下

SELECT class_num, class_name FROM class WHERE class_num not IN (SELECT class_num FROM roster_1);

in表达式的定义如下

SELECT class_num, class_name FROM class WHERE class_num not (class_num = any (select  class_num from  roster_1));

等效于

SELECT class_num, class_name FROM class WHERE class_num not (
   (class_num = 101)
or (class_num = 102)
-----省略多行
or (class_num = null)) --class_num=null的结果是unkown，也即是空

根据德摩根定律，这个查询实际上是

SELECT class_num, class_name FROM class WHERE class_num  (
   (class_num != 101)
and (class_num != 102)
-----省略多行
and unkown)

这个条件的结果总是unkown，因此查询返回的结果总是为空。

3 MySQL优化器对not in的转换

数据库对这个语句是怎么处理的，可以从这条语句的执行计划的生成过程看出来，语句的执行计划的生成过程可以通过优化器追踪文件得到

{
 "expanded_query": "/* select#1 */ select `class`.`class_num` AS `class_num`,`class`.`class_name` AS `class_name` from `class` 
  where <in_optimizer>(`class`.`class_num`,<exists>(/* select#2 */ select `roster_1`.`class_num` from `roster_1` 
where ((<cache>(`class`.`class_num`) = `roster_1`.`class_num`) or (`roster_1`.`class_num` is null)) 
having <is_not_null_test>(`roster_1`.`class_num`)) is false) limit 0,200"
}

上面内容截取自这条语句的优化器追踪文件join_prepare,是join_prepare的最后一步，显示是查询语句转换扩展后的最终结果。优化器将not in形式的子查询转换成了exist形式，条件转换为(class.class_num) = roster_1.class_num) or (roster_1.class_num is null)表达式的为false，roster_1.class_num is null的值是总是true，exists条件也就一直是false，这个查询也就一直返回空集了。

4 解决的办法

4.1 改写not in子查询

简单的解决办法是在子查询里加上一个条件，使子查询的返回结果为非空

mysql> SELECT class_num, class_name FROM class WHERE class_num IN (SELECT class_num FROM roster_1 where class_num is not null);

4.2 改成not exists形式

另一个办法是将语句写成not exists的语法，需要在逻辑思维上做一下改变

mysql> SELECT class_num, class_name     FROM class a    WHERE not exists (SELECT class_num FROM roster_1 where class_num=a.class_num);

这种方式不仅避免了表roster_1上class_num列有空值导致的返回结果为空的问题，在roster_1表的class_num列上有索引的情况下性能会更好一些。执行计划比较如下

--not in形似 roster_1上class_num列有无索引都是这个执行计划
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: <in_optimizer>(class.class_num,class.class_num in (select #2) is false)  (cost=1.25 rows=10) (actual time=2.11..2.11 rows=0 loops=1)
    -> Table scan on class  (cost=1.25 rows=10) (actual time=0.0838..0.0877 rows=10 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((class.class_num = `<materialized_subquery>`.class_num))  (cost=603..603 rows=1) (actual time=0.183..0.183 rows=0.455 loops=11)
            -> Limit: 1 row(s)  (cost=603..603 rows=1) (actual time=0.183..0.183 rows=0.455 loops=11)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (class_num=class.class_num)  (actual time=0.183..0.183 rows=0.455 loops=11)
                    -> Materialize with deduplication  (cost=603..603 rows=3000) (actual time=2..2 rows=6 loops=1)
                        -> Covering index scan on roster_1 using idx_class_num  (cost=303 rows=3000) (actual time=0.0402..1.45 rows=3000 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--not exist 形式class_num列上无索引
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop antijoin  (cost=3002 rows=30000) (actual time=2.04..2.05 rows=5 loops=1)
    -> Table scan on a  (cost=1.25 rows=10) (actual time=0.017..0.0208 rows=10 loops=1)
    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (class_num=a.class_num)  (cost=603..603 rows=1) (actual time=0.202..0.202 rows=0.5 loops=10)
        -> Materialize with deduplication  (cost=603..603 rows=3000) (actual time=2.01..2.01 rows=5 loops=1)
            -> Filter: (roster_1.class_num is not null)  (cost=303 rows=3000) (actual time=0.008..1.52 rows=2999 loops=1)
                -> Table scan on roster_1  (cost=303 rows=3000) (actual time=0.005..1.35 rows=3000 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
--not exists形式 class_num列有无索引
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop antijoin  (cost=505 rows=5000) (actual time=0.0521..0.0594 rows=5 loops=1)
    -> Table scan on a  (cost=1.25 rows=10) (actual time=0.0215..0.0249 rows=10 loops=1)
    -> Covering index lookup on roster_1 using idx_class_num (class_num=a.class_num)  (cost=2709 rows=500) (actual time=0.00271..0.00271 rows=0.5 loops=10)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

在有了合适的索引之后，使用not exists语法的语句使用索引查找避免了子查询的物化，同时使用了MySQL的反连接优化，操作的步骤减少到了3步，性能明显由于前面2个执行计划。避免了查询的物化不仅提高了这条语句单次执行的效率，同时，也提高了这条语句并发执行的能力，各种物化即临时表的创建是高并发SQL语句的主要瓶颈之一。

4.3 调整表结构，在列上加上非空限制

按照正常的业务逻辑，此案列中的roster_1表上class_num列上不应该有控制，一条没有课程的登记记录没有任何意义。业务的这种逻辑应该在表设置是就得到考虑，在roster_1的class_num列加上非空限制,执行下面语句前应该确保要调整的列上没有空置。

 alter table roster_1 modify class_num int not null;

如调整的列上有空值，报错如下

mysql> alter table roster_1 modify class_num int not null;
ERROR 1138 (22004): Invalid use of NULL value

5 后记

null的问题可以说自关系数据库诞生之时就一直存在了，不同的数据库对空值的实现也不尽相同。如果在数据库阶段就考虑这个问题，会避免在后期很多隐患。至于not in和not exists的区别，各个数据库的实现也可能会有区别，选择前也最好分析验证一下。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者