目录
介绍
NULL值处理是数据库应用开发者最容易出错的，主要是因为我们习惯于使用二进制布尔逻辑，而数据库对NULL值的处理逻辑是三值逻辑。事实上，数据库优化器中最有缺陷的组件是与NULL值处理相关的逻辑。即使是成熟的数据库软件，如DB2/Teradata，仍然有超过20%的错误相关NULL处理。
在本文中，我们分析了NULL值陷阱的根本原因，并以简单有效的检查逻辑得出结论，以推断最终结果。同时，我们解释了日常开发工作中四种常见场景的适用条件和解决方案。阅读本文后，您将能够在日常SQL处理工作中处理有关NULL值处理的所有场景。
检查代码
以下检查逻辑涵盖了SQL处理中NULL值的所有方案，通过了解以下逻辑，可以避免NULL陷阱。
- 所有比较NULL和算术运算符（>, =, <, <>, <=, >=, +,-,*, /）的结果是unknown
- unknown的逻辑运算（AND, OR, NOT）遵循三值运算的真值表
- 如果操作的结果直接返回给用户，则用NULL表示unknown
- 如果操作的结果是true或false作为SQL处理过程中的条件，那么操作需要由三值逻辑执行，最终结果由以下逻辑确定：
- {false、unknown} ->false
- {true} ->true
- 在集合运算（如UNION或INTERSECT ,
NULL）中，值被视为彼此相等。
三值逻辑1
在逻辑中，三值逻辑（也称为三值逻辑、三重逻辑、三元逻辑或三值逻辑，有时缩写为3VL）是几个多值逻辑系统中的任何一个，其中有三个真值表示真、假和一些不确定的第三个值。这与更常见的二价逻辑（如经典的判断逻辑或布尔逻辑）形成对比，后者只提供真和假。
NULL值处理的陷阱
1. 与NULL值的比较
结论：expr = null不能确定表达式expr为null，is null应该用来判断expr是否为null。
假设有一个包含以下五列的customer表，其中两列（c_nationcode，c_phone）可为空。
CREATE TABLE customer (
c_custkey int4 NOT NULL,
c_name varchar(25) NOT NULL,
c_nationcode char(8) NULL,
c_phone varchar(15) NULL,
c_regdate date NULL,
CONSTRAINT customer_pkey PRIMARY KEY (c_custkey)
);
insert into customer values(1, 'Randy', 'en', '13910010010', '20210911');
insert into customer values(2, 'Mandy', null, '13910010012', '20200211');
insert into customer values(3, 'Ray', 'us', null, '20180902');如果开发人员想通过以下语句找出使用空电话的客户，他不会成功，因为以下语句的结果始终为空。
select * from customer where c_phone = null;正确的方法应该是：
select * from customer where c_phone is null;我们根据，谓词c_phone = null的计算结果为unknown;则根据规则4.1，unknown被视为false。
c_phone=null
-> unknown
-> false;2. 表达式使用NULL时的Case When
结论：case expr when null无法确定表达式是否为null，正确的方法是case when expr is null。
Where或having子句中的问题更容易检测和纠正，而通过人工或现有的SQL审计工具检测case when表达式中的null用法要困难得多。
例如，如果我们想将国家代码解码为国家名称，并且代码是null，我们要将国家名称设置为China：
select c_name, case c_nationcode
when 'us' then 'USA'
when 'cn' then 'China'
when null then 'China'
else 'Others' end
from customer上述语句不会将国家/地区代码null转换为China。因为when null实际上是由c_nationcode = null执行的操作。正确的方法应该是：
select c_name, case when c_nationcode = 'us' then 'USA'
when c_nationcode = 'cn' then 'China'
when c_nationcode is null then 'China'
else 'Others' end
from customer3. 不在带有空的子查询中
结论：具有可为空select元素的not in子查询的谓词将始终计算为false。
假设我们有一个orders表，其中客户id（o_custkey）和订单日期（o_orderdate）由于缺少数据而可为空。
CREATE TABLE orders (
o_orderkey int4 NOT NULL,
o_custkey int4 NULL,
o_orderdate date NULL,
CONSTRAINT orders_pkey PRIMARY KEY (o_orderkey)
);
insert into orders values(1, 1, '2021-01-01');
insert into orders values(2, null, '2020-09-01');
insert into orders values(3, 3, null);现在我们想找到没有订单的客户进行营销。预期结果是customer的c_custkey是2，查询语句可能如下所示：
select * from
customer
where c_custkey not in (
select o_custkey
from orders
)事实上，上面的查询不会给我们任何回报。原因是在子查询中的o_custkey有值null，NOT IN处理逻辑是这样的：
c_custkey not in (1，3,null)
→ c_custkey<>1 and c_custkey<>3 and c_custkey<>null
→ c_custkey<>1 and c_custkey<>3 and unknown
→ unknown
-> false实际上，如果子查询的结果集中有null值，则SQL将始终返回空结果集。
有两种正确的方法：
1、向子查询添加一个谓词NOT NULL，即：
select *
from customer
where c_custkey not in (
select o_custkey
from orders
where o_custkey is not null
)2、将NOT IN子查询重写为not exists子查询，即：
select *
from customer
where not exists (
select o_custkey
from orders
where o_custkey=c_custkey
)4. 所有合格的子查询与空
结论：具有可为空的选择元素的ALL限定子查询的条件将始终计算为false。
假设我们想找出用户被撤销后错误注册的订单。解决方案之一是以下查询语句：
select *
from customer
where c_regdate > ALL (
select o_orderdate
from orders
where c_custkey = o_custkey
)与上面的NOT IN类似，由于子查询的结果中存在NULL，此SQL不会返回预期结果。该ALL操作实际上是通过将其与返回的结果集进行比较，然后执行该AND操作来执行的，最终结果为unknown。虽然作为要评估的条件是unknown，结果是false。
有两种方法可以纠正它：
1、向子查询添加NOT NULL谓词：
select *
from customer
where c_regdate > all(
select o_orderdate
from orders
where o_orderdate is not null
)2、重写expr > all以聚合标量子查询expr > (select max()...) 2：
select *
from customer
where c_regdate > (
select max(o_custkey)
from orders
)使用SQL优化工具进行NULL处理
数据库将执行SQL指示的操作。因此，开发人员要么必须特别注意这些陷阱，要么借助SQL优化工具（如PawSQL或EverSQL）来应对它们。在PawSQL中，有三个重写优化规则用于NULL处理，对应于上述四种情况。
| 规则代码 | 规则说明 |
| UseEqual4NullRewrite | `expr = null'或'case expr when null'无法确定'expr'是否为null，is null应使用 |
| NotInNullableSubQueryRewrite | 具有可为null的选择元素的'not infalse'子查询的谓词将始终计算为 。 |
| AllQualifierSubQueryRewrite | 具有可为空的选择元素的ALL限定子查询的条件将始终计算为false |
PawSQL会根据DDL中某个列的定义是否可为空，以及对该列的操作是否会产生可为空的结果，来确定子查询中的查询元素是否可为空。如果可以为空，则重写的SQL将推荐给用户。
- 案例 1：= null重写为is null
-- Original SQL
select count(*)
from customer
where c_phone = null;
-- Rewritten SQL
select count(*)
from customer
where customer.c_phone is null;- 案例 2：case expr when null重写为case when expr is null
-- Original SQL
select case c_phone
when null then 1
when '139%' then 0
else -1
end
from customer;
-- Rewritten SQL
select case
when c_phone is null then 1
when c_phone = '139%' then 0
else -1
end
from customer;- 情况3：c_nationkey可为空，添加条件c_nationkey is not null
-- Original SQL
select count(*)
from nation
where n_nationkey not in (
select c_nationkey
from customer
);
-- Rewritten SQL
select count(*)
from nation
where n_nationkey not in (
select c_nationkey
from customer
where c_nationkey is not null
)- 情况4：c_nationkey可为空，因此max(c_nationkey)可为空，添加条件c_nationkey is not null
-- Original SQL
select count(*)
from nation
where n_nationkey not in (
select max(c_nationkey)
from customer
group by c_mktsegment
);
-- Rewritten SQL
select count(*)
from nation
where n_nationkey not in (
select max(customer.c_nationkey)
from customer
where c_nationkey is not null
group by c_mktsegment
)- 情况 5：count(c_nationkey)从不为空，因此无需重写。
select count(*)
from nation
where n_nationkey not in (
select count(c_nationkey)
from customer
group by c_mktsegment
);- 情况 6：c_name不为空，但c_nationkey可为空，因此需要添加c_nationkey is not null条件。
-- Original SQL
select count(*)
from nation
where (n_name,n_nationkey) not in (
select 'China',c_nationkey
from customer
);
-- Rewritten SQL
select count(*)
from nation
where(n_name, n_nationkey) not in (
select'China', c_nationkey
from customer
where customer.c_nationkey is not null
)- 情况 7：c_nationkey可为空，因此> all (select c_nationkey ...)重写为> (select min(c_nationkey) from customer)
-- Original SQL
select count(*)
from customer
where n_nationkey > all(
select c_nationkey
from customer
);
-- Rewritten SQL
select count(*)
from customer
where n_nationkey > (
select min(c_nationkey)
from customer
)- https://en.wikipedia.org/wiki/Three-valued_logic
- 如果expr < all或expr < = all，然后将其重写为expr < (select min() ...)
https://www.codeproject.com/Tips/5345787/Four-Pitfalls-of-SQL-Processing-with-NULL-Values
153
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
