SQL语句逻辑执行过程和相关语法详解

共计 16400 个字符，预计需要花费 41 分钟才能阅读完成。

SQL 语句的逻辑处理顺序，指的是 SQL 语句按照一定的规则，一整条语句应该如何执行，每一个关键字、子句部分在什么时刻执行。

除了逻辑顺序，还有物理执行顺序。物理顺序是 SQL 语句真正被执行时的顺序(执行计划)，它是由各数据库系统的关系引擎中的语句分析器、优化器等等组件经过大量计算、分析决定的。

很多时候因为优化的关系，使得语句最终的物理执行顺序和逻辑顺序不同。按照逻辑顺序，有些应该先执行的过程，可能优化器会指定它后执行。但不管是逻辑顺序还是物理顺序，设计了一条 SQL 语句，语句最后返回的结果不会也不能因为物理顺序改变了逻辑顺序而改变。

其实，逻辑顺序只是为我们编写、理解 SQL 语句提供些帮助，除此之外，它毫无用处。而且，是不是真的存在一条严格且完整的执行顺序规则都是不确定的事情。虽然某些书上、网上给出了一些顺序(我个人所知道的比较权威的，是 SQL Server 的 ” 圣书 ” 技术内幕里介绍过)，但在任何一种数据库系统的官方手册上都没有关于这方面的介绍文档。

SQL Server 和 Oracle 在语句的逻辑处理顺序上是一致的，在这方面，它们严格遵守了标准 SQL 的要求，任何一个步骤都遵循了关系型数据库的范式要求。因为遵循了一些范式要求，导致标准 SQL 不允许使用某些语法。但是 MySQL、MariaDB 和它们小有不同，它们对标准 SQL 进行扩展，标准 SQL 中不允许使用的语法，在 MySQL、MariaDB 中可能可以使用，但很多时候这会违反关系模型的范式要求。

虽然本文的最初目的是介绍 MariaDB/MySQL 语句的逻辑处理顺序，但在篇幅上对标准 SQL 介绍的更多，因为它们符合规范。理解这些规范，实际上是在理解关系模型和集合模型。本文也在多处通过这两个模型来分析 为什么标准 SQL 不允许某些语法，以及为什么 MySQL 可以支持这些 ” 不标准 ” 的语法。

以 SELECT 语句为例。

如下图：

SQL 语句逻辑执行过程和相关语法详解

关于本图需要说明的是，虽然图中给出的顺序是 DISTINCT 比 ORDER BY 先执行，这也是网上流传的版本。但其实，在 DISTINCT 和 ORDER BY 之间的顺序没有严格的界限，甚至 ORDER BY 的顺序要优先于 DISTINCT。后文会分析为什么。而且刚刚去翻了下 sql server 技术内幕中关于逻辑处理顺序的内容，发现它没有对 DISTINCT 执行位置进行排序，只是在介绍 ORDER BY 时提了下 DISTINCT，我想也是因为 DISTINCT 和 ORDER BY 之间没有严格的顺序。

后面关于 MySQL 和 mariadb 的两张逻辑顺序图中，将会把 DISTINCT 和 ORDER BY 的顺序调换过来。

以下是对上述逻辑执行顺序的描述：

(1). 首先从 FROM 语句中获取要操作的表并计算笛卡尔积。如果有要联接的表，则还获取联接表。对它们计算笛卡尔积，笛卡尔积的结果形成一张虚拟表 vt1。

这里就体现了物理顺序和逻辑顺序的一个不同点：按照逻辑顺序，在执行 SQL 语句之初总是会进行笛卡尔积的计算，如果是两张或多张非常大的表，计算笛卡尔积是非常低效的行为，这是不能容忍的。所以物理顺序会进行一些优化决定，比如使用索引跳过一部分或整个笛卡尔积让计算变得很小。

(2). 对虚拟表 vt1 执行 ON 筛选语句，得到虚拟表 vt2。

(3). 根据联接类型，将保留表的外部行添加到 vt2 中得到虚拟表 vt3。

(4). 对 vt3 执行 where 条件筛选，得到虚拟表 vt4。

(5). 执行分组，得到虚拟表 vt5。

注意，分组之后，整个 SQL 的操作上下文就变成了分组列，而不再是表中的每一列，后续的一切操作都是围绕所分的组作为操作对象进行操作的。也就是说，不在分组列表中的列不能在后续步骤中使用。例如，使用 ”group by a” 对 a 列分组，那么后续的 select 列表中就不能使用 b 列，除非是对 b 列进行分组聚合运算。SQL Server、Oracle 和 MariaDB、Mysql 最大的区别就在于此步，后两者可以引用分组列以外的列。

