验证SEMI JOIN、ANTI JOIN与非JOIN SQL写法的等价性

首先创建示例数据表：

CREATE TABLE people AS (
    SELECT * FROM VALUES
    ('david', 10), 
    ('george', 20)
    AS tmp(name, age)
);

CREATE TABLE position AS (
    SELECT * FROM VALUES
    ('george', 'c++'), 
    ('george', 'frontend')
    AS tmp(name, job)
);

SEMI JOIN 等价性分析 #

原SEMI JOIN语句：

SELECT * FROM people SEMI JOIN position ON (people.name=position.name)

与WHERE EXISTS写法的等价性 #

SELECT * FROM people WHERE EXISTS (SELECT * FROM position WHERE people.name=position.name)

完全等价。SEMI JOIN的核心是返回左表中存在右表匹配的行，且不会因右表多匹配重复左表行；EXISTS子查询同样是检查左表行是否有对应右表匹配，找到匹配即停止判断，结果逻辑和SEMI JOIN完全一致，包括重复匹配时的去重效果。

与WHERE IN写法的等价性 #

SELECT * FROM people WHERE name IN (SELECT name FROM position)

在当前示例数据下结果一致，但存在NULL值场景时会有差异：

• 若position.name包含NULL，IN子查询会因NULL的比较逻辑（NULL IN (...)结果为UNKNOWN）过滤掉相关匹配，而SEMI JOIN和EXISTS只要存在非NULL匹配就正常返回。
• 若people.name为NULL，三者行为一致，均不会选中该行。

ANTI JOIN 等价性分析 #

原ANTI JOIN语句：

SELECT * FROM people ANTI JOIN position ON (people.name=position.name)

与WHERE NOT EXISTS写法的等价性 #

SELECT * FROM people WHERE NOT EXISTS (SELECT * FROM position WHERE people.name=position.name)

完全等价。ANTI JOIN返回左表中无右表匹配的行，NOT EXISTS子查询检查左表行是否无对应右表匹配，逻辑完全一致，且不受NULL值影响。

与WHERE NOT IN写法的等价性 #

SELECT * FROM people WHERE name NOT IN (SELECT name FROM position)

在当前示例数据下结果一致，但存在NULL值场景时差异极大：

• 若position.name包含NULL，NOT IN子查询会因NULL的比较逻辑（只要子查询有一个NULL，结果就为UNKNOWN）过滤所有行，而ANTI JOIN和NOT EXISTS会正常返回左表中无匹配的行。
• 若people.name为NULL，NOT IN不会选中该行，而ANTI JOIN和NOT EXISTS会选中（因为右表无匹配的NULL）。

内容的提问来源于stack exchange，提问作者David542