排序时遇到 NULL 默认排在哪里？如何控制 NULLS FIRST / NULLS LAST？

考察要点

这道题面试官想看你是否理解 SQL 中 NULL 的特殊性，以及它在排序操作中的行为。核心考察点包括：

1. SQL 标准与实现差异：是否知道 NULL 的默认排序行为在 SQL 标准中未定义，并且主流数据库（如 MySQL, PostgreSQL, Oracle, SQL Server）有不同的默认实现。
2. 显式控制能力：是否掌握标准 SQL 提供的 NULLS FIRST / NULLS LAST 语法来精确控制 NULL 的位置。
3. 兼容性与变通方案：在数据库不支持标准语法时，是否能用 CASE 表达式等通用方法实现相同的排序逻辑，体现解决问题的灵活性。

核心概念

NULL 在 SQL 中代表“未知”或“缺失值”，它不等于任何值，包括另一个 NULL。这导致它在排序时的行为需要特别定义。

1. 默认排序行为

SQL 标准并未规定 NULL 的默认排序顺序，交由各数据库厂商自行实现。

数据示例： 假设我们有表 employees：

• 在 MySQL / SQL Server 中执行 ORDER BY salary ASC
  text

  ⧉⛶

   1-- 输出
   22, Bob, NULL
   34, David, NULL
   41, Alice, 8000
   53, Charlie, 9000

• 在 PostgreSQL / Oracle 中执行 ORDER BY salary ASC
  text

  ⧉⛶

   1-- 输出
   21, Alice, 8000
   33, Charlie, 9000
   42, Bob, NULL
   54, David, NULL

2. 如何控制 NULL 的位置

为了解决不同数据库实现带来的不一致性，SQL 标准（从 SQL:2003 开始）引入了 NULLS FIRST 和 NULLS LAST 子句。

• NULLS FIRST：强制将 NULL 值排在最前面，无论 ASC 还是 DESC。
• NULLS LAST：强制将 NULL 值排在最后面，无论 ASC 还是 DESC。

语法支持情况：

• ✅ 支持：PostgreSQL, Oracle, MySQL 8.0+, SQLite 3.30+
• ❌ 不支持：SQL Server, MariaDB, MySQL 5.7 及更早版本

SQL 示例 (假设在 PostgreSQL 或 MySQL 8.0+ 环境)：

 1-- 无论升序还是降序，都将 NULL 排在最后
 2SELECT name, salary FROM employees ORDER BY salary ASC NULLS LAST;
 3SELECT name, salary FROM employees ORDER BY salary DESC NULLS LAST;

3. 兼容性解决方案

对于不支持 NULLS FIRST/LAST 的数据库，可以使用 CASE 表达式或特定函数来模拟。

a. 使用 CASE 表达式（最通用） 通过创建一个额外的排序列，将 NULL 和非 NULL 值分到不同的组。

 1-- 目标：升序排列，NULL 在最后 (NULLS LAST)
 2-- 思路：创建一个排序列，非 NULL 为 0，NULL 为 1，先按此列升序排，再按 salary 升序排
 3SELECT name, salary
 4FROM employees
 5ORDER BY
 6  CASE WHEN salary IS NULL THEN 1 ELSE 0 END ASC,
 7  salary ASC;
 8
 9-- 目标：降序排列，NULL 在最前 (NULLS FIRST)
10-- 思路：创建一个排序列，NULL 为 0，非 NULL 为 1，先按此列升序排，再按 salary 降序排
11SELECT name, salary
12FROM employees
13ORDER BY
14  CASE WHEN salary IS NULL THEN 0 ELSE 1 END ASC,
15  salary DESC;

b. 使用 IS NULL (MySQL/MariaDB 特有技巧) 在 MySQL 中，布尔表达式的结果是 1 (true) 或 0 (false)。salary IS NULL 在 salary 为 NULL 时返回 1，否则返回 0。

 1-- 目标：升序排列，NULL 在最后 (NULLS LAST)
 2-- salary IS NULL 返回 0 (非NULL) 或 1 (NULL)，升序排列此结果即可
 3SELECT name, salary
 4FROM employees
 5ORDER BY salary IS NULL ASC, salary ASC;

常见低分回答

1. “NULL 默认都排在最前面”：这是最常见的错误，候选人只熟悉 MySQL 或 SQL Server 的行为，并将其泛化为所有数据库的规则，忽略了 PostgreSQL/Oracle 的不同实现。
2. 不知道 NULLS FIRST/LAST：只知道使用 CASE 表达式或 IFNULL 等 workaround，说明对标准 SQL 的新特性了解不足，缺乏在现代数据库环境下的最佳实践经验。
3. 滥用 COALESCE 或 IFNULL：写出 ORDER BY IFNULL(salary, -1) ASC 这样的代码。这有潜在风险：如果 -1 是一个合法的 salary 值，排序结果就会出错。这种方法只在替换值（如-1, 99999999）绝对不可能出现在业务数据中时才勉强可用，但健壮性差。
4. 在 SQL Server 中尝试使用 NULLS LAST：写出不兼容的 SQL，表明对特定数据库方言的掌握不牢固。
5. 混淆 ORDER BY col DESC 和 NULL 的关系：例如在 PostgreSQL 中，认为 ORDER BY salary DESC 会把 NULL 放在最后，而实际上默认是放在最前。