(6). 对 vt5 执行集合操作 cube 或者 rollup，得到虚拟表 vt6。

(7). 对分组的最终结果 vt6 执行 having 筛选，得到虚拟表 vt7。

(8). 根据给定的选择列列表，将 vt7 中的选择列插入到虚拟表 vt8 中。

注意，选择列是 ”同时性操作 “，在选择列中不能使用列别名来引用列表中的其他列。例如select col1+1 as a,a+1 as b from t1 是错误的，因为 ”col1+1″ 和 ”a+1″ 之间没有执行上的先后顺序，所以它认为 ”a+1″ 中的 a 列是不存在的。

(9). 对 vt8 进行窗口分组相关的计算，得到虚拟表 vt9。

(10). 对 vt9 按照指定的列去除重复行，得到虚拟表 vt10。

这一步是将数据复制到内存中相同的临时表结构中进行的，不过该临时表多出了一个唯一性索引列用来做重复消除。

(11). 对 vt10 进行排序，排序后的表为虚拟表 vt11。

(12). 从 vt11 中根据 top 条件挑出其中满足的行，得到虚拟表 vt12。

如果没有应用 order by，则记录是无序的集合，top 挑出的行可能是随机的。也因此 top 一般要和 order by 字句一起使用。

(13). 将 vt12 从服务端返回给客户端作为最终查询结果。

如下图：

SQL 语句逻辑执行过程和相关语法详解

MariaDB 中，使用的是 LIMIT 子句实现和 TOP 子句一样的功能：限制输出行数。且它不支持 ”WITH CUBE”(直接忽略该关键词)。

但和 SQL Server、Oracle 最大的不同是对 SELECT 列表的处理。在 MS SQL 和 Oracle 中，select_list 是在 group by 和 having 子句之后才进行的，这意味着 group by 分组后，不能在 select_list 中指定非分组列(除非聚合运算)，反过来看，在 group by 中不能使用 select_list 中的别名列。

但在 MariaDB 和 MySQL 中，select_list 是在 group by 之前进行的。在 group by 中能够引用 select_list 中的列，在 select_list 中也能指定非分组列。

mariadb 和 mysql 在这一点上实际上是 ” 不规范 ” 的，因为它违背了数据库的设计范式。详细内容在后文分析。

如下图：

SQL 语句逻辑执行过程和相关语法详解

和 MariaDB 之间并没有什么区别，仅仅只是 MySQL 不支持开窗函数 over()。

但是注意，从 mysql 5.7.5 开始，已经默认设置了sql_mode=ONLY_FULL_GROUP_BY，这意味着 MySQL 默认也将遵循SQL 规范，对于那些非分组列又没有进行聚合的列，都不允许出现在 select_list 中，除非 select_list 中使用的列是主键或者唯一索引列，之所以允许这样的行为，是因为有功能依赖性决定了它可以这样做，由此保证 ” 规范性 ”。同样的，为何不规范的问题见后文。

派生表、CTE(公用表表达式，有的数据库系统支持)、视图和表函数都是表，我们常称之为 ” 表表达式 ”，只不过它们是虚拟表(这里的虚拟表和上面逻辑执行过程中产生的虚拟表 vt 不是同一个概念)。它们都必须满足成为表的条件，这也是为什么定义表表达式的时候有些语法不能使用。

从关系模型上去分析。表对应的是关系模型中的关系，表中的列对应的是关系模型中的元素。

一方面，关系和元素都需要有唯一标识的名称，因此表和列也要有名称，即使表表达式也如此。像派生表是嵌套在语句中的，无法在外部给它指定表明，因此必须为它指定一个表别名。同理，表表达式中的别名也一样，必须唯一且必须要有。

另一方面，关系中的元素是无序的，因此表和表表达式中的数据也应当是无序的。虽然有些表表达式中可以使用 ORDER BY 子句，但这时候的 ORDER BY 只是为了让 TOP/LIMIT 子句来挑选指定数量的行，并不是真的会对结果排序。也就是说表表达式挑选出来的行就像表一样，其内数据行仍然是无序的，以后访问它们的时候是按照物理存储顺序进行访问的，即使表表达式的定义语句中使用了 ORDER BY 子句。

关于数据的无序性和随机性，见下文。

