半连接与反连接

做的事：同一对表，IN和NOT IN子查询各跑一遍

IN子查询是半连接（semijoin）的典型场景，NOT IN是反连接（antijoin）。

建表插入CREATE TABLE customers1 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); CREATE TABLE orders1 (id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, amount INT, INDEX idx_cust(customer_id)); 插入5万customers，30万orders SELECT * FROM customers1 WHERE id IN (SELECT customer_id FROM orders1 WHERE amount > 900); SELECT * FROM customers1 WHERE id NOT IN (SELECT customer_id FROM orders1 WHERE amount > 900);

验证：IN子查询走的是物化去重+索引查找

半连接的意思是"只关心customers1里有没有匹配，不关心匹配了几次"。

结果EXPLAIN SELECT * FROM customers1 WHERE id IN (SELECT customer_id FROM orders1 WHERE amount > 900) -> Nested loop inner join -> Table scan on customers1 -> Single-row index lookup on <subquery2> using <auto_distinct_key> (customer_id = customers1.id) -> Materialize with deduplication -> Filter: (orders1.amount > 900) -> Table scan on orders1

的执行方式是：先把子查询 SELECT customer_id FROM orders1 WHERE amount > 900 的结果物化成一张临时表，并且在物化过程中就去重（Materialize with deduplication），再对customers1的每一行，用自动生成的去重索引做一次"存不存在"的单行查找——这就是半连接：只判断存在性，不会因为一个customer在orders1里出现了多次而重复输出这个customer。

验证：NOT IN用的是同一套物化，只是判断反过来

反连接（antijoin）是半连接的镜像。

结果EXPLAIN SELECT * FROM customers1 WHERE id NOT IN (SELECT customer_id FROM orders1 WHERE amount > 900) -> Filter: <in_optimizer>(customers1.id, customers1.id in (select #2) is false) -> Table scan on customers1 -> Select #2 (subquery in condition; run only once) -> ... -> Materialize with deduplication -> Filter: (orders1.amount > 900) -> Table scan on orders1

同样是先物化去重子查询结果，但外层判断反了过来：customers1.id in (select #2) is false——只保留"在子查询结果里找不到"的customer，这就是反连接的语义。

验证：两个结果互补，加起来精确等于客户总数

用的最终数字做一次交叉验证。

结果semijoin(IN) 耗时: 0.0553 s, 结果: 22487 antijoin(NOT IN) 耗时: 0.0468 s, 结果: 27513 22487 + 27513 = 50000 （精确等于customers1的总行数）

分步演示：半连接和反连接的执行方式

每一步都是本文上面用EXPLAIN跑出来的原始结果。