MySQL - Why does WHERE ignore RIGHT JOIN?

Question

I have the following MySQL query:

SELECT inv.inventory_id, inv.item_id, item.description, inv.quantity, item.class_id, class.description AS class,
class.is_spool, inv.location_id, location.description AS location, location.division_id, division.name AS division,
inv.service_date, inv.reel_number, inv.original_length, inv.current_length, inv.outside_sequential,
inv.inside_sequential, inv.next_sequential, inv.notes, inv.last_modified, inv.modified_by  
FROM reel_inventory AS inv
INNER JOIN reel_items AS item ON inv.item_id = item.item_id
INNER JOIN reel_locations AS location ON inv.location_id = location.location_id
INNER JOIN locations AS division ON location.division_id = division.location_id
RIGHT JOIN reel_classes AS class on item.class_id = class.class_id;

The query works exactly as expected as is. What I was trying to do was add a WHERE clause to this query with one qualifier. For example:

RIGHT JOIN reel_classes AS class ON item.class_id = class.class_id
WHERE inv.current_length  > 0;

When I do this, all of the results from the RIGHT JOIN are not included in the result. I've not had a ton of experience with advanced queries, but could someone explain why the RIGHT JOIN is excluded from the result set when a WHERE is used, and how to property write the query to include the RIGHT JOIN information?

Thanks in advance.

LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Similarly for RIGHT JOIN ON & right table rows. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT/RIGHT JOIN ON a WHERE, INNER JOIN or HAVING that requires a right/left [sic] table column to be not NULL removes rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. (Most of the zillion duplicates of this outer join problem are ask re left join, if you want to find more answers.) — philipxy, Sep 17 '21 at 16:57
This is a faq. Please before considering posting read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. — philipxy, Sep 17 '21 at 16:57
Does this answer your question? [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/questions/55094277/is-it-true-that-using-inner-join-after-any-outer-join-will-essentially-invalidat) — philipxy, Sep 17 '21 at 16:59

score 1 · Accepted Answer · answered Sep 17 '21 at 17:26

What you want is:

RIGHT JOIN reel_classes AS class
    ON item.class_id = class.class_id AND
       inv.current_length  > 0;

Your question is why the RIGHT JOIN turns into an INNER JOIN with the WHERE clause.

The reason is simple. For the non-matching rows, inv.current_length is NULL and this fails the comparison.

I would also suggest that you use LEFT JOIN, starting with the table where you want to keep all the rows. Most people find it much easier to understand logic that is "keep all rows in the first table" rather than "keep all rows in some table whose name will come up".

This answers both questions, "why the RIGHT JOIN is excluded from the result set when a WHERE is used, and how to property write the query to include the RIGHT JOIN information?" — xQbert, Sep 29 '21 at 13:56

MySQL - Why does WHERE ignore RIGHT JOIN?

1 Answers1