这里还请区分表表达式 (虚拟表) 和逻辑执行过程中我们想象出来的虚拟表。表表达式是实实在在符合关系模型的表，即使它可能只是一条或几条语句，也不会将相关数据行进行物理的存储，但在关系引擎看来，它就是表。而逻辑执行过程中我们想象出来的虚拟表，只是为了方便理解而描述出来的，实际上不会有这样的表，它们只是按一定规则存放在内存中的一些数据行，虽然某些步骤中可能也会使用系统自建的临时表存放中途的数据，但它们不是表。

在 SQL 语句中，我们避免不了要对表、列使用别名进行引用。关于别名，需要注意两点：

(1). 定义了表别名后，在语句中对该表的引用都必须使用别名，而不能使用原表名。

(2). 引用别名时，注意查询的逻辑处理过程。在某一阶段只能引用该阶段前面阶段定义的别名，使用该阶段后才定义的别名将报错。

例如下面的两个查询语句，第一个错误原因是不能引用原表名，第二个错误是因为 WHERE 阶段不能引用 SELECT 阶段定义的字段别名。

SELECT Student.Name FROM Student AS 学生表
SELECT Name,Sex AS 性别 FROM Student WHERE 性别 = ‘ 男 ’

下面是正确的写法。

SELECT 学生表.Name FROM Student AS 学生表
SELECT Name,Sex AS 性别 FROM Student WHERE Sex = ‘ 男 ’

在关系型数据库中，必须时刻都铭记在心的是 ” 集合元素是无序 ” 的，体现在数据库中就是 ” 表中数据行是无序的 ”，除非建立了相关索引。

出于集合模型的考虑，像我们平时看到的有行、有列的二维表数据(下图左边)，更应该看作是下图右边的结合结构，因为集合是无序的。

SQL 语句逻辑执行过程和相关语法详解

由于数据无序，导致检索数据时都是按照存储时的物理顺序进行访问，如此检索得到的数据��都是随机而不保证任何顺序的，除非指定了ORDER BY 子句。而使用ORDER BY 查询得到的结果，它因为有序而不满足集合的概念。实际上ORDER BY 生成的是一个游标结果。了解SQL 的人，都知道能不用游标就尽量不用游标，因为它的效率相比符合集合概念的SQL 语句来说，要慢很多个数量级。但也不能一棍子将其打死，因为有时候使用游标确实能比较容易达到查询目标。

在 SQL 中没有使用ORDER BY 时，有不少子句的返回结果(虚拟表) 都是随机的，因为实在没办法去保证顺序，但却又要求返回数据。例如直接进行 SELECT * from t;，再例如TOP/LIMIT 子句。

纵观整个 SQL 的各个环节，不难发现很多时候获取随机行数据是不应该的，因为这种不确定性，让我们操作数据时显得更出乎意料、更危险。 因此，除非不得不显示随机数据，标准SQL 都会通过一些手段让获取随机数据的行为失败，而且在可能获取随机数据的时候，一般都会给出相关的建议和提示。

MySQL、mariadb 之所以和 sql server、oracle 的语法相差那么大，归根结底就是对待关系型数据库的范式要求和随机数据的态度不同。MySQL、mariadb 总是“ 偷奸耍滑“，在本无法满足关系型数据库范式的时候，它们总是挑选一个随机单行数据出来，让返回结果满足范式要求，最典型的就是group by 的处理方式。不过MySQL 从5.7.5 版本开始，已经逐渐走向规范化了。

这里并非是要否认mysql、mariadb 的设计模式，正所谓每个数据库系统都有自己对标准SQL 的扩展方式，MySQL 只是走了一条和标准SQL 不同的路而已。而且关系模型的范式本就是人为定义的，为何不能违反呢？甚至可以说，表所满足的范式越强，检索表时的性能越低，nosql 就没有关系模型的范式要求。

在后文，将在多处分析标准SQL 为什么不允许某些语法，同时还会提到MySQL 和mariadb 是如何“ 偷奸耍滑“ 的。

TOP 和 LIMIT 是限制输出行数量，它们挑选数据行时是随机的(根据物理访问顺序)，所以得到的结果也是随机的。因此，建议 TOP/LIMIT 和 ORDER BY 一起使用。但即使如此，仍是不安全的。例如，ORDER BY 的列中有重复值，那么 TOP/LIMIT 的时候如何决定获取哪些行呢？见如下 LIMIT 的示例(TOP 也一样)：

