MySQL 查询语句执行顺序以及On与Where条件过滤的区别

阿里云-轻量应用服务器

下面是一段MySQL 查询语句代码:

SELECT
DISTINCT <字段列表>
FROM <左表>
<连接方式> JOIN <右表>
ON <连接条件>
WHERE <过滤条件>
GROUP BY <分组字段>
HAVING <包含条件>
ORDER BY <排序方式>
LIMIT <限制行数>[, <偏移行数>]

以上的伪代码,有联结、过滤、分组、排序等,基本覆盖了查询语句的所有子句。

然而在SQL中,处理的第一个子句是FROM子句,而SELECT在SQL查询中首先出现的子句将在以后进行处理。SQL查询的逻辑处理涉及的阶段如下:

  1. FROM子句
  2. ON条款
  3. OUTER子句
  4. WHERE子句
  5. GROUP BY子句
  6. HAVING子句
  7. SELECT子句
  8. DISTINCT子句
  9. ORDER BY子句
  10. LIMIT条款

SQL在查询各个阶级分别干了什么?

(1)FROM 阶段

FROM阶段标识出查询的来源表,并处理表运算符。在涉及到联接运算的查询中(各种join),主要有以下几个步骤:

  1. 求笛卡尔积。不论是什么类型的联接运算,首先都是执行交叉连接(cross join),求笛卡儿积,生成虚拟表VT1-J1。
  2. ON筛选器。这个阶段对上个步骤生成的VT1-J1进行筛选,根据ON子句中出现的谓词进行筛选,让谓词取值为true的行通过了考验,插入到VT1-J2。
  3. 添加外部行。如果指定了outer join,还需要将VT1-J2中没有找到匹配的行,作为外部行添加到VT1-J2中,生成VT1-J3。

经过以上步骤,FROM阶段就完成了。概括地讲,FROM阶段就是进行预处理的,根据提供的运算符对语句中提到的各个表进行处理(除了join,还有apply,pivot,unpivot)

(2)WHERE阶段

WHERE阶段是根据<where_predicate>中条件对VT1中的行进行筛选,让条件成立的行才会插入到VT2中。

(3)GROUP BY阶段

GROUP阶段按照指定的列名列表,将VT2中的行进行分组,生成VT3。最后每个分组只有一行。

(4)HAVING阶段

该阶段根据HAVING子句中出现的谓词对VT3的分组进行筛选,并将符合条件的组插入到VT4中。

(5)SELECT阶段

这个阶段是投影的过程,处理SELECT子句提到的元素,产生VT5。这个步骤一般按下列顺序进行:

  1. 计算SELECT列表中的表达式,生成VT5-1。
  2. 若有DISTINCT,则删除VT5-1中的重复行,生成VT5-2
  3. 若有TOP,则根据ORDER BY子句定义的逻辑顺序,从VT5-2中选择签名指定数量或者百分比的行,生成VT5-3

(6)ORDER BY阶段

根据ORDER BY子句中指定的列明列表,对VT5-3中的行,进行排序,生成游标VC6.


提示和注意事项

  • SELECT先前步骤不能使用列表中创建的别名。强制执行此限制是因为在SELECT评估子句之前出现的子句(例如WHERE子句)时,可能尚未确定列值。
  • 在某些数据库(例如MySQL)中,使用和子句中SELECT允许使用列表中创建的别名,即使这些子句出现在子句之前(并且在其之前评估)。GROUP BYHAVINGSELECT
  • 表达式别名不能被同一SELECT列表中的其他表达式使用。这是因为评估表达式的逻辑顺序无关紧要,并且无法保证。例如,此SELECT子句可能无法按预期工作,因此不受支持:SELECT a + 1 AS x, x + 1 AS y
  • 使用an时INNER JOIN,如果在WHERE子句或ON子句中指定逻辑表达式,则无关紧要。这是正确的,因为ON和之间没有逻辑差异WHERE(除了使用OUTER JOINGROUP BY ALL选项时)。
  • 使用时该DISTINCT子句是多余的GROUP BY。因此,它不会从记录集中删除任何行。

on条件与where条件的区别

由上述SQL查询执行的顺序可以看出,假设使用左连接(left join)时,

  1. on条件是在生成临时表(VT1)时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
  2. where条件是在临时表(VT1)生成好后,再对临时表进行过滤的条件,进而生成临时表(VT2)。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

on条件不是最终过滤, 因为后面left join还可能添加回来, 而where才是最终过滤.只有当使用外连接(left, right)时, on 和 where 才有这个区别, 如果用inner join, 在哪里制定都一样, 因为on 之后就是where, 中间没有其它步骤.

总结一下:

在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里

参考资料

发表评论

电子邮件地址不会被公开。 必填项已用*标注