IN 有一种特殊的情况为 semi-join (参考 2), 即当 IN 中子查询未使用外部表达式中的变量, 即子查询独立于外部查询, IN 中的子查询会单独执行并建立一个对于特定的列具有 UNIQUE 约束的临时的表, 随后对此表使用类似 JOIN 的操作, 对于原表的每一行都判断是否存在于此表中, 由于临时表具有 UNIQUE 约束(甚至可能建立索引), 从而使得每获得一行结果的复杂度降低到 n*log 水平. 如果原表的对应列也具有索引, 那么甚至会到达 log*log 的水平.
EXISTS 则是对于每一行进行子查询的运算, 某些情况下可能效率要高于简单的 JOIN, 某些情况下则会效率等同于 semi-join 的 IN 操作, 例如以下两个操作效率等同(参考 1)
select ...
from table_a
where col_1 in (
select col_1
from table_b
);
select ...
from table_a
where exists (
select 1
from table_b
where table_a.col_1 = table_b.col_1
)
结论: join 在无索引的表中表现较差, 原因是 semi-join 允许通过一个临时表进行聚合并复用此表进行匹配, 而 join 则有可能要通过两步(移除重复行和额外建立一个哈希表, 见参考 1, 此处涉及参考 1 中的测试语句然而由于本人懒惰尚未能在上面说明)来实现相同的操作.
对于 semi-join 的直观解释建议直接阅读参考 2.