MariaDB [test]> select * from Student order by age;
+——+———-+——+——–+
| sid | name | age | class |
+——+———-+——+——–+
| 6 | zhaoliu | 19 | Java |
| 4 | lisi | 20 | C# |
| 8 | sunba | 20 | C++ |
| 3 | zhangsan | 21 | Java |
| 5 | wangwu | 21 | Python |
| 1 | chenyi | 22 | Java |
| 7 | qianqi | 22 | C |
| 2 | huanger | 23 | Python |
| 9 | yangjiu | 24 | Java |
+——+———-+——+——–+

MariaDB [test]> select * from Student order by age limit 9;
+——+———-+——+——–+
| sid | name | age | class |
+——+———-+——+——–+
| 6 | zhaoliu | 19 | Java |
| 4 | lisi | 20 | C# |
| 8 | sunba | 20 | C++ |
| 3 | zhangsan | 21 | Java |
| 5 | wangwu | 21 | Python |
| 1 | chenyi | 22 | Java |
| 7 | qianqi | 22 | C |
| 2 | huanger | 23 | Python |
| 9 | yangjiu | 24 | Java |
+——+———-+——+——–+

从两次查询结果中看到，即使都是对 age 进行升序排列，但 age=20 的两行前后顺序不一致，age=22 的行顺序也不一致。

因此一般会给另一个建议，为了确保数据一定是符合预期的，在 order by 中应该再加一列 (最好具有唯一性) 作为决胜属性，例如对 age 排序后再按照 sid 排序，这样就能保证返回结果不是随机的。

select * from Student order by age,sid;
select * from Student order by age,sid limit 9;

DISTINCT 子句用于消除 select_list 列的重复行，这很容易理解。大多数情况下，DISTINCT 子句在功能上都可以认为等价于 group by 子句。有些 DISTINCT 不适合做的操作，可以在 GROUP BY 中来完成。

例如下面两个 SQL 语句是等价的：

select distinct class,age from Student;
select class,age from Student group by class,age;

正因为等价，很多时候对 DISTINCT 的优化行为总是和 GROUP BY 的优化行为一致。以下是 sql server 上对上述两条语句的执行计划：

select distinct class,age from Student;
|–Sort(DISTINCT ORDER BY:([test].[dbo].[Student].[class] ASC, [test].[dbo].[Student].[age] ASC))
|–Table Scan(OBJECT:([test].[dbo].[Student]))
select class,age from Student group by class,age;
|–Sort(DISTINCT ORDER BY:([test].[dbo].[Student].[class] ASC, [test].[dbo].[Student].[age] ASC))
|–Table Scan(OBJECT:([test].[dbo].[Student]))

从结果中看到，执行 DISTINCT 去除重复行时，默认就带有了排序过程。实际上，DISTINCT 几乎总是会将数据复制到内存中的一张临时表中进行，该临时表的结构和前面得到的虚拟表字段结构几乎一致，但却多了一个唯一性索引列用来做重复消除。

但如果 DISTINCT 结合 GROUP BY 子句呢？其实不建议这么做。这里也不讨论这种问题。

既然 DISTINCT 默认就带了排序行为，那此时再指定 ORDER BY 会如何？例如下面的语句：

select distinct class,age from Student ORDER BY age desc;

在 SQL Server 中的执行计划如下：

select distinct class,age from Student ORDER BY age desc;
|–Sort(DISTINCT ORDER BY:([test].[dbo].[Student].[age] DESC, [test].[dbo].[Student].[class] ASC))
|–Table Scan(OBJECT:([test].[dbo].[Student]))

其实和前面没什么区别，无非是先对 order by 列进行排序而已。但是从这里能看出，DISTINCT 和 ORDER BY 字句其实没有严格的逻辑执行先后顺序，甚至 ORDER BY 指定的排序列还优先于 DISTINCT 的排序行为。

但是，DISTINCT 和 ORDER BY 结合时，order by 的排序列是有要求的：排序列必须是 select_list 中的列(distinct 很多时候都可以看作 group by)。例如 select distinct a,b from t order by c; 是错误的。但 MySQL 和 mariadb 又在这里进行了扩展，它们的排序列允许非 select_list 中的列。

先说标准 SQL 为何不允许使用非 select_list 中的列，这归根结底还是关系型数据库的范式问题。假如 DISTINCT 消除了部分列的重复值，最终将只返回一条重复记录，而如果使用非 select_list 的列排序，将要求返回一条重复记录的同时还要返回每个重复值对应的多条记录以便排序，而在要求范式的关系表中是无法整合这样的结果。

例如表中数据如下：

