mysql - 相关子查询的 SQL 执行顺序

Question

我有以下人事表:

+---------+----------+-------------+
| name    | dept_nbr | job_title   |
+---------+----------+-------------+
| Michael | 14       | Programmer  |
| Kumar   | 14       | Programmer  |
| Dave    | 14       | Programmer  |
| Jane    | 14       | Manager     |
| Carol   | 37       | Programmer  |
| Joe     | 37       | Programmer  |
| John    | 59       | CEO         |
+---------+----------+-------------+

问题:找到所有少于 3 个程序员的 dept_nbr(部门)。

工作查询:

SELECT DISTINCT dept_nbr
  FROM Personnel AS P1
 WHERE (SELECT COUNT(P2.dept_nbr)
          FROM Personnel AS P2
         WHERE P1.dept_nbr = P2.dept_nbr AND P2.job_title = 'Programmer') < 3;

结果:

37
59

注意事项:

部门 14 正确地不包括在内，因为它有 3 名程序员(3 等于但不少于 3)。 Department 59 的程序员为零，也正确地包含在结果中。

我的问题:

当上面的查询执行时，一个通用的 SQL 引擎是如何进行的？据我所知，SQL 执行顺序(大致)是:From、Where、Group By、Having 和 Select。那么，下面的说法正确吗？

1 - 外部查询将 Personnel 表的每一行作为 P1 传递给内部查询。

2.a - 内部查询将整个人员表扫描为 P2，逐行查找满足条件“P1.dept_nbr = P2.dept_nbr AND P2.job_title = '程序员”。

2.b –内部查询完成整个表后，它计算匹配的 dept_nbr 值并将其返回给外部查询。

3 – 在Outer Query中，如果Inner Query返回的count满足条件“WHERE (Inner Query Count Result) < 3”，则P1行对应的dept_nbr被SELECTed。

4 – 在外部查询处理的所有行之后，外部查询对结果执行 DISTINCT 并显示唯一的 dept_nbr 值。

我上面的理解对吗？具体来说，外部查询是否在最后(第 4 步)执行 DISTINCT？看起来这样，内部查询做了冗余扫描(例如，它处理了四次dept_nbr = 14，当它在第一遍中真正有答案时)。

我在带有 MySQL 5.6 的 sqlfiddle.com 上测试了上述查询。

Answer 1

When the above query executes, how does a generic SQL engine proceed? From what I have read, SQL execution order is (roughly): From, Where, Group By, Having, and Select.

这种说法——一般来说——是不正确的。 SQL 按照您描述的顺序解析。但是，执行 由优化器决定，可能与原始查询关系不大。请记住:SQL 是一种描述性语言，而不是过程性语言。它描述的是结果集，而不是计算结果集的具体步骤。

也就是说，MySQL 的执行计划比大多数其他数据库(尤其是具有更好优化器的更高级数据库)更接近于查询。而且，几乎所有数据库都将按照您为该查询描述的步骤进行。子查询中的聚合限制了优化的选择。

如果要消除冗余，请在过滤之前执行select distinct :

SELECT dept_nbr
FROM (SELECT DISTINCT dept_nbr FROM Personnel P1) P1
WHERE (SELECT COUNT(P2.dept_nbr)
       FROM Personnel AS P2
       WHERE P1.dept_nbr = P2.dept_nbr AND P2.job_title = 'Programmer'
      ) < 3;

您还可以通过聚合更简单地完成此操作:

select dept_nbr
from personnel
group by dept_nbr
having sum(job_title = 'Programmer') < 3;