MySQL LEFT JOIN 查询返回冗余关联行 未达预期结果问题咨询

当前查询返回多行冗余结果的核心是JOIN和WHERE的执行顺序错位：

• 执行LEFT JOIN时，会先按照inv_info.id=inv_category.id的规则，把主表和所有关联的分类行拼成临时笛卡尔积结果（也就是1行主表对3行分类，生成3行临时数据）
• 后续WHERE子句是在这个临时结果上做筛选，只要临时行满足“主表任意字段匹配搜索词 OR 分类字段匹配搜索词”就会被保留；因为主表本身的description已经匹配了usb，所以3行临时数据全部满足WHERE条件，最终就把所有分类行都返回了。

核心调整逻辑是：把关联表的分类匹配规则从WHERE子句移到JOIN的ON子句中，让数据库在拼接临时结果阶段就过滤掉不满足分类匹配规则的行，不要生成冗余的笛卡尔积数据。

方案1：精准匹配单条分类结果（完全匹配期望返回效果） #

SELECT 
  inv_info.manufacturer, 
  inv_info.model, 
  inv_info.description, 
  inv_info.quantity, 
  inv_category.categories 
FROM inv_info 
LEFT JOIN inv_category 
  ON inv_info.id = inv_category.id 
  -- 关联表的匹配规则前置到ON中，JOIN阶段就只留符合条件的分类行
  AND inv_category.categories LIKE '%usb%'
WHERE 
  -- 主表自身的搜索规则保留
  inv_info.manufacturer LIKE '%usb%' 
  OR inv_info.model LIKE '%usb%' 
  OR inv_info.description LIKE '%usb%'
  -- 分类匹配的判定改为判断关联值非空，替代原来的categories LIKE条件
  OR inv_category.categories IS NOT NULL

执行效果：JOIN阶段会自动排除inv_category中不匹配%usb%的SATA、SSD行，仅保留匹配的usb分类行和主表拼接，最终返回单条带匹配分类的结果。

方案2：多匹配场景下强制返回单条主表记录 #

如果存在一个主表行对应多个匹配分类的场景，可以通过聚合函数把所有匹配的分类值合并为单字段内容，彻底避免多行返回：

SELECT 
  inv_info.manufacturer, 
  inv_info.model, 
  inv_info.description, 
  inv_info.quantity, 
  -- 仅对JOIN阶段留下的匹配分类做聚合拼接
  GROUP_CONCAT(inv_category.categories SEPARATOR ',') AS categories
FROM inv_info 
LEFT JOIN inv_category 
  ON inv_info.id = inv_category.id 
  AND inv_category.categories LIKE '%usb%'
WHERE 
  inv_info.manufacturer LIKE '%usb%' 
  OR inv_info.model LIKE '%usb%' 
  OR inv_info.description LIKE '%usb%'
  OR inv_category.categories IS NOT NULL
GROUP BY 
  inv_info.id, inv_info.manufacturer, inv_info.model, inv_info.description, inv_info.quantity

执行效果：不管匹配到几个分类值，主表永远只返回1行，所有匹配的分类会以逗号分隔的形式放在categories字段中，不会生成冗余的笛卡尔积行。

• 不要将关联表的匹配条件直接放在WHERE的OR逻辑中不做前置过滤：WHERE是在JOIN生成全量临时数据后才执行的，此时已经生成了主表和所有分类的拼接行，仅靠OR筛选无法剔除不相关的分类行，会持续出现多行冗余结果。

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