MariaDB [test]> select sid,age,class from Student order by class;
+——+——+——–+
| sid | age | class |
+——+——+——–+
| 7 | 22 | C |
| 4 | 20 | C# |
| 8 | 20 | C++ |
| 1 | 22 | Java |
| 3 | 21 | Java |
| 6 | 19 | Java |
| 9 | 24 | Java |
| 2 | 23 | Python |
| 5 | 21 | Python |
+——+——+——–+

现在对 class 列进行去重。

MariaDB [test]> select distinct class from Student order by class;
+——–+
| class |
+——–+
| C |
| C# |
| C++ |
| Java |
| Python |
+——–+

现在假设 order by 的排序列能使用 sid 进行排序。那么期待的结果将是根据如下数据进行返回的：

select distinct class from Student order by sid;
+——+——–+
| sid | class |
+——+——–+
| 7 | C |
+—————+
| 4 | C# |
+—————+
| 8 | C++ |
+—————+
| 1 | |
| 3 | Java |
| 6 | |
| 9 | |
+—————+
| 2 | |
| 5 | Python |
+——+——–+

这样的结构已经违反了关系型数据库的范式要求。因此，sql server 和 oracle 会直接对该语句报错。

但是 MySQL/mariadb 就允许在 order by 中使用非 select_list 列进行排序。它们是如何 ” 偷奸耍滑 ” 的呢？还是上面违反关系模型范式的数据结构，MySQL 和 mariadb 会从 Java 和 Python 对应的 sid 中挑选第一行(order by 已经对其排序，因此不是随机数据)，然后和 Java、Python 分别组成一行，得到如下虚拟表：

+——+——–+
| sid | class |
+——+——–+
| 7 | C |
+—————+
| 4 | C# |
+—————+
| 8 | C++ |
+—————+
| 1 | Java |
+—————+
| 2 | Python |
+——+——–+

然后将此虚拟表中非 select_list 中的列都去掉，得到最终结果。真的是最终结果吗？

MariaDB [test]> select distinct class from Student order by sid;
+——–+
| class |
+——–+
| Java |
| Python |
| C# |
| C |
| C++ |
+——–+

虽然返回的结果内容上和前面分析的一致，但是顺序却不一致，影响因素就是 ”order by sid”。

其实认真观察结果，很容易就发现它们是根据 sid 排序后再对 class 去重得到的结果。也就是说，ORDER BY 子句比 DISTINCT 子句先执行了。稍稍分析一下，这里先以 sid 排序，得到如下虚拟结果：

+——–+——+
| class | sid |
+——–+——+
| Java | 1 |
| Python | 2 |
| Java | 3 |
| C# | 4 |
| Python | 5 |
| Java | 6 |
| C | 7 |
| C++ | 8 |
| Java | 9 |
+——–+——+

再对 class 去重，得到如下虚拟结果：

+——+——–+
| sid | class |
+—————+
| 1 | |
| 3 | Java |
| 6 | |
| 9 | |
+——+——–+
| 2 | |
| 5 | Python |
+——+——–+
| 4 | C# |
+—————+
| 7 | C |
+—————+
| 8 | C++ |
+—————+

最后去掉非 select_list 中的列 sid，得到最终结果。

如果让我给 SQL 语句的逻辑执行顺序划分为两段式，我会将 ” 三八线 ” 划在 GROUP BY 这里。因为在 GROUP BY 之前甚至完全没有 GROUP BY 子句的语句部分，操作的对象都是表中的每行数据，也就是说操作的上下文环境是表的数据行。而在 GROUP BY 之后，操作的对象是组而不再是行，也就是说操作的上下文将从表中的数据行变成组。

直白一点说，GROUP BY 之前，关系引擎的目光集中在数据行的细节上，GROUP BY 之后，关系引擎的目光则集中在组上。至于每个分组中的行，对关系引擎来说是透明的，它不在乎组中行这种细节性的东西是否存在，而且按照关系模型的要求，也不应该认为它们存在。注意，这里说的是标准 SQL，而 MySQL 和 mariadb 又 ” 偷奸耍滑 ” 去了。

举个例子就很容易理解 GROUP BY 前后侧重点的变化过程。

以下是 Student 表的内容。

MariaDB [test]> select * from Student;
+——+———-+——+——–+
| sid | name | age | class |
+——+———-+——+——–+
| 1 | chenyi | 22 | Java |
| 2 | huanger | 23 | Python |
| 3 | zhangsan | 21 | Java |
| 4 | lisi | 20 | C# |
| 5 | wangwu | 21 | Python |
| 6 | zhaoliu | 19 | Java |
| 7 | qianqi | 22 | C |
| 8 | sunba | 20 | C++ |
| 9 | yangjiu | 24 | Java |
+——+———-+——+——–+

