MySQL中WHERE子句为何会过滤掉RIGHT JOIN的返回结果？

问题根本原因 #

RIGHT JOIN 的作用是保留右表reel_classes的所有行，即使左侧关联的reel_inventory、reel_items等表没有匹配记录。此时左表无匹配的行中，所有左表字段的值都会被置为NULL。
你将inv.current_length > 0放在WHERE子句中时，数据库会在完成所有JOIN操作后对全量结果集做过滤：NULL和任何数值做比较都会返回UNKNOWN，不符合WHERE的匹配条件，因此所有RIGHT JOIN保留的左表无匹配行都会被过滤，实际效果等价于将RIGHT JOIN转为了INNER JOIN。

正确修改方案 #

有两种常用方案可以在保留RIGHT JOIN结果的同时实现过滤逻辑：

• 方案1：将过滤条件移到RIGHT JOIN的ON子句中（更推荐）
  过滤逻辑会在表关联阶段执行，只会过滤左表的匹配行，不会影响右表全量行的保留，执行效率更高：

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 AND inv.current_length > 0;

• 方案2：在WHERE子句中兼容NULL值
  如果必须把条件写在WHERE中，可以补充对NULL的判断，保留右表无匹配的行：

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

内容的提问来源于stack exchange，提问作者Michael Buckman