IN 子查询和 JOIN 到底怎么选

做的事：同一个"是否存在"的判断，真实用两种写法各测一遍

代码-- IN子查询写法 SELECT count() FROM demo.jleft WHERE k IN (SELECT id FROM demo.dictsrc WHERE id < 100000); -- 等价的JOIN写法 SELECT count() FROM demo.jleft INNER JOIN (SELECT id FROM demo.dictsrc WHERE id < 100000) AS sub ON jleft.k=sub.id;

验证：真实IN子查询更轻，走的是不同的执行路径

实测 · 本机执行输出

IN子查询：真实内存 4,582,165 字节(约4.58MB)，耗时 5ms
JOIN写法：真实内存 6,840,965 字节(约6.84MB)，耗时 7ms

EXPLAIN actions=1 ... WHERE k IN (...);
→ CreatingSets (Create sets before main query execution)   ← 真实专门的Set结构
  └──Aggregating
     └──Filter
        │  Filter column: k IN subquery1
        └──ReadFromMergeTree (demo.jleft)

EXPLAIN真实揭示了两者底层机制的差异：IN子查询走的是CreatingSets——ClickHouse会把子查询的结果构建成一个专门为"这个值在不在这个集合里"这种存在性判断优化过的Set数据结构，只需要记住哪些值出现过，不需要保留子查询结果里其他列的信息；而JOIN走的是通用的哈希JOIN机制，即使这次JOIN的唯一目的也只是判断存在与否，构建出来的哈希表依然要按照JOIN的通用语义保留右表的完整行信息（虽然本文这个例子刚好子查询只SELECT了id一列，代价没有被进一步放大）。

真实的选择原则：只判断存在与否，用IN；需要拿到对方列，才用JOIN

这组真实数据说明IN子查询和JOIN不是两种可以随意互换的等价写法，而是为不同目的设计的不同工具：如果你的查询目的真实只是"筛选出左表里，某个字段的值也出现在另一个集合里的那些行"（存在性判断，不需要用到右边集合的其他字段），IN子查询用真实更轻量的Set结构就能完成，没必要用JOIN；但如果查询目的是"把两张表的数据拼接在一起，读取对方的其他字段"（比如本系列前面几篇的字典查找场景，需要真实取出name这样的字段），就必须用JOIN，因为IN子查询的Set结构天生不保留除了判断值以外的任何数据。本文的差距（4.58MB vs 6.84MB，约1.5倍）在小数据量下看起来不算夸张，但这个比例会随着子查询结果集变大而真实放大——子查询返回的行数越多、每行字段越多，JOIN因为要多保留数据而付出的真实代价也会跟着变大，而IN子查询的Set只跟去重后的值本身个数有关，跟每行原本有多少字段无关。

分步演示：从EXPLAIN看清IN和JOIN真实不同的路径

每一步都是本文上面用真实两种写法跑出来的原始结果。

真实的机制差异，而不是简单的语法偏好

这一步新验证的写法

本文用到的工具

本机 ClickHouse 26.9.4（单节点），真实的 EXPLAIN actions=1 对比CreatingSets与哈希JOIN

下一篇 · CLICKHOUSE 内部原理 43

WITH、子查询和 CTE 会不会真的只执行一次