mysql 线上not in查询中的一个坑

今天早上开发又过来说，怎么有个语句一直没有查询出结果，数据是有的呀，并发来了如下的sql（为了方法说明，表名及查询均做了修改）：

select * from t2 where t2.course not in (select name from t1);

 

两个表的数据如下：

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
|  2 | chen |
|  3 | li   |
+----+------+
3 rows in set (0.00 sec)


mysql> select * from t2;
+------+--------+
| id   | course |
+------+--------+
|    1 | NULL   |
|    2 | chen   |
|    3 | math   |
+------+--------+
3 rows in set (0.00 sec)

 

所以按照要求来说，理论上应该显示如下的数据才对：

+------+--------+
| id   | course |
+------+--------+
|    3 | math   |
+------+--------+
1 row in set (0.00 sec)

但实际的返回结果却时empty

mysql>  select * from t2 where t2.course not in (select name from t1);
Empty set (0.00 sec)

这个可怪有意思了，不过一看到这种表中有null字段的都会先吐槽一下，一般来说字段都可以设置成非null的，而且效率也低，也要占用空间（mysql要记录该条目是否为null）

既然已经这样了，就分析分析：

mysql>  select * from t2 join t1 on t1.name=t2.course;
+------+--------+----+------+
| id   | course | id | name |
+------+--------+----+------+
|    2 | chen   |  2 | chen |
+------+--------+----+------+
1 row in set (0.00 sec)

注意：结果中并没有出现course与name都为null的字段，即在mysql中null与null不相等。

 

mysql>  select * from t2 where t2.course not in (null);
Empty set (0.00 sec)

mysql>  select * from t2 where t2.course not in (null,'chen');
Empty set (0.00 sec)

注意：not in中包含有null时，结果集一直为Empty set

 

再看看下面的语句：

mysql>  select * from t2 where t2.course not in ('chen');
+------+--------+
| id   | course |
+------+--------+
|    3 | math   |
+------+--------+
1 row in set (0.00 sec)


mysql> select 1 > null;
+----------+
| 1 > null |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

注意，id=1，course=null的字段并没有被查询出来,null值与任何非null值比较都为null（非0非1）

如果非要查询出包含null的字段，可以这样：

mysql> select * from t2 where course is null or course not in ('chen');
+------+--------+
| id   | course |
+------+--------+
|    1 | NULL   |
|    3 | math   |
+------+--------+
2 rows in set (0.00 sec)

 

那么正确的姿势应该是怎么写呢？
用left join的方法，如下：

mysql> select t2.* from t2 left join t1 on t2.course=t1.name where t1.name is null ;
+------+--------+
| id   | course |
+------+--------+
|    1 | NULL   |
|    3 | math   |
+------+--------+
2 rows in set (0.00 sec)

 

当然如果t2表不包含null时，可以用字查询接条件not null的方法：

mysql> select * from t2 where t2.course not in (select name from t1 where name is not null);
+------+--------+
| id   | course |
+------+--------+
|    3 | math   |
+------+--------+
1 row in set (0.00 sec)

有允许null的字段查询时要多留个心眼，最好设计之初就不要出现允许null字段的存在。