CODEWITHSUNDEEP
mysqlsqlperformance
Anton Kozytskyi
Anton Kozytskyi

Reputation: 93

What's the difference between these two left join sql queries?

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

Upvotes: 1

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

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

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

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

Upvotes: 3

Related Questions