mysql 线上not in查询中的一个坑
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)
既然已经这样了,就分析分析:
这个可怪有意思了,不过一看到这种表中有null字段的都会先吐槽一下,一般来说字段都可以设置成非null的,而且效率也低,也要占用空间(mysql要记录该条目是否为null)
既然已经这样了,就分析分析:
注意:结果中并没有出现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)
再看看下面的语句:
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字段的存在。
原创:https://www.cnblogs.com/zejin2008/p/5227698.html