SQL的ORDER BY已走过漫漫长路

# SQL 的 ORDER BY 已经走了很远 来源：https://modern-sql.com/blog/2026-05/order-by-history BigQuery Db2 (LUW) DuckDB H2 MariaDB MySQL Oracle DB PostgreSQL SQL Server SQLite 2005 2007 2009 2011 2013 2015 2017 2019 2021 2023 2025 ⚠ 3.5.7 - 3.53.0a ⚠ 2008R2 - 2025ab ⚠ 8.3 - 18b ✓ 11gR1 - 23.26.1 ✓ 5.7 - 9.7.0a ⚠ 5.0 - 5.6a ✓ 10.1 - 12.2.2a ⚠ 5.1 - 10.0.10a ⚠ 1.4.191 - 2.4.240b ✓ 1.0.0 - 1.5.0 ✓ 9.7 - 12.1.4 ✓ 2.0+ 1. 在 `union`、`except` 和 `intersect` 分支中有限支持（https://modern-sql.com/blog/2026-05/order-by-history#subquery.F855） 2. 表达式中不能包含选中的列（https://modern-sql.com/blog/2026-05/order-by-history#expressions.selected） `order by` 子句无疑是 SQL 中最为人熟知的子句之一。然而，仍有一些常见的误解需要澄清。为此，我将带你回顾 ISO/IEC 9075-2:2023（即 SQL 标准）中 `order by` 子句的演变。 目录： 1. 20 世纪 80 年代（https://modern-sql.com/blog/2026-05/order-by-history#early） 2. 三种顺序：语法顺序、逻辑顺序和实际执行顺序（https://modern-sql.com/blog/2026-05/order-by-history#syntax-logical-actual-order） 3. 位置引用（https://modern-sql.com/blog/2026-05/order-by-history#positional） 4. 未选中的列（https://modern-sql.com/blog/2026-05/order-by-history#non-selected） 5. 表达式（https://modern-sql.com/blog/2026-05/order-by-history#expressions） 6. 空值排序（https://modern-sql.com/blog/2026-05/order-by-history#null） 7. 在子查询中（https://modern-sql.com/blog/2026-05/order-by-history#subquery） ## 20 世纪 80 年代 最初，¹ `order by` 子句有几个后来被取消的限制：(1) 它只能引用对应 `select` 子句产生的列；(2) 不允许表达式；(3) 没有提供直接控制 `null` 值排序的语法；(4) 只允许作为查询的最后一条子句。例如，下面的查询在 ISO/IEC 9075-2:1999 之前是无效的，因为 `order by` 子句引用了一个未选中的列： ```sql SELECT a FROM t ORDER BY b ``` 你可能想知道为什么标准会让排序未选中的列变得无效。但这是个完全错误的视角。让我解释一下。 ## 三种顺序：语法顺序、逻辑顺序和实际执行顺序 如今，社交网络上充斥着无数 AI 生成的图表。其中很多都涉及 SQL 子句的*语法顺序*和*逻辑评估顺序*之间的区别。虽然我们可以很容易地看到语句中的语法顺序，比如 `select`、`from`、`order by`，但逻辑评估顺序则更为微妙。尽管逻辑评估顺序通常遵循语法顺序，但也有例外，比如 `select` 子句。 对于上面的查询，逻辑评估顺序是 `from`、`select`、`order by`。这意味着 `from` 子句首先创建一个包含表“`t`”全部内容的虚拟结果。这包括了列“`b`”（如果存在的话）。这个虚拟结果然后被传递给 `select` 子句，后者产生另一个只包含列“`a`”的虚拟结果。这个虚拟结果再被传递给 `order by` 子句，而 `order by` 子句无法在从 `select` 子句接收到的输入中找到列“`b`”。因此，该语句不是有效的 SQL。不是因为标准明确这么说，而是因为虚拟结果在子句之间传递的不幸后果。 到目前为止，我提到了 SQL 的两种顺序：语法顺序（语句中的子句顺序）和逻辑评估顺序。这两者都在标准中定义。然而，还有第三种顺序，目前流传的图表未能强调：*实际执行顺序*。尽管看起来标准定义了引擎必须执行哪些步骤来获得结果，但实际上并非如此！SQL 标准说： > 符合 SQL 标准的实现不需要执行通用规则中定义的确切操作序列，只要其效果 [...] 与该序列的效果相同即可。 > — ISO/IEC 9075-1:2023 §6.3.3.3 换句话说：标准中的所有内容仅仅意味着：“结果必须与遵循这些步骤得到的结果相同”。这涉及到另一组图表，它们宣扬“尽早过滤”以提高性能的口号。虽然这个口号在应用于*实际执行顺序*时是正确的，但将其应用于语法顺序或逻辑顺序则毫无意义。以这个语句为例： ```sql SELECT * FROM t WHERE a = 42 ``` SQL 标准定义该语句结果的方式是首先获取表“`t`”的全部内容。这个虚拟结果然后被传递给 `where` 子句，后者会丢弃所有不匹配的行，产生一个更小但仍然虚拟的结果。然后由 `select` 子句处理，最终产生查询的最终结果。如果标准要求实际执行顺序遵循这些步骤，那么索引根本不可能实现。事实上，引擎会很乐意使用列“`a`”上的索引（如果可用）。这实际上将 `where` 子句合并到了 `from` 子句中。如果查询中有连接也是如此。只要在不改变结果的情况下可以尽早应用过滤（“*下推*（https://modern-sql.com/feature/with/performance#predicate-pushdown）”），引擎就会这样做。建议使用子查询或 CTE（https://modern-sql.com/feature/with）来尽早应用过滤（例如，在连接之前）只是浪费时间。引擎会自动完成这项工作，已经持续了几十年。² 相信执行计划（https://use-the-index-luke.com/sql/explain-plan），而不是图表。执行计划告诉你引擎*实际*做了什么。 ## 位置引用 在这个简短的题外话之后，让我们回到 `order by` 子句。当时，在 ISO/IEC 9075:1992 之前，还有另一种指定哪些选中的列用于排序的方式：位置引用。下面示例中的文字“`1`”表示使用第一列进行排序。 ```sql SELECT a FROM t ORDER BY 1 ``` 请注意，这在 1999 年从标准中移除，³ 但它仍然被广泛支持，并且经常被使用。 BigQuery 2026-05-12 Db2 (LUW) 12.1.4 DuckDB 1.5.0 H2 2.4.240a MariaDB 12.2.2b MySQL 9.7.0b Oracle DB 23.26.1 PostgreSQL 18 SQL Server 2025 SQLite 3.53.0 `order by` 中的位置引用（https://modern-sql.com/blog/2026-05/order-by-history/order-by.positional.unsigned-integer.html） 1. 也可以通过绑定参数实现：`order by ?`• 负参数（但不是文字）会反转方向 2. 也可以通过绑定参数实现：`order by ?` ## 未选中的列 这是上个世纪 `order by` 子句的正式功能。但随后现代 SQL 的“大爆炸”发生了：1999 年 12 月发布了新版的 SQL 标准。这几乎改变了一切。从 `boolean` 类型到自定义类型，从 CTE（https://modern-sql.com/feature/with）到分组集。无数新功能将 SQL 远远推离了其关系起源。但 SQL:1999 也不羞于改进旧的和简单的功能，比如 `order by` 子句。特别是，SQL:1999 允许在 `order by` 中使用未选中的列。 正如我们上面所见，`order by` 子句无法访问未选中的列。所以问题是：SQL:1999 是如何做到的呢？如果 `order by` 子句引用了在 `select` 产生的虚拟结果中不存在的列，标准只是将这些列添加到 `select` 子句中。⁴ 标准文本实际上为你重写了查询。这种重写被称为*语法转换*。虽然它们非常常见，但大多数都停留在语法单元内，比如一个子句内。而这个特定的语法转换则不在乎重写另一个子句。⁵ 一旦添加到 `select` 子句中，`order by` 就可以像使用任何选中的列一样使用它。为了避免将这些添加的列返回给最终结果，`order by` 子句本身又会将它们删除。⁶ 关注点分离被无情地打破了。我认为，只要目的正当，可以不择手段。并且记住：只要结果与遵循这些步骤得到的结果相同，引擎仍然可以随心所欲。 注意这种语法转换的优先级：只有那些在 `select` 子句中找不到的列才会进行语法转换。考虑这个表和下面的查询： ```sql CREATE TABLE t ( a INTEGER, b INTEGER ) ``` ```sql SELECT b AS a FROM t ORDER BY a -- 指的是 t.b ``` `order by a` 中的列名“`a`”明确地指的是 `select` 子句产生的列“`a`”。只有当不存在这样的列时，才会考虑 `from`/`join` 子句产生的列。这确保了该查询的含义对于 SQL-92 和 SQL:1999 是相同的。 BigQuery 2026-05-12 Db2 (LUW) 12.1.4 DuckDB 1.5.0 H2 2.4.240 MariaDB 12.2.2 MySQL 9.7.0 Oracle DB 23.26.1 PostgreSQL 18 SQL Server 2025 SQLite 3.53.0 `order by` 未选中的列（https://modern-sql.com/caniuse/E121-02） ## 表达式 早期 `order by` 子句的另一个同样烦人的限制在 1999 年被解除：`order by` 键不再仅限于列引用和整数值。从那时起，`order by` 可以包含任意表达式（公式）⁷，这些表达式必须至少包含一个列名。如上所示的 `ORDER BY 1` 明确不再是标准 SQL。⁸ 27 年后，知道这种语法仍然被普遍接受，也许值得重新考虑是否将其标准化。然而，一个更相关的问题是，仍然有一些引擎不完全支持 `order by` 子句中的表达式。它们只允许对来自 `from` 子句的列使用表达式。考虑以下示例。第一个在所有测试系统中都能工作，但第二个在某些系统中会失败。 ```sql SELECT a + b FROM t ORDER BY COALESCE(a + b, 0) ``` ```sql SELECT a + b AS x FROM t ORDER BY COALESCE(x, 0) ``` BigQuery 2026-05-12 Db2 (LUW) 12.1.4 DuckDB 1.5.0 H2 2.4.240 MariaDB 12.2.2 MySQL 9.7.0 Oracle DB 23.26.1 PostgreSQL 18 SQL Server 2025 SQLite 3.53.0 对选中列使用表达式（https://modern-sql.com/blog/2026-05/order-by-history/E121-03.derived-column.html） 这个限制在一些系统的文档中提及：PostgreSQL（https://www.postgresql.org/docs/current/queries-order.html#:~:text=Note%20that%20an%20output%20column%20name%20has%20to%20stand%20alone）、SQL Server（https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver17#order_by_expression:~:text=If%20the%20ORDER%20BY%20clause%20references%20a%20column%20alias%20from%20the%20select%20list%2C%20use%20the%20column%20alias%20on%20its%20own%2E）。 ## 空值排序 `order by` 子句的下一个显著扩展是在 2003 年：引入了 `nulls first | last` 规范。 ```sql SELECT a FROM t ORDER BY b NULLS FIRST ``` 如果没有这个规范，`null` 值相对于非 `null` 值的位置是由实现定义的（ID133（https://modern-sql.com/caniuse/ID133））。标准所做的唯一规定是，引擎必须对所有 `null` 值一视同仁，要么把它们放在最前面，要么放在最后面。它们出现在最前面还是最后面可能取决于 `asc`/`desc` 规范，这样它们实际上被视为最小（≪）或最大（≫）的可能值进行排序。实际上大多数系统将 `null` 视为非常小或者非常大。 BigQuery 2026-05-05 ≪ Db2 (LUW) 12.1.4 ≫ DuckDB 1.5.0 lasta H2 2.4.240 ≪ MariaDB 12.2.2 ≪ MySQL 9.7.0 ≪ Oracle DB 23.26.1 ≫ PostgreSQL 18 ≫ SQL Server 2025 ≪ SQLite 3.53.0 ≪ 默认空值排序（https://modern-sql.com/blog/2026-05/order-by-history/concept-null.order-by-default.html） 1. 可配置：`set default_null_order = [NULLS_FIRST | NULLS_LAST | NULLS_FIRST_ON_ASC_LAST_ON_DESC | NULLS_LAST_ON_ASC_FIRST_ON_DESC]` ## 在子查询中 正如本文开头提到的，`order by` 最初不允许在子查询中使用。这又要从 SQL 的历史说起：由于“SQL 基于关系模型，但并非严格实现”，⁹ 它继承了许多关系模型的思想。其中之一是表本质上实现了集合——实际上，是多集。然而，（多）集合意味着其成员没有顺序这一属性也适用于表。这也影响到在子句之间以及从子查询到其外层查询之间传递的虚拟表。因此，在子查询中使用 `order by` 毫无意义。 直到 ISO/IEC 9075-2:2008 发布。该版本引入了 `fetch first` 子句。如果你不知道：`fetch first` 只是 `limit` 或 `top` 的更强大版本。这些子句通常（但不一定）跟在 `order by` 子句之后，后者在按照 `fetch first` 子句指定截断结果之前建立行顺序。 #### 旁注：没有“有序表” 在子句之间传递的虚拟表可能有关联的元数据。例如，`group by` 子句产生一个“*分组表*”，以便 `having` 可以按分组方式查看表中的行。同样，“*窗口表*”用于窗口函数。但在 SQL 标准中并没有“*有序表*”这种东西。这就产生了一个问题：`fetch first` 子句如何从 `order by` 子句获取有序表？答案可能令人失望：`Fetch first` 只是显式地引用由 `order by`（如果存在）建立的顺序。这种交叉引用在标准中只占一页，因为所有三个子句都在标准的同一节中涵盖。 可能只是我个人的偏好，但我倾向于将标准文本（和法律）视为代码，应该组织成更小的单元，这些单元只通过小而定义明确的接口相互作用。是的，我知道这很天真。但为什么不朝着这个目标努力呢？从这个角度来看，刚才提到的交叉引用不是什么大问题。而 `order by` 使未选中的列可用的方式在这方面则非常不同。 由于 `fetch first` 子句在子查询中是有意义的，尤其是在 `lateral` 连接中，因此在子查询中使用 `order by` 的需求就出现了。因此，`order by` 子句的语法位置从``（https://jakewheat.github.io/sql-overview/sql-2016-foundation-grammar.html#_14_3_cursor_specification）稍微移到了``（https://jakewheat.github.io/sql-overview/sql-2016-foundation-grammar.html#query-expression），后者可以嵌套。从那时起，`order by` 被允许在子查询中使用，这样 `fetch first` 也可以有意义地在其中使用。同样，但在技术上有所不同，当放在括号中时，`order by` 在 `union`、`intersect` 和 `except` 的分支中也变得有效（详见 *F855，“查询表达式中的嵌套 ORDER BY”*（https://modern-sql.com/caniuse/F855））。 BigQuery 2026-05-12 Db2 (LUW) 12.1.4 DuckDB 1.5.0 H2 2.4.240ad MariaDB 12.2.2b MySQL 9.7.0 Oracle DB 23.26.1 PostgreSQL 18ad SQL Server 2025c SQLite 3.53.0 F851，“子查询中的 ORDER BY”（https://modern-sql.com/caniuse/F851） F855，“查询表达式中的嵌套 ORDER BY”（https://modern-sql.com/caniuse/F855） 1. 某些变体（https://modern-sql.com/caniuse/F850#variants）