【SQL】结果不存在返回null的问题--以LeetCode数据库176、177第N高的薪水为例

一、使用场景

当结果不存在时，返回null

二、解题思路

• 使用IFNULL显式地处理
• 或者利用标量子查询天然返回NULL

1.IFNULL函数

IFNULL()函数时SQL中用于处理NULL值的空值处理函数。它的核心是提供一个默认值，当一个表达式可能为NULL时，用这个默认值来代替NULL，从而避免NULL值在计算或数据显示中引发问题。

语法结构：IFNULL ( expression, alt_value )

• expression：需要被检查是否为NULL的表达式。可以时一个字段名、一个计算式或者一个函数。

• alt_value：当 expression 的求值结果为 NULL 时，函数将返回这个替代值。alt_value 的类型应与 expression 的类型兼容。

返回值规则：

• 当expression不为NULL，返回expression的值
• 当expression为NULL，返回alt_value的值

2.利用标量子查询天然返回NULL

SQL标准规定，如果一个标量子查询无法返回任何行时，它不会报错，而是自动地、天然地返回NULL值。

标量子查询是只返回单个值(一行一列)的子查询。

想要天然返回NULL需要两个条件：

1. 子查询被设计为标量子查询，即只返回单个值(一行一列)的子查询

2. 查询逻辑导致结果集为空。子查询的where条件或其他过滤逻辑使得查询结果为空。

三、例题

例题链接：176. 第二高的薪水 - 力扣（LeetCode）177. 第N高的薪水 - 力扣（LeetCode）

1.题目描述

表: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id 是该表的主键（列中的值互不相同）。
该表的每一行都包含有关员工工资的信息。

查询并返回 Employee 表中第二高的 不同 薪水 。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。查询结果如下例所示。

示例 1：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

示例 2：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

2.解题思路

1️⃣查询并返回 Employee 表中第二高的 不同 薪水 。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。

a.找到第二高的不同薪水。不同薪水需要distinct去重；第二高有两种方法：一种是order by降序排列，通过limit offset限制查询结果，另一种是使用窗口函数排序。

#降序排列，limit限制第二高
select distinct salary
from employee
order by salary desc
limit 1 offset 1;

#窗口函数排序
select distinct salary,dense_rank()over(order by salary desc) rk
from employee

b.第二高的薪水不存在时返回null。可以使用IFNULL函数显式地处理，也可以依赖子查询天然返回null。

#使用IFNULL
select ifnull(
    (select distinct salary
    from employee
    order by salary desc
    limit 1,1)
,null) SecondHighestSalary

select ifnull(
    (select salary
    from (select distinct salary,dense_rank()over(order by salary desc) rk
            from employee)t
    where rk=2)
,null) SecondHighestSalary

#标量子查询天然返回null
select 
    (select distinct salary
    from employee
    order by salary desc
    limit 1,1) SecondHighestSalary

select 
    (select salary
    from (select distinct salary,dense_rank()over(order by salary desc) rk
            from employee)t
    where rk=2) SecondHighestSalary

2️⃣编写一个解决方案查询 Employee 表中第 n 高的 不同 工资。如果少于 n 个不同工资，查询结果应该为 null 。

这里题目本身给出了一个定义函数的结构。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN(
        --子查询逻辑
);
END

CREATE FUNCTION：声明创建一个函数；

getNthHighestSalary(N INT)：函数名为getNthHighestSalary，传入的参数为一个整数N；

RETURNS INT：指定函数的返回结果为整数类型；

BEGIN...END：包裹函数体逻辑，即使只有一条语句也需要此关键字；

RETURN：返回值语句，它指定了函数执行后应该返回什么值。

它表示创建了一个名为getNthHighestSalary的函数，向其中传入一个整数参数N，返回对应的第N高的不同薪水。我们要做的是查询第N高的不同薪水，将方案写在RETURN后面的括号中，就完成了完整函数的创建。

思路和176题一样，我更习惯使用窗口排序。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN(
    select salary
    from (
        select distinct salary,dense_rank()over(order by salary desc) as rk
        from employee)t
    where rk=N
);
END

⚠️这里没有使用显式的IFNULL函数，当标量子查询没有结果时，天然返回null值。函数与普通的select查询不同，函数指定了执行后返回一个值，所以它其中的标量子查询结果为空时，会返回null值。

3.完整代码

这里另外写一下177题使用limit的写法。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
    RETURN(
        select salary
        from employee
        order by salary desc
        limit 1 offset N
);
END

求第N高的薪水，需要跳过N-1条记录，limit并不支持计算，所以先设定变量N=N-1。