MySQL 5.7 RIGHT JOIN返回行数不符合预期问题求助

表结构说明 #

表tickets #

• 销售数据表，包含字段ticket_type_id，总计4900行销售记录
• ticket_type_id共有18个有效取值：1至10、12、13、14、17、18、19、30、41（未售出的票种如11、22等无对应销售数据）

表ticket_types #

• 全量票种主表，包含字段id，与tickets表的ticket_type_id字段关联（已配置参照完整性）
• id共有35个取值：1至19、22、23、24、29、30、31、34、35、36、38、39、40、41、42、43、44

已验证的正常查询结果 #

• 执行以下UNION查询返回53行，结果符合预期；替换为UNION ALL时返回35行，结果同样正确：

select distinct ticket_type_id from tickets t 
where t.user_id != 2 
and t.status = 'active'  -- 该子查询返回18行
    UNION
-- UNION ALL
select distinct id from ticket_types tt -- 该子查询返回35行
order by 1

异常查询问题 #

执行以下RIGHT JOIN查询仅返回18行，仅包含tickets表中有匹配的记录，但预期应返回ticket_types表的全部35行数据；尝试反转表顺序使用等价的LEFT JOIN，结果仍为18行，不符合预期：

-- RIGHT JOIN - 未得到预期结果
select t.ticket_type_id, tt.id
from tickets t
RIGHT OUTER join ticket_types tt
on t.ticket_type_id  = tt.id 
where t.user_id <> 2 and t.status = 'active' 
group by 1, 2

核心原因 #

WHERE子句中针对tickets表的t.user_id <> 2和t.status = 'active'过滤条件，会对关联后的所有行生效。对于ticket_types表中没有对应tickets数据的行，t.user_id和t.status的值为NULL，而NULL与任何值的比较结果都是UNKNOWN，这类行会被WHERE子句过滤掉，最终只保留有匹配的18行。

修复方案 #

将针对tickets表的过滤条件从WHERE子句移到JOIN的ON子句中，这样只会在关联过程中过滤tickets表的匹配数据，不会影响ticket_types表中无对应销售记录的行：

select t.ticket_type_id, tt.id
from tickets t
RIGHT OUTER join ticket_types tt
on t.ticket_type_id = tt.id 
and t.user_id <> 2 and t.status = 'active'  -- 过滤条件移至ON子句
group by 1, 2

也可以使用等价的LEFT JOIN写法，逻辑完全一致：

select t.ticket_type_id, tt.id
from ticket_types tt
LEFT OUTER join tickets t
on tt.id = t.ticket_type_id 
and t.user_id <> 2 and t.status = 'active'
group by 1, 2

如果仅需要获取唯一的票种记录，无需分组去重的话，可以去掉GROUP BY，改用DISTINCT：

select distinct t.ticket_type_id, tt.id
from tickets t
RIGHT OUTER join ticket_types tt
on t.ticket_type_id = tt.id 
and t.user_id <> 2 and t.status = 'active'

内容的提问来源于stack exchange，提问作者STUART Hayes