Oracle null的问题帮忙解决一下

Question

select * from emp where comm not in (null);
select * from emp where comm in (null);
为什么都没有输出东西

Answer 1

如下引自官方文档 (本来想翻译的，不过原文更准确)

A null can be assigned but it cannot be equated with anything, including itself. NULL values represent missing or unknown data. NULL values are not an integer, a character, or any other specific data type. Note that NULL is not the same as an empty data string or the numerical value '0'.

null 不能用 = 与任意值比较，甚至包括null 本身，也就是说即便是 null = null 也是没有意义的。

select * from emp where comm not in (null); 相当于比较coｍｍ = null
select * from emp where comm in (null); 相当于比较coｍｍ != null
这二行语句都是无效语句

oracle 有专门的 IS [NOT] NULL，用于测试表达式是否为null值。

例如：

SELECT *
FROM suppliers
WHERE supplier_name IS NULL;