现在按照 class 进行分组。下面是分组后经过我加工的表结构：

SQL 语句逻辑执行过程和相关语法详解

其中第一列是分组得到的结果，我把它和原表的数据结合在一起了。注意，这是一个不符合关系模型范式要求的结构。

在分组前，关系引擎会对 sid、name、age 和 class 列的每一行进行筛选。但是分组后，关系引擎只看得到第一列，也就是 class 列，而 sid、name 和 age 列被直接忽略，因此无法引用它们。

关于 GROUP BY，有以下两个问题：

1. 为什么分组之后涉及到对组的操作时只允许返回标量值？

标量值即单个值，比如聚合函数返回的值就是标量值。在分组之后，组将成为表的工作中心，一个组将成为一个整体，所有涉及到分组的查询，将以组作为操作对象。组的整体是重要的，组中的个体不重要，甚至可以理解为分组后只有组的整体，即上图中左边加粗的部分，而组中的个体是透明的。

以上图中的第一条记录举一个通俗的例子。在分组以前，知道了该学生的姓名 ”chenyi” 之后，关注点可能要转化为它的主键列 sid 值 ”1″，因为主键列唯一标识每一行，知道了主键值就知道了该行的所有信息。而在分组之后，关注的中心只有分组列 class，无论是知道姓名 ”chenyi” 还是学号 ”1″ 都不是关注的重点，重点是该行记录 (集合的元素) 是属于 ”Java” 班级的。

这就能解释为什么只能以组作为操作对象并返回标量值。例如，在分组之后进行 SUM 汇总，将以 ”Java” 班作为一个汇总对象，以 ”Python” 班作为另一个汇总对象，汇总的将是每个分组的总值，而不是整个表的总值，并且汇总的值是一个标量值，不会为组中的每行都返回这个汇总值。否则就违反了关系模型的范式。

2. 为什么分组之后只能使用 GROUP BY 列表中的列，如果不在 GROUP BY 列表中，就必须进行聚合？

分组后分组列成为表的工作中心，以后的操作都必须只能为组这个整体返回一个标量值。

如果使用了非分组列表的列，将不能保证这个标量值。例如，分组后对 ”Java” 班返回了一个汇总值，假如同时要使用 sid 列和 name 列，因为这两列没有被聚合或分组，因此只能为这两列的每个值返回一行，也就是说在返回汇总标量值的同时还要求返回 ”Java” 班组中的每一行，要实现这样的结果，需要整合为如上图所示的结果，但在关系表中这是违反规范的。正如前文介绍的 DISTINCT 一样，ORDER BY 的排序列只能使用 DISTINCT 去重的 select_list 列表。

因此，分组后只能使用分组列表中的列。如果要使用非分组列表中的列，应该让它们也返回一个标量值，只有这样才能实现分组列和非分组列结果的整合。例如，下面的语句将会产生错误，因为 select_list 在 GROUP BY 阶段后执行，且 select_list 中的列没有包含在 GROUP BY 中，也没有使用聚合函数。

SELECT sid,name FROM Student GROUP BY class;

事实上从严格意义上看待这条语句，它没有实现分组的意义：既然不返回分组列的分组结果，那为什么还要进行分组呢？

其实，无论是标准 SQL 还是 MySQL、mariadb，执行 group by 子句时都会表扫描并创建一个临时表 (此处为了说明 group by 的特性，不考虑 group by 使用索引优化的情况)，这个临时表中只有 group by 的分组列，没有那些非分组列。这也是前面说 group by 之后，关系引擎的目光从行转为组的真正原因。由此，已经足够说明为什么 select_list 中不能使用非 group by 的分组列。

MySQL 和 mariadb 的 GROUP BY 有几个扩展特性 (都是标准 SQL 不支持的)：(1). 能够在 group by 中使用列别名;(2). 可以在 select_list 中使用非分组列;(3). 可以在 group by 子句中指定分组列的升序和降序排序。下面分别说明这些特性。

(1).group by中能够使用列别名。

其实对于 MySQL 和 mariadb 而言，并非是有一个专门的 select_list 筛选过程，使得筛选完成后，后续的步骤就能使用这些筛选出来的列。而是从 WHERE 子句筛选了行之后，后面所有的过程都可以对 select_list 进行检索扫描。其中 ORDER BY 子句扫描 select_list 的时候是先检索出列表达式，再检索所引用表中的列，直到找出所有的排序列；而 GROUP BY 和 HAVING 子句则是先检索表中的列，再检索列表达式，直到找出所有的分组列。因此，MySQL、mariadb 能够使用列别名。

