如何使用LEFT JOIN查询多对多关联中不属于指定房间的用户？

可以用LEFT JOIN实现，性能远优于你当前使用的相关子查询方案，具体实现如下：

优化后的LEFT JOIN查询语句 #

SELECT u.id
FROM users u
LEFT JOIN room_users ru 
  ON u.id = ru.user_id 
  AND ru.room_id = 3
WHERE ru.user_id IS NULL;

逻辑说明 #

• 左连接会完整保留users表的所有用户记录，仅关联room_users表中同时满足「用户ID匹配」、「房间ID为3（Football房间）」的记录
• 关联后如果ru.user_id字段为NULL，说明该用户没有和Football房间的绑定记录，也就是不属于该房间，筛选这类记录即可得到预期结果2、3、4、5

性能优势说明 #

你原本的子查询属于相关子查询，会对users表的每一行都单独执行一次COUNT聚合查询，数据量越大性能衰减越严重。
而LEFT JOIN方案仅需一次关联操作即可完成查询，如果给room_users表建立(room_id, user_id)或者(user_id, room_id)的联合索引，还能进一步避免全表扫描中间表，查询效率会更高。

同性能可选方案（NOT EXISTS） #

除了LEFT JOIN之外，你也可以用NOT EXISTS写法，两者在有索引的情况下性能基本相当：

SELECT u.id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM room_users ru 
  WHERE ru.user_id = u.id AND ru.room_id = 3
);

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