如何修改MySQL内连接查询实现City/Cityaddition二选一的用户筛选？

问题背景 #

需要筛选出归属ID为4321的Country的所有User，已知每个User的Location仅会关联City或Cityaddition中的一个，不会同时关联两者。但当前的MySQL查询错误地只筛选出同时拥有City和Cityaddition关联的User，需修改查询，用单条语句实现二选一的关联条件。

数据库结构关系：

• User 拥有多个 Location
• Location 关联一个 City 或 一个 Cityaddition
• City / Cityaddition 关联一个 District
• District 关联一个 Region
• Region 关联一个 Country

原错误查询代码：

SELECT users.* FROM users

INNER JOIN locations ON locations.user_id = users.id

INNER JOIN cities    ON cities.id = locations.city_id
INNER JOIN districts ON districts.id = cities.district_id
INNER JOIN regions   ON regions.id = districts.region_id

INNER JOIN cityadditions           ON cityadditions.id = locations.cityaddition_id
INNER JOIN districts districts_cityadditions ON districts_cityadditions.id = cityadditions.district_id
INNER JOIN regions regions_districts       ON regions_districts.id = districts_cityadditions.region_id

WHERE (regions.country_id = 4321)

问题分析 #

原查询使用INNER JOIN同时关联了City和Cityaddition两条路径，这会强制要求Location同时存在city_id和cityaddition_id才能被匹配，完全违背了“二选一”的业务规则，因此只能筛选出同时拥有两种关联的异常用户，漏掉了正常仅关联其中一种的用户。

解决方案 #

以下两种方式均为单条SQL语句，可满足需求：

方案1：LEFT JOIN合并路径+条件过滤 #

SELECT DISTINCT users.* 
FROM users
INNER JOIN locations ON locations.user_id = users.id
-- 关联City分支
LEFT JOIN cities ON cities.id = locations.city_id
LEFT JOIN districts d1 ON d1.id = cities.district_id
LEFT JOIN regions r1 ON r1.id = d1.region_id
-- 关联Cityaddition分支
LEFT JOIN cityadditions ca ON ca.id = locations.cityaddition_id
LEFT JOIN districts d2 ON d2.id = ca.district_id
LEFT JOIN regions r2 ON r2.id = d2.region_id
-- 二选一的筛选条件
WHERE (r1.country_id = 4321 AND locations.city_id IS NOT NULL)
   OR (r2.country_id = 4321 AND locations.cityaddition_id IS NOT NULL)

• 用LEFT JOIN保留两种关联路径的所有可能，避免过滤正常用户
• 通过OR条件判断其中一条分支的国家ID符合要求，IS NOT NULL确保该分支关联有效
• DISTINCT避免同一用户因多条Location记录重复返回

方案2：UNION ALL合并独立查询 #

SELECT users.* 
FROM users
INNER JOIN locations ON locations.user_id = users.id
INNER JOIN cities ON cities.id = locations.city_id
INNER JOIN districts ON districts.id = cities.district_id
INNER JOIN regions ON regions.id = districts.region_id
WHERE regions.country_id = 4321

UNION ALL

SELECT users.* 
FROM users
INNER JOIN locations ON locations.user_id = users.id
INNER JOIN cityadditions ON cityadditions.id = locations.cityaddition_id
INNER JOIN districts ON districts.id = cityadditions.district_id
INNER JOIN regions ON regions.id = districts.region_id
WHERE regions.country_id = 4321

• 分别查询两个分支的符合条件用户，再用UNION ALL合并结果
• 由于业务规则保证用户不会同时关联两者，UNION ALL无重复数据，性能优于UNION

选择建议 #

• 若需同时获取City/Cityaddition的关联字段，方案1更便捷；
• 若仅需User数据，方案2的INNER JOIN过滤效率更高，性能更优。

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