下面两个查询的例子很能说明问题：

MariaDB [test]> set @a:=0;select sid,name,class,@a:=@a+1 as class from Student order by class;
+——+———-+——–+——-+
| sid | name | class | class |
+——+———-+——–+——-+
| 1 | chenyi | Java | 1 |
| 2 | huanger | Python | 2 |
| 3 | zhangsan | Java | 3 |
| 4 | lisi | C# | 4 |
| 5 | wangwu | Python | 5 |
| 6 | zhaoliu | Java | 6 |
| 7 | qianqi | C | 7 |
| 8 | sunba | C++ | 8 |
| 9 | yangjiu | Java | 9 |
+——+———-+——–+——-+

MariaDB [test]> set @a:=0;select sid,name,class,@a:=@a+1 as class from Student group by class;
+——+———+——–+——-+
| sid | name | class | class |
+——+———+——–+——-+
| 7 | qianqi | C | 7 |
| 4 | lisi | C# | 4 |
| 8 | sunba | C++ | 8 |
| 1 | chenyi | Java | 1 |
| 2 | huanger | Python | 2 |
+——+———+——–+——-+

上面两个查询中，表达式 @a:=@a+1 的别名为 class，和 Student 表中的 class 列重复。在第一个查询中，使用 order by 对 class 排序，由于 order by 先从 select_list 中的列表达式开始检索，因此这个排序列 class 是 @a:=@a+1 对应的列，结果也正符合此处的分析。第二个查询中，使用 group by 对 class 进行分组，因为它先检索表的字段名，因此这个分组列 class 是 Student 中的 class 列，结果也同样符合此处的分析。

但是，在标准 SQL 中这是不允许的行为。虽然在 select_list 中出现两个同名的列名称是允许的，但是在引用列别名的时候，无论是 group by 还是 order by 子句或其他子句，都认为同列名会导致二义性。标准 SQL 严格遵循 select_list 是 ” 同时性的 ”，引用列的时候无法像 mysql/mariadb 一样分先后顺序地检索 select_list。

SQL 语句逻辑执行过程和相关语法详解

(2).在 group by 子句中可以指定分组列的升序和降序排序。

无论是标准 SQL 还是 MySQL、mariadb，group by 分组的时候，都会按照分组列升序排序。只不过标准 SQL 中只能使用默认的升序，而 MySQL、mariadb 可以自行指定排序方式。

例如：

MariaDB [test]> select class from Student group by class;
+——–+
| class |
+——–+
| C |
| C# |
| C++ |
| Java |
| Python |
+——–+

SQL 语句逻辑执行过程和相关语法详解

很明显，结果中是按照分组列 class 进行升序排序的。

在 MySQL、mariadb 中可以为 group by 子句指定排序方式。而 MS SQL 和 Oracle 不允许。

MariaDB [test]> select class from Student group by class desc;
+——–+
| class |
+——–+
| Python |
| Java |
| C++ |
| C# |
| C |
+——–+

不过 MS SQL 和 Oracle 也能实现同样的功能，只需使用 ORDER BY 即可。

SQL 语句逻辑执行过程和相关语法详解

请记住，GROUP BY 子句默认会进行排序，这一点很重要。

(3).在 select_list 中可以使用非分组列。

MySQL 和 MariaDB 在这里又 ” 偷奸耍滑 ” 了。

如下查询：

MariaDB [test]> select * from Student group by class;
+——+———+——+——–+
| sid | name | age | class |
+——+———+——+——–+
| 7 | qianqi | 22 | C |
| 4 | lisi | 20 | C# |
| 8 | sunba | 20 | C++ |
| 1 | chenyi | 22 | Java |
| 2 | huanger | 23 | Python |
+——+———+——+——–+

上一小节分析了标准 SQL 的 group by 的特性，select_list 中本无法使用非分组列，但这里却能使用，为什么呢？仍然使用上一小节加工后的数据结构来说明：

SQL 语句逻辑执行过程和相关语法详解

标准 SQL 中之所以不能使用 sid、name 和 age 列，是因为 group by 的每个分组都是单行 (标量) 结果，如果使用了这些列，会违反关系模型的范式要求(一行对多行)。而 MySQL、mariadb 之所以允许，是因为它们会从重复的分组列中挑出一个随机行(注意随机这个字眼)，将它和分组列的单行组成一行，这样就满足范式要求了。

