如何优化筛选关联表中同时存在两种flag值的SQL查询？

现有两张表a和b，需查询表a中满足以下条件的记录：在表b中至少存在一行数据满足b.a_id = a.id且flag=1，同时至少存在一行数据满足b.a_id = a.id且flag=0。

我已给出一种解决方案，但想了解是否有更优雅、高效的实现方式？

实际场景中表a有数百万行数据，且已建立相关索引，以下是简化后的示例数据：

建表与插入数据语句 #

CREATE table a (
  id int unsigned NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE b (
  id int unsigned NOT NULL AUTO_INCREMENT,
  a_id int unsigned NOT NULL,
  flag tinyint NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO a VALUES (1),(2),(3),(4);
INSERT INTO b (a_id, flag) VALUES
  (1,0),(1,1),(1,1),
  (2,0),(2,1),(2,0),
  (3,1),
  (4,0);

示例数据查询结果 #

表a数据 #

表b数据 #

预期查询结果 #

当前解决方案 #

select * from a where
    exists (select * from b where a_id = a.id and flag = 1) 
    and exists (select * from b where a_id = a.id and flag = 0);

更优雅高效的实现方式 #

1. 分组统计关联记录 #

先在表b中按a_id分组，筛选出同时包含flag=0和flag=1的a_id，再关联表a：

SELECT a.*
FROM a
JOIN (
    SELECT a_id
    FROM b
    WHERE flag IN (0, 1)
    GROUP BY a_id
    HAVING COUNT(DISTINCT flag) = 2
) AS b_filtered ON a.id = b_filtered.a_id;

这种方式只需扫描表b一次，对于大表来说能减少IO操作，前提是b表上有(a_id, flag)的联合索引，能大幅提升分组效率。

2. 利用JOIN+DISTINCT筛选 #

通过两次JOIN表b分别匹配不同flag，再去重：

SELECT DISTINCT a.*
FROM a
JOIN b b1 ON a.id = b1.a_id AND b1.flag = 1
JOIN b b2 ON a.id = b2.a_id AND b2.flag = 0;

如果b表的(a_id, flag)有索引，这种JOIN方式也能高效匹配，但需要注意DISTINCT会带来少量额外开销，适合关联记录不多的场景。

3. 原方案的优化建议 #

你当前的EXISTS方案本身已经很高效，因为EXISTS在找到第一条匹配记录后就会停止扫描，不需要遍历全表。如果要进一步优化，确保表b上创建了联合索引(a_id, flag)，这样每次子查询都能快速定位到匹配的记录，避免全表扫描。

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