如何编写MySQL LEFT JOIN结合IN的查询以仅获取两表匹配值？

假设我们有两个业务表（以常见关联场景为例）：

• table_a：主数据表，核心字段为a_id（主键）、product_name
• table_b：关联数据表，核心字段为b_id、a_id（关联table_a的外键）、product_detail

目标查询语句 #

如果要确保table_b的关联字段a_id一定存在于table_a，同时仅返回两表匹配的记录，可执行以下SQL：

SELECT a.a_id, a.product_name, b.b_id, b.product_detail
FROM table_a a
LEFT JOIN table_b b ON a.a_id = b.a_id
WHERE b.a_id IS NOT NULL  -- 过滤掉左表无匹配的记录，仅保留两表交集
  AND b.a_id IN (SELECT a_id FROM table_a);  -- 显式约束table_b的a_id必须在table_a中存在

补充说明 #

1. 关于LEFT JOIN转匹配结果：默认LEFT JOIN会保留table_a所有记录，加上b.a_id IS NOT NULL后，就只会返回两表有匹配的行，效果等价于INNER JOIN，完全符合你要求用LEFT JOIN的场景。
2. 关于IN子句的作用：如果你的table_b的a_id已经设置了外键约束，这个IN条件其实可以省略（外键已经保证值存在于table_a）；但如果没有外键约束，这个条件能过滤掉table_b中无效的a_id值。
3. 如果是对比非关联字段：比如要确保table_b的product_detail存在于table_a的product_name中，只需调整IN子句的查询字段：

SELECT a.a_id, a.product_name, b.b_id, b.product_detail
FROM table_a a
LEFT JOIN table_b b ON a.a_id = b.a_id
WHERE b.a_id IS NOT NULL
  AND b.product_detail IN (SELECT product_name FROM table_a);

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