Why does MySQL consecutive JOIN give an unexpected result?

Question

I had 3 tables which were Category, Product and Vendor. Then I tried to make a consecutive join 3 tables in 1 query to count the product for each category.

If a product or it's vendor is deleted, it will be excluded from the list. Moreover, if there was no product available, I still need to keep the category with the 0 count result. Because all tables are using the soft-delete strategy, I need to filter deleted items from the Query to get a proper count result. Then, the problem start from here.

A quick sample is below

Category
--|----------|----------|
id|name      |deleted_at|
--|----------|----------|
 1|Food      |      NULL|
 2|Stationery|      NULL|

Product
--|-------|---------|------|----------|
id|cate_id|vendor_id|name  |deleted_at|
--|-------|---------|------|----------|
 1|      1|        1|Bread |      NULL|
 2|      1|        2|Milk  |      NULL|
 5|      2|        2|Pencil|      NULL|

Vendor
--|-------|----------|
id|name   |deleted_at|
--|-------|----------|
 1|Woolies|      NULL|
 2|Cole   |2020-01-18|

Query1 gave an unexpected result

SELECT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product
FROM
    category c
LEFT JOIN product p 
    ON p.cate_id = c.id AND p.deleted_at IS NULL
INNER JOIN vendor v
    ON v.id = p.vendor_id AND v.deleted_at IS NULL
GROUP BY c.id;

cate_id|cate_name|total_product|
-------|---------|-------------|
      1|Food     |            1|

Query2 gave a correct response.

SELECT
    c.id AS cate_id,
    c.name AS cate_name,
    COUNT(p.id) AS total_product
FROM
    category c
LEFT JOIN (SELECT p1.* FROM product p1 INNER JOIN vendor v ON v.id = p1.vendor_id AND v.deleted_at IS NULL) 
    AS p ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id;

cate_id|cate_name |total_product|
-------|----------|-------------|
      1|Food      |            1|
      2|Stationery|            0|

I'm not likely sure but I guess the Query1 did the LEFT JOIN between Category & Product tables first, then did the INNER JOIN on the JOINED Record & Vendor, finally leading to an unexpected result. Am I correct?

Is there any way to achieve this purpose without using a sub-query?

Answer 1

Use

SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Category c
LEFT JOIN ( Product p 
            INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL 
            ) ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;

or

SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Product p 
INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL 
RIGHT JOIN Category c ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;

fiddle

---

I guess the Query1 did the LEFT JOIN between Category & Product first, then did the INNER JOIN on the JOINED Record & Vendor, finally leading to an unexpected result. Am I correct?

Yes, if at least one outer joining is present then tables scanning is performed in query text ordering (like STRAIGHT_JOIN is used).

---

It would be really thankful if you can suggest a name or a link so that I can do some research about it.

MySQL 8.0 Reference Manual / ... / JOIN Clause