多表关联含NULL值的SQL查询问题求助

修正后的SQL语句 #

SELECT 
    mh.house_name,
    mfm.member_name,
    mp.config_value AS cats
FROM my_houses mh
-- 左连接家庭成员表，排除成员名称为NULL的无效记录，无成员的房屋显示NULL
LEFT JOIN my_family_members mfm 
    ON mh.house_id = mfm.house_id
    AND mfm.member_name IS NOT NULL
-- 左连接仅包含猫咪的宠物数据，无猫的房屋显示NULL
LEFT JOIN my_pets mp 
    ON mh.house_id = mp.house_id 
    AND mp.config_key = 'cat'
ORDER BY mh.house_name;

执行后将得到你期望的结果：

+---------------+-------------+--------+
| house_name    | member_name | cats   |
+---------------+-------------+--------+
| My Blue house | Ash         | Tom    |
| My Blue house | Ash         | Jerry  |
| My Red house  | Mel         | NULL   |
| My Green house| NULL        | Bubbles|
| My Pink House | NULL        | NULL   |
+---------------+-------------+--------+

原SQL的问题分析 #

1. 主表选择错误：以my_pets作为查询起点，只能获取到有宠物的房屋，直接漏掉了无宠物的房屋（如My Pink House）。
2. 连接类型错误：使用内连接JOIN关联my_family_members，会过滤掉没有家庭成员的房屋，同时排除了有成员但无猫的房屋（如My Red House）。
3. 过滤条件位置错误：WHERE config_key = 'cat'放在主查询中，会把左连接后生成的NULL猫记录过滤掉，导致无猫的房屋无法显示。

核心逻辑说明 #

• 以my_houses为主表，确保所有房屋都被列出；
• 左连接my_family_members时添加mfm.member_name IS NOT NULL，排除成员名称为NULL的无效记录，无成员的房屋自动显示NULL；
• 左连接my_pets时将config_key = 'cat'放在连接条件中，而非WHERE子句，这样不会过滤掉无猫的房屋，而是显示NULL；
• 最终结果覆盖所有房屋的组合场景：有成员有猫、有成员无猫、无成员有猫、无成员无猫。

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