INNER JOIN 是实现两表主键交集最直接高效的方式，语义清晰且性能优；需确保连接字段有索引，避免 ON 中加额外过滤，多表交集宜用 CTE 或派生表分步收缩。
用 INNER JOIN 实现两个表的主键交集最直接
如果两张表都有明确的主键或唯一标识字段（比如 user_id），INNER JOIN 是语义最清晰、性能也通常最好的方式。它天然只返回两边都存在的记录，等价于集合交集。
常见错误是写成 LEFT JOIN 后加 WHERE right_table.id IS NOT NULL，这虽然结果对，但多了一次全量扫描，执行计划更差。
- 确保连接字段在两边都有索引，否则
JOIN会变慢甚至触发全表扫描 - 如果只是想取交集的 ID 列（不查其他字段），用
SELECT DISTINCT t1.id避免重复（当关联是一对多时） - 别在
ON条件里加额外过滤（如AND t2.status = 'active'），这会让交集逻辑变模糊；应先过滤再 JOIN，或用子查询封装
SELECT t1.user_id FROM users t1 INNER JOIN orders t2 ON t1.user_id = t2.user_id;
IN 子查询适合小结果集，但要注意 NULL 和性能陷阱
当一边数据量小（比如几十到几百行），用 IN 很直观。但它在遇到 NULL 值时行为反直觉：只要子查询返回任意 NULL，整个 IN 表达式结果为 UNKNOWN，该行被过滤掉——不是报错，而是静默丢失数据。
- 务必在子查询中加
WHERE col IS NOT NULL，尤其当源字段允许 NULL 时 - MySQL 5.7+ 对
IN (subquery)有优化，但若子查询结果超几千行，性能会明显下降，此时应改用JOIN或临时表 - 避免嵌套多层
IN，可读性和优化器支持都弱
SELECT user_id FROM users WHERE user_id IN ( SELECT user_id FROM orders WHERE user_id IS NOT NULL );
用 EXISTS 替代 IN 更安全，且对大表更友好
EXISTS 不受 NULL 影响，语义上是“是否存在匹配行”，和交集意图高度一致。它的执行策略通常是半连接（semi-join），MySQL 优化器往往能更好地利用索引，尤其当子查询表很大、但匹配行很少时。
- 子查询里必须相关（correlated）：比如
WHERE o.user_id = u.user_id，否则变成恒真/恒假 - 子查询中
SELECT *没问题，MySQL 只关心是否存在，不会真取字段值 - 如果子查询条件复杂，建议把过滤提前到子查询内部，而不是靠外层
WHERE
SELECT u.user_id FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.user_id );
多个表求交集不要链式 JOIN，用派生表或 CTE 控制顺序
三个及以上表求交集时，有人会写 t1 JOIN t2 JOIN t3，看似简洁，但一旦某张表无匹配数据，整条链就断了——而你可能只想知道三者共同存在的 ID。更稳妥的方式是分步收缩：先算出前两张的交集，再跟第三张比。
- MySQL 8.0+ 推荐用 CTE，逻辑清晰且可复用：
WITH common AS (...) - 5.7 及以下可用派生表（子查询 +
AS），但注意 MySQL 会物化中间结果，大数据量时慎用 - 避免在多表交集中混用
LEFT JOIN和INNER JOIN，容易误以为是“至少存在一个”，实际语义已偏离交集
WITH t1t2 AS ( SELECT user_id FROM users u INNER JOIN orders o USING(user_id) ) SELECT user_id FROM t1t2 INNER JOIN payments p USING(user_id);
交集操作看着简单，真正容易出问题的是 NULL 处理、索引缺失和多表组合时的逻辑收缩顺序——这些地方不显眼，但一出错就是数据少查、慢查甚至查错。