CODEWITHSUNDEEP
mysql
Chan
Chan

Reputation: 1959

"LEFT JOIN AND" and "LEFT JOIN WHERE" which is better

orders

id
name

order_details

id
name
type
order_id

If I want to count order details which type is 1, which sql statement is better and more efficient

A

SELECT
    `orders`.`name`
FROM
    `orders`
LEFT JOIN `order_details` ON `order_details`.`order_id` = `order`.`order_id`
WHERE
    `order_details`.`type` = 1
GROUP BY `orders`.`id`

B

SELECT
    `orders`.`name`
FROM
    `orders`
LEFT JOIN `order_details` ON `order_details`.`order_id` = `order`.`order_id` AND `order_detail`.`type` = 1
GROUP BY `orders`.`id`

Upvotes: 0

Views: 46

Answers (3)

Ronak Shah
Ronak Shah

Reputation: 1549

I think its no matter you use “LEFT JOIN AND” and “LEFT JOIN WHERE"

For bulk data, You must have proper indexing to get result faster.

Upvotes: 0

Barmar
Barmar

Reputation: 781210

The reason to use a LEFT JOIN is so that the result will include rows from the first table that have no match in the second table. For this reason, you have to put all the criteria on the second table into the ON clause. If you put it into the WHERE clause, it will filter out all the rows with no match, because in these rows order_details.type will be NULL, is obviously not = 1.

In addition, if you're counting matches, you need to use IFNULL(COUNT(order_details.someColumn), 0) rather than COUNT(*). If you use COUNT(*) you'll get 1 for the rows with no match, because there's still a row in the results with data from orders and NULLs in all the order_details columns. By counting a column in the order_details table, the null values will not be counted.

Upvotes: 1

juergen d
juergen d

Reputation: 204784

Putting the condition into your where clause will turn the left join into an inner join.

Why? Because you filter the data in the where clause. But in the on condition of a join you only define what to join.

Upvotes: 2

Related Questions