mysql - 带有 INNER JOIN 的Where子句 - 作用于子集还是原始表？

Question

给出这个查询:

SELECT `promotions`.* 
FROM `promotions` 
    INNER JOIN `orders_promotions` ON `promotions`.`id` = `orders_promotions`.`promotion_id` 
WHERE `orders_promotions`.`order_id` = 3
AND `promotions`.`code` = 'blablabla'

我想知道最后一个 WHERE (WHERE `promotions`.`code` = 'blablabla') 语句是否违背了整个 promotions表，或针对 INNER JOIN 返回的集合进行操作？

Answer 1

SQL查询执行的操作顺序如下

FROM clause
JOIN clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause 

如您所见，WHERE 在执行 JOIN 操作之后出现；这表示在您上面发布的查询中 WHERE 条件将针对从 JOIN 操作获得的结果集进行操作。

您可以更改查询并将条件包含在 JOIN ON 子句中，以便在到达 WHERE 子句之前对其进行过滤

SELECT `promotions`.* 
FROM `promotions` 
INNER JOIN `orders_promotions` 
ON `promotions`.`id` = `orders_promotions`.`promotion_id` 
AND `orders_promotions`.`order_id` = 3
WHERE `promotions`.`code` = 'blablabla'

您可能想阅读This Article