一文解决SQL查询中关于NULL的陷阱

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 -> false;

select c_name, case c_nationcode
               when 'us' then 'USA'
               when 'cn' then 'China'
               when null then 'China'
               else 'Others' end
from customer

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 customer

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);

select * from customer 
  where c_custkey not in (select o_custkey from orders)

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

select * from customer 
where c_custkey not in (select o_custkey from orders 
                        where o_custkey is not null)

select * from customer
where not exists (select o_custkey from orders 
where o_custkey=c_custkey)

select * from customer where c_regdate 
         > all(select o_orderdate from orders)

select * from customer 
where c_regdate > all(select o_orderdate from orders 
      where o_orderdate is not null)

select * from customer where 
   c_regdate > (select max(o_custkey) from orders)

1. 遍历Query语法树，获取所有的NOT IN 谓词
  1. 如果这个谓词是PredicateInValueSelect( 样式`expr not in (select ....)`)
     1. 判断子查询结果集的列是否可以为空
        1. 不可以为空，返回
        2. 可以为空，为结果集里的每个可以为空的列rc，判定在子查询的where条件里是否有 `rc is not null`谓词
           1. 如果没有，新增一个 `rc is not null`谓词，通过and连接符合并到子查询的where条件里
           2. 如果有，继续判定下个列
  2. 如果这个谓词是PredicateInValueList （`expr not in values(1,2,null)`）
     1.遍历值列表
     1. 如果值为null，删除

isNullable(选择列expr){
    if (expr是数据库表的列) {
        if (列定义可以为NULL)
            return true;
        else
            return true;
    } else if (选择列是NULL) {
        return true;
    } else if (选择列是非NULL常量或是变量) {
        return false;
    } else if (选择列是组合表达式){
        return 组合表达式的每个分项 的 or 结果;
    }else if (选择列是函数) {
        if(函数是"SUM"、"Count"、"AVG")
            return false;
        else
            return isNullable(函数参数)
    } else if (expr是标量子查询) {
        return isNullable(标量子查询的选择列)
    } else{
        return true;
    }
}

-- 案例1: = null 重写为 is null
select count(*) from customer where c_phone=null;  
-- 案例2：case expr when null 重写为 case when expr is null
select case c_phone when null then 1 when '139%' then 0 else -1 end from customer where not c_phone = null;
-- 案例 3: c_nationkey可空，增加 c_nationkey  is not null条件
select count(*) from nation where n_nationkey not in (select c_nationkey from customer);
-- 案例 4: c_nationkey可空，所以max(c_nationkey)可空, 增加 c_nationkey is not null条件
select count(*) from nation where n_nationkey not in (select max(c_nationkey) from customer group by c_mktsegment);
-- 案例 5: count()/sum() 永远不为空，所以无需重写
select count(*) from nation where (n_name,n_nationkey) not in (select 'China',c_nationkey from customer);
-- 案例 6: c_name不为空，但是c_nationkey可空，所以需增加 c_nationkey is not null 条件
select count(*) from nation where n_nationkey not in (select count(c_nationkey) from customer group by c_mktsegment);
-- 案例 7:  c_nationkey可空，所以重写为 > (select min(c_nationkey) from customer)
select count(*) from customer where n_nationkey > all(select c_nationkey from customer);

/********************************************************************\
 *                                                                   *
 *                PawSQL Optimization Summary                        *
 *                                                                   *
 *                 Powered by PawSQL(2021- )                         *
 *                                                                   *
\********************************************************************/


1. Optimization Summary
 - There are 7 query analyzed;
 - There are 6 optimization rewrites for 6 queries;
 - There are 9 violations of optimization rule against 6 queries;
 - There are 1 indices recommended for 4 queries;
 - There is 12.91% performance improvement.


2. Optimization Rule Violations
 - RuleUseEqual4Null: [null.sql-stmt2, null.sql-stmt1]
 - RuleAllQualifierSubQueryRewrite: [null.sql-stmt7]
 - RuleNoCond4NonAggSelect: [null.sql-stmt3, null.sql-stmt6, null.sql-stmt7]
 - RuleNotInNullableSubQueryRewrite: [null.sql-stmt4, null.sql-stmt3, null.sql-stmt6]
 ...

-- 原SQL
select count(*) from customer where c_phone=null; 


-- 重写后的SQL
select count(*) from customer where customer.c_phone is null;

-- 原SQL
select case c_phone when null then 1 when '139%' then 0 else -1 end from customer;


-- 重写后的SQL
select case
         when c_phone is null then 1 
         when c_phone = '139%' then 0 
         else -1 
       end
  from customer;

-- 原SQL
select count(*) from nation where n_nationkey not in (select c_nationkey from customer);


-- 重写后的SQL
select count(*) from nation where n_nationkey not in ( select c_nationkey from customer
               where c_nationkey is not null)

select count(*) from nation where n_nationkey not in (select max(c_nationkey) from customer group by c_mktsegment);


-- 重写后的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)

select count(*) from nation where n_nationkey not in (select count(c_nationkey) from customer group by c_mktsegment);

-- 原SQL
select count(*) from nation where (n_name,n_nationkey) 
    not in (select 'China',c_nationkey from customer);


-- 重写后的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)

-- 原SQL
select count(*) from customer where n_nationkey > all(select c_nationkey from customer);


-- 重写后的SQL
select count(*)
  from customer
  where n_nationkey > (select min(c_nationkey) from customer)