带有内连接的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