semi join是数据库优化器将in或exists子查询转换后的执行计划概念，非标准sql语法；它对应select * from t1 where exists (select 1 from t2 where t1.id = t2.id)等语义，优化器在满足条件时自动选择semi-join执行策略。

SEMI JOIN在SQL中到底对应什么语法

标准SQL里没有SEMI JOIN这个关键字，它是一个执行概念，不是语法。数据库优化器在遇到IN、EXISTS这类子查询时，如果满足条件（比如右表无重复值、只关心匹配存在性），内部常转为SEMI JOIN执行计划。你不能直接写SEMI JOIN，但可以写出会被优化器识别为SEMI JOIN的语句。

用EXISTS替代IN更可靠，尤其当右表有NULL或重复值时

IN在右表含NULL时行为反直觉：只要子查询返回任意NULL，整个IN表达式结果为UNKNOWN，导致该行被过滤掉——这和“是否存在匹配”语义不符。而EXISTS完全不关心NULL，只看子查询是否返回至少一行。

• SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'US') → 若customers.id有NULL，可能漏数据
• SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.region = 'US') → 行为确定，且多数引擎会生成SEMI JOIN执行计划
• 若右表customers上id无索引，EXISTS通常比IN快，因为可提前终止；IN可能先物化全部结果再哈希匹配

不同数据库对SEMI JOIN的实际支持差异

PostgreSQL 12+ 和 SQL Server 2017+ 的查询计划里会明确标出Semi Join节点；MySQL 8.0+ 在EXISTS场景下也倾向使用FirstMatch（语义等价于SEMI JOIN）；但SQLite和旧版MySQL仍可能走嵌套循环或临时表。别依赖语法，要查EXPLAIN输出。

• PostgreSQL：EXPLAIN (ANALYZE) SELECT ... EXISTS ... 看计划里是否有Semi Join
• MySQL：EXPLAIN FORMAT=TREE 查是否有<firstmatch></firstmatch>或<materialize></materialize>标记
• 避免在IN右侧写SELECT DISTINCT——这反而可能阻止SEMI JOIN优化，因为优化器要额外保证去重语义

LEFT JOIN + IS NOT NULL不是SEMI JOIN，慎用

有人用LEFT JOIN后加WHERE right_col IS NOT NULL模拟SEMI JOIN，这是错误做法：它可能因右表多匹配而产生笛卡尔积式重复行，逻辑不等价，且无法被优化器识别为SEMI JOIN，性能通常更差。

• 错例：SELECT o.* FROM orders o LEFT JOIN customers c ON o.customer_id = c.id WHERE c.region = 'US' AND c.id IS NOT NULL → 若一个订单对应多个美国客户，会返回多行
• 对例：坚持用EXISTS，语义清晰、无歧义、引擎友好
• 如果真需要左表所有行+右表匹配标志，才考虑LEFT JOIN，但那是ANTI JOIN或OUTER JOIN场景，不是SEMI JOIN

真正影响SEMI JOIN是否生效的，是子查询结构、索引覆盖和优化器版本，不是你写了IN还是EXISTS——但EXISTS给了优化器更多推导空间，也避开了IN的NULL陷阱。