数据库 NULL 值对比运算符（null safe equal）

在 SQL 的规定中，NULL 是不等于 NULL 的，所以如果使用类似 SELECT NULL = NULL 这种语句，获取到的会是一个 FALSE。

但是有些时候我们又希望能够匹配到数据库中的 NULL，通常写法是 SELECT NULL IS NULL，但是有没有能够同时兼容 NULL 和非 NULL 的情况呢？

MySQL

MySQL :: MySQL 5.7 Reference Manual :: 12.4.2 Comparison Functions and Operators

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

Oracle

Comparing NULLable Values | An Oracle Programmer

Oracle Null Safe Comparison (Spoiler alert: SYS_OP_MAP_NONNULL) | Aykut Akın’s Blog

PostgreSQL

a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b

PostgreSQL: Documentation: 9.2: Comparison Operators

PostgreSQL: Re: NULL safe equality operator

SQL Server

SQL Server 没有这种操作符，只能通过控制 ANSI_NULLS 来控制，但是这个只影响列和常量比较，不影响两个列之间的比较，所以还是有缺陷。

Changing the setting of ANSINULLS is not a solution, because it does not affect column-to-column comparisons, only column to variable comparisons. Setting ANSINULLS to off is also non-standard and not granular enough to apply to specific comparisons in a single query.

——Add language and optimizer support for ISO – Customer Feedback for ACE Community Tooling

一种通用的写法：

• a IS NOT DISTINCT FROM b can be rewritten as:

  (NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL))
  

  ——sql - How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM? - Stack Overflow

或者可以写成 (b IS NULL AND b IS NULL) OR (a IS NOT NULL AND b IS NOT NULL AND a = b)

Does SQL Server support IS DISTINCT FROM clause? - Stack Overflow

sql - How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM? - Stack Overflow

= (Equals) (Transact-SQL) - SQL Server | Microsoft Docs

SET ANSI_NULLS (Transact-SQL) - SQL Server | Microsoft Docs

Mimicking null-safe equal to operator in SQL Server - CodeProject

Why does NULL = NULL evaluate to false in SQL server - Stack Overflow

参考资料

1. Modern SQL: IS DISTINCT FROM — A comparison operator that treats two NULL values as the same
2. Comparing NULLable Values | An Oracle Programmer