现象

在执行 sql 中得到了非预期结果

select * from user where age > 20;

其中, 数据库中存在大量的 age 为 null 的记录, 在执行完上述 sql 后, 没有将 age is null 的数据查出

测试

SELECT 1>=1, NULL = NULL, Null > 1;
  • mysql 5.7.21-log img_2.png
  • sqlite 3.34.0 img.png
  • postgresql PostgreSQL 14.0 (Debian 14.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit img.png

原因

stackoverflow

在 The Three-Valued Logic of SQL 中规定, 与 null 比较返回值为 null

The SQL null value basically means “could be anything”. It is therefore impossible to tell whether a comparison to null is true or false. That’s where the third logical value, unknown, comes in. Unknown means “true or false, depending on the null values”.

The result of each of the following comparisons is therefore unknown:0

NULL = 1 NULL <> 1 NULL > 1 NULL = NULL Nothing equals null. Not even null equals null because each null could be different.

文档地址 https://modern-sql.com/concept/three-valued-logic