SQL 中 NULL 值在 ORDER BY 中的排序规则

橘子 发布于 18 天前 11 次阅读

背景

SQL 标准未严格规定 NULL 在 ORDER BY 排序中的默认位置，各数据库的约定不一致，分两大阵营：一方将 NULL 视为"比任何值都小"（最小值），另一方将 NULL 视为"比任何值都大"（最大值）。这导致同样的 SQL 在不同数据库中，NULL 行的出现位置可能截然相反。

此外，MaxCompute（ODPS）底层与 Hive 有渊源，但两者的 NULL 排序行为并不完全一致，容易混淆。

调研结果

两大阵营总览

"最小值"阵营（NULL 比任何值都小）：

  • 升序（ASC）：NULL 排在最前面
  • 降序（DESC）：NULL 排在最后面
  • 代表：MaxCompute、MySQL、SQL Server、SQLite、Hive（CDH 默认）

"最大值"阵营（NULL 比任何值都大）：

  • 升序（ASC）：NULL 排在最后面
  • 降序（DESC）：NULL 排在最前面
  • 代表：Oracle、PostgreSQL、Impala、Hive（CDP 默认）

各数据库具体行为

数据库NULL 视为支持 NULLS FIRST/LAST
MaxCompute最小值支持（可能需开 flag）
MySQL最小值不支持
SQL Server最小值不支持
SQLite最小值支持
Oracle最大值支持
PostgreSQL最大值支持
Hive（CDH 默认）最小值支持
Hive（CDP 默认）最大值支持
Impala最大值支持

备注：

  • Hive 的 NULL 排序方向受 hive.default.nulls.last 配置控制，CDH 默认 false（最小值），CDP 默认 true（最大值），可通过 set hive.default.nulls.last=false; 切换。
  • MaxCompute 内建函数（SORT_ARRAY、GREATEST）同样将 NULL 视为最小值，与 ORDER BY 一致。

MaxCompute 的 NULLS FIRST / NULLS LAST 语法

MaxCompute 支持在 ORDER BY 中使用 NULLS FIRST / NULLS LAST 显式控制 NULL 位置。但使用时可能需要先开启 flag：

set odps.sql.executionengine.streamlinex.enable.all=true;

如果不开 flag 直接写 NULLS FIRST/LAST，可能报错：

Parse exception - To enable nulls first/last, must set odps.sql.executionengine.streamlinex.enable.all=true

窗口函数的 ORDER BY 子句语法直接内置了 [nulls {first|last}]，官方文档写明：

order by  [asc|desc][nulls {first|last}] [, ...]

MaxCompute 与 Hive 的关键差异

MaxCompute 没有暴露类似 hive.default.nulls.last 的配置项来切换 NULL 排序方向。NULL 在 MaxCompute ORDER BY 中固定视为最小值，无法通过配置改为最大值行为。如果需要让 NULL 在 ASC 时排到最后，只能使用 NULLS LAST 语法或 ORDER BY (col IS NULL) ASC, col ASC 等变通写法。

关键结论

  1. MaxCompute 中 NULL 在 ORDER BY 中视为最小值，与 MySQL 一致，与 Oracle 相反。
  2. 如果之前从 Oracle/PostgreSQL 迁移 SQL 到 MaxCompute，NULL 的排序位置会反转，需注意。
  3. 要显式控制 NULL 位置，使用 NULLS FIRST / NULLS LAST，但可能需要先开启功能。
  4. Hive 的 NULL 行为可被配置切换，MaxCompute 不可以——不要将 Hive 的行为直接套用到 MaxCompute 上。
  5. MaxCompute 内建函数（SORT_ARRAY、GREATEST）与 ORDER BY 对 NULL 的处理一致，都视为最小值。

参考

MaxCompute（阿里云官方文档）：

PostgreSQL（官方文档）：

  • PostgreSQL 文档 7.5. Sorting Rows (ORDER BY)： https://www.postgresql.org/docs/current/queries-order.html
    • 原文："By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise." 即：NULL 视为最大值。ASC 时 NULL 排最后（等价 NULLS LAST），DESC 时 NULL 排最前（等价 NULLS FIRST）。支持 NULLS FIRST/LAST 语法。

SQL Server（微软官方文档）：

MySQL（官方文档）：

  • MySQL 9.0 Reference Manual - 5.3.4.6 Working with NULL Values： https://dev.mysql.com/doc/refman/9.0/en/working-with-null.html
    • 原文："When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC." 即：ASC 时 NULL 排最前，DESC 时排最后（等同最小值）。MySQL 不支持 NULLS FIRST/LAST 语法。

Oracle（官方文档）：

  • Oracle Database 19c SQL Language Reference - ORDER BY 子句语法图： https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/img_text/order_by_clause.html
    • 语法图确认支持 [ASC | DESC] [NULLS FIRST | NULLS LAST]。
  • Oracle Java DB (Derby) 10.6 参考文档（同域 docs.oracle.com，行为一致）： https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html
    • 原文："If the null ordering is not specified then the handling of the null values is: NULLS LAST if the sort is ASC, NULLS FIRST if the sort is DESC." 即：NULL 视为最大值。ASC 时 NULL 排最后，DESC 时排最前。支持 NULLS FIRST/LAST 语法。 注意：此原文来自 Oracle Java DB (Derby) 文档，非 Oracle Database SQL Language Reference 正文。Oracle Database 的 SELECT 语句文档页面过长，ORDER BY 语义部分未能完整获取，但语法图已确认支持 NULLS FIRST/LAST，且行为与上述一致。

SQLite（官方文档）：

  • SQLite Datatypes In SQLite Version 3 - 第 6 节 Sorting, Grouping and Compound SELECTs： https://sqlite.org/datatype3.html
    • 原文："When query results are sorted by an ORDER BY clause, values with storage class NULL come first, followed by INTEGER and REAL values interspersed in numeric order..." 即：NULL 在排序中排在最前（等同最小值）。该页面未区分升序与降序，也未提及 NULLS FIRST/LAST 语法。SQLite 较新版本（3.30+）已支持 NULLS FIRST/LAST，但官方 datatype3 文档未涉及此语法。

Hive（Cloudera 官方文档）：

Impala（Cloudera 官方文档）：