null 专题

关于 null “丢信息”的底层逻辑较复杂，这里仅介绍注意点。

sql 三值逻辑：true / false / unknown

只要过程存在 null，结果就可能出错。

1. 关系运算符：

is null √

= null ×

null 搭配 =、<、>、<> 会被判为 unknown，得不到想要的查询结果。

 力扣584：

select name
from customer
where referee_id <> 2

---当 referee_id is null，表达式被判为 unknown，非 true，因此 null 行的记录被排除在结果外。

select name
from customer
where referee_id <> 2
or referee_id is null

select name
from customer
where ifnull(referee_id,0)!=2

---加上条件 where ... is not null / 用 ifnull (..., 0) 处理变量即可规避。

select name
from customer
where id not in
(select id
from customer
where referee_id = 2)

---也可用集合中补集的观念看待，学会将二维表看做集合。

2. in、all：

in () 、all () 的查询结果中有 null 会出错，加上条件 where ... is not null / 用 ifnull (...,0) 处理变量即可规避。

3. max 等聚合函数（count除外）：

max 不计入 null，无需加 not null 条件；

max 对空值返回 null。

力扣176：

select max(salary) as SecondHighestSalary
from 
    (select salary,dense_rank() over(order by salary desc) as rnk
    from Employee) as tmp
where rnk=2

---要求查无返回 null，用 max (salary) 替代 salary 即可。

力扣619：

select max(num) as num
from
    (select num
    from MyNumbers
    group by num
    having count(*) = 1
    and num >= all
        (select num
        from MyNumbers
        group by num
        having count(*) = 1)) as tmp

---max 聚合函数执行顺序晚于group by，早于having，故最后用 max() 需再 select 一次。

4. count：

count (*)：计入 null 所在行

count (列名)：不计入 null 所在行

找到所有学生都提交了的报告的学院：

select dpt
from students
group by dpt
having count(*) = count(sbmt_date)

---having 后条件很巧妙，count(*) 含未交报告即提交日期为 null 的学院学生，count(sbmt_date) 不含；两者相等即为学院学生均交了报告。

5. join：

力扣：

select Person.firstName,Person.lastName,city,state
from Person left join Address
on Person.PersonId = Address.PersonId

---匹配不到会返回 null。