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

GBase 8a在执行not in时，如果嵌套的子查询里有null值，结果集是空，没有满足条件的记录。 因为从理论上讲， null 到底是没匹配上，还是原来的值就是null,无法区分，所以SQL在这种情况下，都是返回空。

复现

gbase> select * from table1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from table2;
+------+
| id   |
+------+
|    1 |
| NULL |
|    2 |
+------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> insert into table1 values(3);
Query OK, 1 row affected (Elapsed: 00:00:00.03)

gbase> select * from table1;
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> select * from table2;
+------+
| id   |
+------+
|    1 |
| NULL |
|    2 |
+------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> select * from table1 where id in(select id from table2);
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select * from table1 where not id in(select id from table2);
Empty set (Elapsed: 00:00:00.01)

gbase> select * from table1 where not id in(select id from table2 where id is not null);
+------+
| id   |
+------+
|    3 |
+------+
1 row in set (Elapsed: 00:00:00.02)

解决方案

not in 改成 not exists

gbase> select * from table1 where not exists (select id from table2 where table2.id=table1.id);
+------+
| id   |
+------+
|    3 |
+------+
1 row in set (Elapsed: 00:00:00.11)