SQL从入门到出门 第9篇 初探SQL查询执行顺序

时间：2021-06-08 20:00:54

    本篇介绍SQL:2016（ISO/IEC 9075:2016）标准中定义的SQL查询语句中，各个子句之间的执行顺序，以及六种主流数据库中的实现及差异：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。

    我们在前面的章节里介绍了基本的查询语句，以及各个子句的用法。在SQL标准中，除了SELECT和FROM子句之外，其他子句都是可选项。

    接下来我们需要考虑一个问题：当查询中出现不同的子句时，它们的书写顺序；更重要的是，它们的执行顺序。我们将前文所介绍过的所有子句都写上：

    SELECT DISTINCT t1.column1 AS alias1, t2.col2, aggregate_function(column3) FROM table1 t1 JOIN table2 t1 ON t1.column1 = t2.column1 WHERE conditions GROUP BY t1.column1, t2.col2 HAVING group_condition ORDER BY t1.column1 ASC, t2.col2 DESC OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;

    以上就是SQL语法中常见的各个子句（当然不是全部子句）的书写顺序，但是它们的逻辑执行顺序却与此不同：

    1. 首先，FROM和JOIN是SQL执行的第一步，它们从逻辑上决定了接下来要操作的数据集；
    2. 其次，执行WHERE子句，对上一步的数据集进行过滤，保留满足条件的行；需要注意的是，此时还没有执行SELECT子句，WHERE条件中不能引用SELECT列表中的列别名（alias1）或者聚合函数。不过，SQLite允许WHERE条件中使用列别名（alias1）；
    3. 接下来，基于GROUP BY子句指定的表达式进行分组，分组条件有多少不同的取值，操作后的结果就有多少行；
    4. 然后，基于分组结果执行聚合函数 aggregate_function，对于每个分组取值，生成一个函数结果；如果没有分组子句，基于所有结果执行一次聚合操作；
    5. 如果查询中使用了GROUP BY，可以使用HAVING针对分组后的结果进一步进行过滤，通常是利用聚合函数的值进行过滤，如果是其他过滤条件，可以在WHERE子句中提前指定，避免无谓的操作；
    6. 接下来，SELECT子句可以选择要显示的列。如果存在GROUP BY子句，SELECT列表只能引用分组所使用的列，或者聚合函数；如果不存在GROUP BY子句，可以引用FROM和JOIN指定的表中的任何列；
    7. 如果在SELECT之后指定了DISTINCT关键字，需要针对上一步的结果集进行去重操作，过滤掉所有重复的值；
    8. 应用ORDER BY子句对上一步的结果进行最终的排序操作。如果存在GROUP BY子句或者DISTINCT关键字，只能使用SELECT列表中出现的字段进行排序；如果不存在GROUP BY子句和DISTINCT，可以使用FROM和JOIN指定的表中的任何列；
    9. 最后，OFFSET和FETCH（或者LIMIT、TOP）限定了返回的行。

    接下来，我们看一些示例。首先是外连接查询中的ON条件和WHERE条件的区别：

    SELECT d.department_name, e.job_id, AVG(e.salary) AS avarage_salary FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE d.department_name IN ('IT', 'Sales', 'Retail Sales') GROUP BY department_name, e.job_id ORDER BY d.department_name, e.job_id;

    以上语句查询的是三个部门（IT、Sales、Retail Sales）的员工平均薪水，使用左外连接是因为 Retail Sales 部门还没有员工。查询结果如下：
    SQL从入门到出门 第9篇 初探SQL查询执行顺序

    我们修改一下上面的查询条件，只查询三个部门中职位为 IT_PROG 的员工平均薪水：

    SELECT d.department_name, e.job_id, AVG(e.salary) AS avarage_salary FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id AND e.job_id = 'IT_PROG' WHERE d.department_name IN ('IT', 'Sales', 'Retail Sales') GROUP BY department_name, e.job_id ORDER BY d.department_name, e.job_id;

    先看查询结果：
    SQL从入门到出门 第9篇 初探SQL查询执行顺序

    结果中仍然显示了 3 条数据，但是 Sales 部门的平均薪水变成了 NULL。我们回头看一下查询中的职位过滤条件：

        ON d.department_id = e.department_id AND e.job_id = 'IT_PROG'

    因为JOIN ON子句先执行，而且我们使用的是左外连接，左外连接一定会返回左表中的所有行，结果并不只显示职位为 IT_PROG 的员工平均薪水。如果想要达到这个效果，可以将AND e.job_id = 'IT_PROG'放到WHERE子句中。

    第二个例子我们看一下列别名的使用：

    SELECT job_id AS job, AVG(salary) AS avarage_salary FROM employees WHERE job_id IN ('IT_PROG', 'ST_CLERK', 'SA_REP') GROUP BY job_id HAVING AVG(salary) > 5000 ORDER BY job_id;

    以上语句查询不同职位的平均薪水，并且只返回大于 5000 的记录，在SELECT、WHERE、GROUP BY以及ORDER BY中都使用了 job_id 列，同时为该列指定了别名 job。考虑一个问题，在哪些子句中可以使用别名 job；同时，在HAVING子句中是否能够使用别名 avarage_salary。

    按照 SQL 执行顺序，只有ORDER BY在SELECT之后执行，所以只能在ORDER BY子句中使用列别名：

    SELECT job_id AS job, AVG(salary) AS avarage_salary FROM employees WHERE job_id IN ('IT_PROG', 'ST_CLERK', 'SA_REP') GROUP BY job_id HAVING AVG(salary) > 5000 ORDER BY job; -- use column alias in ORDER BY clause

    但是，并非所有的数据库都遵循该规则：

    • MySQL、PostgreSQL、SQLite允许在GROUP BY中使用列别名
    • MySQL、SQLite允许在HAVING中使用列别名
    • SQLite允许在WHERE中使用列别名
    -- For MySQL, PostgreSQL, SQLite