例如上图中的 Java 组对应了 4 行记录，MySQL 可能会挑 sid=1(按照物理存储顺序挑，因此结果是随机的)的那行和 Java 组构成一行，Python 组对应了 2 行记录，MySQL 可能会挑 sid= 2 的那行和 Python 构成一行。于是得到结果：

+——+———+——+——–+
| sid | name | age | class |
+——+———+——+——–+
| 7 | qianqi | 22 | C |
| 4 | lisi | 20 | C# |
| 8 | sunba | 20 | C++ |
| 1 | chenyi | 22 | Java |
| 2 | huanger | 23 | Python |
+——+———+——+——–+

MySQL 和 MariaDB 用了一种不是办法的办法解决了关系模型的范式要求问题，使得 select_list 中能够使用非分组列。但因为挑选数据的时候具有随机性，因此不太建议如此使用。除非你知道自己在做什么，或者额外使用了 ORDER BY 子句保证挑选的数据是意料之中的。

MariaDB [test]> select * from Student1 group by class order by sid desc;
+——+———-+——+——–+
| sid | name | age | class |
+——+———-+——+——–+
| 8 | sunba | 20 | C++ |
| 7 | qianqi | 22 | C |
| 4 | lisi | 20 | C# |
| 3 | zhangsan | 21 | Java |
| 2 | huanger | 23 | Python |
+——+———-+——+——–+

想必写过 GROUP BY 子句的人都很恼火选择列中不能使用非分组列，明明很想查看分组后所有行的结果，GROUP BY 却阻止了这样的行为。

万幸，还有一个 OVER()子句供我们实现目标。不过 MySQL 中不支持 OVER()子句，ms sql、Oracle 和 mariaDB(MariaDB 10.2.0 开始引入该功能)都支持，之所以 MySQL 不支持，我想是因为它的 GROUP BY 本就允许 select_list 中使用非分组列。

over()子句常被称为窗口函数或开窗函数，其实它就是进行分组，分组后也能进行聚合运算。只不过在 over()的世界里，组称为窗口。

例如，以下是按照 StudentID 列进行分组。

SQL 语句逻辑执行过程和相关语法详解

其实从上面的分组形式上看，它和 GROUP BY 分组的不同之处在于 GROUP BY 要求每个分组必须返回单行，而开窗则可以将单行数据同时分配给多个行，从而构成一个窗口。group by 的侧重点是组，而开窗的侧重点在于组中的每行。

窗口函数很强大，强大到仅仅这一个专题就可以写成一本书。本文不会对其多做描述，而是围绕本文的主题 ” 语句的逻辑执行顺序 ” 稍作分析。

over()子句是对数据行按照指定列进行开窗(划分窗口)，开窗后可以围绕每一组中的行进行操作，例如排序、聚合等等。

假如先执行 DISTINCT 去重再执行 OVER，那么去重后再对具有唯一值的列 (或多列) 进行开窗就没有任何意义。例如上图中，如果先对 StudentID 去重，那么去重后将只有 3 行，这 3 行都是唯一值，没必要再去开窗，而且这也不符合开窗的目的。

因此 OVER()是在 DISTINCT 之前完成开窗的。

另外，建议 DISTINCT 不要和 OVER()一起使用，因为这时候的 DISTINCT 根本没有任何作用，但却会消耗额外的资源。

如果真的想对某些列去重后再开窗，可以借助 GROUP BY。因为 DISTINCT 的功能基本等价于 GROUP BY，但 GROUP BY 却先执行。

虽然 SQL 语句的逻辑处理过程和真正的执行计划在有些地方会有所不同。但是理解逻辑处理过程，对学习 SQL 很有帮助。

回顾全文，不难发现 MySQL、MariaDB 对 SQL 的扩展实现了不少标准 SQL 中不允许的语法。能够实现这样的行为，是因为 MySQL/mariadb 总是通过获取一个随机行的行为保证结果满足关系模型的范式要求。也正因为这样，使得看上去 mysql/mariadb 的语法和标准 SQL 的语法没什么大区别，连逻辑执行顺序都基本一致，但它们却会对其他子句产生连带反应，导致最终的执行结果不一致。

虽然实际编写 SQL 语句的过程中，无需去在意这其中的为什么，但我个人觉得，理解它们很有帮助，毕竟关系型数据库的本质在于关系模型和集合模型。而且在我自己的体会中，在深入学习 SQL 的过程中，经常会感受到 SQL 和关系、集合之间的联系，这种感受可能不会立刻被自己发现，但回首一想，还真是那么回事。