工程实践与优化

• 优先使用标准语法：在支持 NULLS FIRST/LAST 的环境中（如 MySQL 8.0+），应始终优先使用此语法。它代码更简洁、意图更明确，且数据库优化器能更好地理解和优化。
• CASE 表达式的性能：ORDER BY CASE WHEN col IS NULL ... END, col 这种写法虽然通用，但可能影响索引的使用。优化器可能无法将其转换为简单的索引扫描，从而导致 filesort（在内存或磁盘上排序），尤其是在 col 上有索引时。相比之下，原生 NULLS FIRST/LAST 更有可能被优化器利用索引高效执行。
• 通过 EXPLAIN 验证：无论使用哪种方法，都应该使用 EXPLAIN (或 EXPLAIN ANALYZE) 来检查执行计划。关注 type、key 和 Extra 字段。如果 Extra 中出现 Using filesort，说明排序无法利用索引，需要分析原因。
• 索引设计：B+ 树索引本身就可以存储 NULL 值。在 MySQL InnoDB 中，NULL 值被视为最小值，并记录在索引的最左侧叶子节点。因此，ORDER BY col ASC (默认 NULLS FIRST) 可以直接利用索引顺序扫描，而 ORDER BY col DESC 也是反向扫描，效率很高。
• 避免在应用层排序：除非数据量极小且有特殊业务逻辑，否则不要将排序任务交给应用层。这会增加网络传输开销、消耗应用服务器资源，并且无法实现数据库分页（LIMIT/OFFSET）。

常见追问与延伸题目

1. 追问：如果我用的 MySQL 5.7，ORDER BY salary IS NULL, salary 这个写法的原理是什么？

   • 回答要点：在 MySQL 中，布尔表达式的结果为 0 (false) 或 1 (true)。salary IS NULL 这个表达式会针对每一行计算：如果 salary 是 NULL，结果是 1；如果不是 NULL，结果是 0。ORDER BY salary IS NULL ASC, salary ASC 的意思是：
     1. 首先，按照 salary IS NULL 的结果升序排序。所有非 NULL 行（结果为 0）会排在 NULL 行（结果为 1）的前面。
     2. 然后，在 0 和 1 这两个大组内部，再按照 salary 自身的值升序排序。这就实现了非 NULL 值内部有序，且所有 NULL 值被统一放到了最后。

2. 追问：如果我要对一个字符串列 position 排序，同时希望 NULL 和空字符串 '' 都排在最后，该怎么写？

   • 回答要点：这需要一个更精细的 CASE 表达式来创建三个排序组：有效字符串、空字符串、NULL。
     sql

     ⧉⛶

      1ORDER BY
      2  CASE
      3    WHEN position IS NULL THEN 2  -- NULL 值组
      4    WHEN position = '' THEN 1     -- 空字符串组
      5    ELSE 0                        -- 其他有效字符串组
      6  END ASC,
      7  position ASC;

     这个 CASE 表达式创建了一个优先级：有效字符串 (0) > 空字符串 (1) > NULL (2)。先按这个优先级升序，即可把空字符串和 NULL 都排到最后，并且 NULL 在空字符串之后。

3. 追问：NULLS FIRST/LAST 和 CASE 写法，在有索引的情况下，哪个性能更好？为什么？

   • 回答要点：通常情况下，NULLS FIRST/LAST 的性能更好。
     • NULLS FIRST/LAST 是数据库的原生语法。查询优化器被设计为能够直接理解这个指令。如果排序列上有索引，优化器可以直接利用索引进行顺序或逆序扫描，NULL 值在 B+ 树索引中通常位于一端，数据库可以高效地处理它们，避免额外的排序操作。
     • CASE 表达式 对优化器来说是一个黑盒函数。虽然现代优化器越来越智能，但很多时候它无法将 ORDER BY CASE WHEN col IS NULL... 优化为对 col 列索引的直接使用。这很可能导致数据库放弃使用索引进行排序，转而执行“filesort”：即读取所有满足 WHERE 条件的行，然后在内存或临时文件中进行排序。这个过程的代价远高于直接利用索引。
     • 结论：NULLS FIRST/LAST 不仅可读性好，而且给了优化器最大的优化空间，是性能首选。CASE 表达式是功能强大的兼容性后备方案，但在性能敏感的场景下要用 EXPLAIN 仔细评估其对索引使用的影响。