SELECT job_id AS job, AVG(salary) AS avarage_salary FROM employees WHERE job_id IN ('IT_PROG', 'ST_CLERK', 'SA_REP') GROUP BY job -- use column alias in GROUP BY clause HAVING AVG(salary) > 5000 ORDER BY job;
    -- For MySQL, SQLite
SELECT job_id AS job, AVG(salary) AS avarage_salary FROM employees WHERE job_id IN ('IT_PROG', 'ST_CLERK', 'SA_REP') GROUP BY job HAVING avarage_salary > 5000 ORDER BY job;
    -- For SQLite only
SELECT job_id AS job, AVG(salary) AS avarage_salary FROM employees WHERE job IN ('IT_PROG', 'ST_CLERK', 'SA_REP') -- use column alias in WHERE clause GROUP BY job HAVING avarage_salary > 5000 ORDER BY job;

    第三个示例，使用了GROUP BY对于HAVING、SELECT以及ORDER BY的影响：

    -- For SQLite only
SELECT job_id, first_name, AVG(salary) AS avarage_salary FROM employees WHERE job_id IN ('IT_PROG', 'ST_CLERK', 'SA_REP') GROUP BY job_id HAVING manager_id > 100 ORDER BY hire_date;

    以上查询只有 SQLite 支持，需要注意三点：

    • SELECT中引用了 first_name，但是该字段没有出现在GROUP BY中。问题在于，我们按照职位进行分组，对于同一个职位可能存在多个员工，那么应该显示谁的名字呢？SQLite 针对每个分组随机选择一个 first_name，其他数据库会报错，MySQL比较复杂，它通过配置项（sql_mode）控制该行为，默认 only_full_group_by 也会报错（假如SELECT中引用的是 job_title，而 job_title 可以由 job_id 决定，则不会报错），否则也是随机选择一个 first_name；
    • HAVING中引用了 manager_id，但是该字段没有出现在GROUP BY中。HAVING用于过滤分组后的数据，分组之后没有 manager_id，只有分组列和聚合结果。SQLite 是个例外，仍然允许HAVING中使用这样的列；
    • ORDER BY中引用了 hire_date，但是该字段没有出现在GROUP BY中。问题同样在于，对于同一个分组职位可能存在多个员工，那么应该按照谁的雇佣日期排序呢？SQLite 针对每个分组随机选择一个 hire_date 进行排序，其他数据库会报错，MySQL同样通过配置项（sql_mode）控制该行为。

    看完留下点什么吧！欢迎评论！

    标签：

    相关文章