mysql中的null

not in 中有null 不会返回任何结果

select * from emp where comm not in (null,200);
Empty set

select empno,ename,comm from emp where comm not in (200) and comm is not null;
+-------+--------+----------+
| empno | ename  | comm     |
+-------+--------+----------+
|     6 | 彭和尚 | 20000.00 |
|     7 | 张中   | 10000.00 |
|     8 | 说不得 | 15000.00 |
|     9 | 冷谦   | 10000.00 |
|    10 | 周巅   | 30000.00 |
+-------+--------+----------+
5 rows in set (0.26 sec)

in中有null返回非null部分

select empno,ename,comm from emp where comm in (null,200);
+-------+--------+--------+
| empno | ename  | comm   |
+-------+--------+--------+
|     1 | 张无忌 | 200.00 |
+-------+--------+--------+
1 row in set (0.08 sec)

select empno,ename,comm from emp where comm in (200) or comm is null;
+-------+----------+--------+
| empno | ename    | comm   |
+-------+----------+--------+
|     1 | 张无忌   | 200.00 |
|     2 | 紫衫龙王 | NULL   |
|     3 | 白眉鹰王 | NULL   |
|     4 | 金毛狮王 | NULL   |
|     5 | 青翼蝙王 | NULL   |
|    11 | 殷野王   | NULL   |
|    12 | 殷素素   | NULL   |
|    13 | 小昭     | NULL   |
|   666 | NULL     | NULL   |
+-------+----------+--------+
9 rows in set (0.29 sec)

不等于不返回null的数据

select empno,ename,comm from emp where comm != 200;
+-------+--------+----------+
| empno | ename  | comm     |
+-------+--------+----------+
|     6 | 彭和尚 | 20000.00 |
|     7 | 张中   | 10000.00 |
|     8 | 说不得 | 15000.00 |
|     9 | 冷谦   | 10000.00 |
|    10 | 周巅   | 30000.00 |
+-------+--------+----------+
5 rows in set (0.28 sec)