表数据如下所示:
+----------+------+
| name | age |
+----------+------+
| xiaoming | 13 |
| xiaohong | 13 |
| xiaogang | 14 |
| xiaofa | 14 |
| xiaoliu | NULL |
+----------+------+
我们想完成以下两个查询:
1. 查询age属于(13,NULL)的记录
mysql> SELECT * FROM STUDENTS WHERE AGE IN (13,NULL);
+----------+------+
| name | age |
+----------+------+
| xiaoming | 13 |
| xiaohong | 13 |
+----------+------+
2 rows in set (0.00 sec)
2. 查询age不属于(13,NULL)的记录
mysql> SELECT * FROM STUDENTS WHERE AGE NOT IN (13,NULL);
Empty set (0.00 sec)
结果并非我们预期的一样:
- 查询1缺少了
xiaoliu NULL
这条记录 - 查询2缺少了
xiaogang14, xiaofa 14
两条记录
原因在于MySQL中,任何数据都等于NULL,任何数据都不等于NULL,即
x == NULL // false
x != NULL // false
x为任意数据,包括NULL
一般来说,两个数的关系只可能是相等和不相等二者之一,然而MySQL中的NULL却做到了时而相等时而不相等。。。
根据上述分析,修改查询如下:
mysql> SELECT * FROM STUDENTS WHERE AGE IN (13) OR AGE IS NULL;
+----------+------+
| name | age |
+----------+------+
| xiaoming | 13 |
| xiaohong | 13 |
| xiaoliu | NULL |
+----------+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM STUDENTS WHERE AGE NOT IN (13);
+----------+------+
| name | age |
+----------+------+
| xiaogang | 14 |
| xiaofa | 14 |
+----------+------+
2 rows in set (0.00 sec)
总结,凡是含有NULL的表达式返回值都是false