这两个左连接sql查询之间有什么区别？

There are two tables: customers and orders. Customer can have one or more orders. I would like to understand the difference in terms of execution speed. Any ideas will be useful for me to understand it better. So, thanks in advance for everybody who answers.

有两个表：客户和订单。客户可以有一个或多个订单。我想了解执行速度方面的差异。任何想法对我来说都有用，可以更好地理解它。所以，感谢所有回答的人。

1）

SELECT `customers`.* FROM `customers`
 LEFT JOIN `orders` ON `orders`.`customer_id` = `customers`.`id`
 WHERE `orders`.`status` = 1

2）

SELECT `customers`.* FROM `customers`
 LEFT JOIN `orders` ON `orders`.`customer_id` = `customers`.`id` AND `orders`.`status` = 1

2 个解决方案

#1

First one will act as INNER JOIN. When you filter the right table in where clause the non matching NULL records from right table will be filtered, because anything = NULL will fail

第一个将充当INNER JOIN。当您在where子句中过滤右表时，将过滤右表中不匹配的NULL记录，因为任何= NULL将失败

Second one will work as LEFT JOIN. In join condition AND orders.status = 1 says the records to be joined with left table

第二个将作为LEFT JOIN工作。在连接条件AND中，orders.status = 1表示要与左表连接的记录

Regarding the question about performance, as I mentioned above both the queries are not same so you cannot compare the performance really

关于性能的问题，正如我上面提到的，两个查询都不相同，所以你无法真正比较性能

#2

The first turns into an INNER JOIN, because for non-matching rows, orders.customer_id is NULL. Hence the WHERE clause will filter all of them out.

第一个变为INNER JOIN，因为对于不匹配的行，orders.customer_id为NULL。因此，WHERE子句将过滤掉所有这些子句。

#1