Why does MySQL consecutive JOIN give an unexpected result?
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 &amp; Product first, then did the INNER JOIN on the JOINED Record &amp; 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