南大通用GBase 8a中not in 和 not exists的区别

本文介绍,在GBase 8a数据据集群中, not in 和 not exists的区别。

先看in 和 exists, 从本质上讲,都是判断等于,这个基本不会有任何问题,但对于判断不等于,出现了一个特例,那就是 null 不等于任何值。

错误样例

查询没有使用中的颜色。

颜色表

gbase> select * from colors;
+----------+-------------+
| color_id | color_value |
+----------+-------------+
| c001     | Red         |
| c002     | Yellow      |
| c003     | Green       |
| c004     | Black       |
| c005     | White       |
+----------+-------------+
5 rows in set (Elapsed: 00:00:00.00)

产品表

注意,其中1003的产品,颜色当前未知,为null。

gbase> select * from products;
+------------+-------------------+----------+
| product_id | product_name      | color_id |
+------------+-------------------+----------+
|       1000 | smartwatch        | c001     |
|       1001 | ballpoint pen     | c003     |
|       1002 | wireless keyboard | c004     |
|       1003 | charging pad      | NULL     |
+------------+-------------------+----------+
4 rows in set (Elapsed: 00:00:00.01)

not in的查询结果

结果为空,不是预期的Yellow和White。

gbase> SELECT col.color_value FROM Colors AS col 
  WHERE col.color_id  NOT IN  
  (SELECT prod.color_id FROM Products AS prod);
Empty set (Elapsed: 00:00:00.04)

gbase> 

原因就是,在判断not in时,是将每个值做类似如下的判断

color_id NOT IN (c001, c003, c004, NULL)

等同于

 NOT (color_id = c001 OR color_id = c003 OR color_id = c004 OR color_id = NULL).

而其中的 color_id = NULL 导致返回结果是【未知】,因为null不【等于】null, 也不【不等于】null。

最终导致返回值为false。

修正方法

使用not exists代替not in ,除非你确认你的数据里不包含null。

gbase> SELECT col.color_value
    -> FROM Colors AS col
    -> WHERE NOT EXISTS
    -> (SELECT prod.color_id
    ->  FROM Products AS prod
    ->  WHERE col.color_id = prod.color_id);
+-------------+
| color_value |
+-------------+
| Yellow      |
| White       |
+-------------+
2 rows in set (Elapsed: 00:00:00.09)

总结

尽量避免使用not in这个用法,而是改用not exists。

参考

GBase 8a在not in子查询中有null值时返回空结果集的原因