LEFT SEMI JOIN非标准SQL，仅MaxCompute、Hive、Spark SQL等大数据引擎原生支持；MySQL/PostgreSQL等关系型数据库不支持该语法，其“SEMI JOIN优化”是优化器自动行为，需通过EXPLAIN验证是否生效。
LEFT SEMI JOIN不是标准SQL，得看引擎支不支持
MySQL、PostgreSQL、SQL Server 原生不支持 LEFT SEMI JOIN 语法，直接写会报错 Unknown type of join 或类似提示。它主要存在于大数据计算引擎中：MaxCompute、Hive、Spark SQL、Trino（需开启配置）、ClickHouse（用 INNER JOIN + SEMI hint 模拟）。如果你在 MySQL 里搜到“SEMI JOIN 优化”，实际是优化器内部行为，不是你能手写的语法。
判断方法很简单：执行 EXPLAIN FORMAT=TREE（MySQL 8.0+）或 EXPLAIN (ANALYZE)（PostgreSQL），看 Extra 列是否出现 FirstMatch、LooseScan 或 DuplicateWeedout——有，说明优化器悄悄转成了 Semi-Join；没有，就是普通嵌套循环。
想替代IN/EXISTS？先确认语义是否等价
LEFT SEMI JOIN 的语义非常明确：只返回左表中**能在右表找到至少一行匹配**的记录，且**不重复输出右表字段**。它和 WHERE col IN (SELECT col FROM right_table) 在大多数场景下逻辑一致，但有三个关键差异点：
-
IN遇到右表子查询返回NULL，整个条件判假（SQL 标准），哪怕其他值都匹配；LEFT SEMI JOIN完全忽略右表的NULL值，只看是否存在非空匹配行 -
IN子查询若含GROUP BY、HAVING、聚合函数或相关列（如WHERE b.x = a.y），多数引擎无法自动转为 Semi-Join，仍走全量物化 - 当右表结果集极大（比如 500 万行），
IN可能先建临时哈希表再比对；LEFT SEMI JOIN（尤其配合MAPJOINhint）可直接广播右表，在内存中做快速查找，省去物化开销
MaxCompute / Hive 中怎么写才真正生效
在 MaxCompute（原 ODPS）或 Hive 中，LEFT SEMI JOIN 是显式语法，但有两个硬性限制必须遵守，否则会被降级为普通 JOIN 或报错：
- 右表只能出现在
ON子句中，不能在SELECT或WHERE里引用任何右表字段，否则报错Column not found in left table - 右表不能有
DISTINCT、LIMIT、ORDER BY等修饰，必须是基础表或简单子查询；复杂子查询建议先落临时表并加分区/分桶 - 如果右表数据量小（/*+ MAPJOIN(b) */ hint 强制广播，避免 shuffle；否则可能因内存溢出失败
正确示例：SELECT a.* FROM sales a LEFT SEMI JOIN /*+ MAPJOIN(b) */ sale_items b ON a.order_id = b.order_id AND b.status = 'shipped';
Spark SQL 里没LEFT SEMI JOIN？用INNER JOIN + SELECT DISTINCT凑合
Spark SQL 3.0+ 支持 LEFT SEMI JOIN，但老版本或某些部署环境默认关闭。如果执行报错，可用等效写法替代：
- 用
INNER JOIN+SELECT DISTINCT：虽然多一步去重，但执行计划通常能自动优化掉冗余 shuffle - 用
EXISTS子查询：Spark 会尝试将其转为 BroadcastHashJoin，效果接近 Semi-Join，前提是子查询能走索引或被广播 - 绝对不要用
IN+ 大子查询：Spark 对IN的优化较弱，容易触发CollectList导致 Driver OOM
特别注意：Spark 中 LEFT SEMI JOIN 不支持 ON 后跟多个条件带函数（如 ON a.id = CAST(b.ref_id AS STRING)），隐式转换会导致右表无法广播，必须提前清洗类型。
最常被忽略的一点：Semi-Join 的性能收益高度依赖右表是否可广播或是否命中索引。如果右表本身要扫 2TB 分区，再怎么写 SEMI 也没用——先检查右表的过滤条件能不能下推，比如加 PARTITION(sale_date='2026') 或 WHERE status IN ('shipped','delivered')。
