SEMI_JOIN不是SQL标准语法，数据库引擎会自动对IN或EXISTS子查询选择哈希半连接算法；应优先用EXISTS替代IN以避免NULL陷阱和计划退化，并确保关联字段有索引、子查询简洁无DISTINCT或复杂表达式。
SEMI_JOIN 不是 SQL 标准语法，别在 WHERE 中写 SEMI_JOIN
SQL 标准里没有 SEMI_JOIN 关键字。你看到的“SEMI JOIN 优化”，其实是数据库引擎（比如 PostgreSQL、Spark SQL、ClickHouse）在执行 IN 或 EXISTS 子查询时，**自动选择用哈希半连接（hash semi-join）算法来加速**，而不是真的让你手写 SEMI_JOIN。强行在语句里写会报错：syntax error at or near "SEMI"。
真正可控的，是写出能让优化器识别为半连接场景的结构，并避免破坏它的判断条件。
用 EXISTS 替代 IN 防止 NULL 引发逻辑错误和计划退化
IN 子句遇到子查询返回 NULL 时，整行直接被过滤掉（哪怕匹配值存在），这是陷阱；更隐蔽的是，某些版本的 PostgreSQL 或 MySQL 在 IN (subquery) 中若子查询含 NULL 或未加索引，优化器可能放弃半连接，回退到嵌套循环或临时表扫描。
改用 EXISTS 不仅语义清晰（只关心是否存在匹配行），还更稳定触发半连接优化：
SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.status = 'active' );
-
EXISTS不受子查询中NULL值影响，逻辑安全 - 确保子查询中的关联字段（如
c.id）有索引，否则优化器大概率不选哈希半连接 - 子查询里避免
SELECT *或复杂表达式——优化器只看是否存在，多查字段无意义，还可能干扰计划
避免在 IN 右侧用子查询，尤其带聚合或 DISTINCT
写成 WHERE id IN (SELECT DISTINCT user_id FROM events) 看似简洁，但 DISTINCT 会让优化器难以估计结果集大小，常导致放弃半连接，改用 Materialize + Hash Lookup，内存开销大、速度慢。
等价但更友好的写法是：
SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM events e WHERE e.user_id = u.id );
- 去掉
DISTINCT，把去重逻辑交给连接本身（半连接天然去重） - 如果必须先聚合（比如要找“近7天登录过的人”），把子查询提前物化为 CTE 或临时表，并在该结果上建索引（PostgreSQL 支持对 CTE 结果加
CREATE INDEX ON ...的变通方式） - MySQL 8.0+ 对
IN (subquery)有 semi-join optimization flag，但需关闭optimizer_switch='semijoin=off'才生效——默认是开的，但一旦子查询含GROUP BY或窗口函数，它就自动禁用
检查执行计划，确认是否真用了 Hash Semi Join
光改写不够，得验证。在 PostgreSQL 中用 EXPLAIN (ANALYZE, BUFFERS)，关注输出里是否有 Hash Semi Join 节点；在 Spark SQL 中看 EXPLAIN 输出里的 SemiJoin 或 BuildLeft 标识。
- 如果看到
Nested Loop或Materialize，说明优化器没走半连接——回头检查子查询是否有未索引的关联条件、是否引用了外部列导致无法下推 - ClickHouse 中，
IN子查询默认转为JOIN，但若右表超 10k 行，会自动切为GLOBAL IN，引发网络广播，此时应手动改写为JOIN并用PREWHERE过滤 - 一个典型漏掉的点：父查询的
WHERE条件没下推到子查询里。比如WHERE status = 'paid' AND id IN (SELECT id FROM refunds)，应改成WHERE EXISTS (SELECT 1 FROM refunds r WHERE r.id = t.id AND r.reason IS NOT NULL)，让过滤尽早发生
半连接不是银弹——它依赖准确的统计信息和干净的关联路径。一旦子查询里混入 OR、函数索引字段、或跨库查询，优化器基本就放弃了。这时候与其硬调，不如拆成两步：先取 ID 列表（加 LIMIT 控制大小），再用 IN (val1, val2, ...)。
