如何在一对多关系中从多表筛选指定值并关联主表数据

示例表 #

主表（one_table） #

多表（many_table） #

期望结果 #

筛选出主表所有用户，关联多表中标记为已购买（purchased='y'）的产品，无对应记录则显示空值：

实现方案 #

方案1：LEFT JOIN 关联筛选 #

用LEFT JOIN保留主表全部数据，同时在关联条件中指定多表的筛选规则：

SELECT 
    o.ID,
    o.User,
    m.product_purchased AS purchased
FROM one_table o
LEFT JOIN many_table m 
    ON o.ID = m.ID 
    AND m.purchased = 'y';

• LEFT JOIN保证主表所有记录都被返回，即使多表中没有匹配项
• 将m.purchased = 'y'放在ON子句而非WHERE子句，避免过滤掉主表中无对应符合条件记录的行
• 无匹配时purchased字段会返回NULL，与期望结果一致

方案2：子查询方式 #

如果每个主表记录最多对应一条符合条件的多表记录，用子查询更简洁：

SELECT 
    ID,
    User,
    (SELECT product_purchased 
     FROM many_table m 
     WHERE m.ID = o.ID AND m.purchased = 'y') AS purchased
FROM one_table o;

• 子查询会为主表每一行匹配多表中符合条件的产品，无匹配则返回空值

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