MySQL中<>运算符与NOT逻辑判断NULL值的差异说明

你的第一条SQL语句没有语法报错，但存在逻辑错误——你误用了普通比较运算符来判断NULL值，这是SQL新手非常容易踩的基础规则坑。 #

规则说明 #

SQL（包括你使用的MySQL）对NULL值的运算有特殊的三值逻辑设计，和普通值的比较规则完全不同：

• NULL的语义是未知/不存在的值，不是一个可以参与普通比较的具体值。所有使用普通比较运算符（=、<>、>、<、!=等）和NULL进行运算的表达式，结果永远不会是TRUE或FALSE，只会返回第三个逻辑值：UNKNOWN。
• WHERE子句的过滤逻辑是：只保留判断结果为TRUE的行，结果为FALSE或UNKNOWN的行都会被过滤掉。
  当你执行select firstName, lastName from people where middleName <> NULL;时，无论行中的middleName是存储的实际字符串还是NULL，middleName <> NULL的判断结果都是UNKNOWN，所有行都被过滤，自然返回空集。

  补充：哪怕你写middleName = NULL，得到的结果也完全一样——所有行的判断结果都是UNKNOWN，查不到任何匹配值。

• IS NULL和IS NOT NULL是SQL标准中专门用来判断NULL值的运算符，不遵循普通比较的三值逻辑，返回结果只有TRUE和FALSE两种：
  • 若字段值为NULL：字段 IS NULL返回TRUE，字段 IS NOT NULL返回FALSE
  • 若字段为非NULL的具体值：字段 IS NULL返回FALSE，字段 IS NOT NULL返回TRUE
    这也是为什么你执行select firstName, lastName from people where middleName is not null;时，能正确筛选出middleName有实际值的行，返回预期的John Kennedy记录。

额外提示 #

MySQL默认严格遵循上述SQL标准规则，虽然可以通过修改sql_mode中的ANSI_NULLS参数改变NULL比较的行为，但这种修改会破坏SQL的跨库兼容性和逻辑一致性，生产环境强烈不建议使用。 #

内容的提问来源于stack exchange，提问作者anas16