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)