数据库基础体系 · 第 69/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQL Join 完整指南：Inner、Outer、Semi、Anti、算法和陷阱

Join 是 SQL 中把多个关系组合起来的核心操作。它表面上只是“按条件匹配两张表”，但要正确理解 Join，至少需要同时掌握：

匹配条件如何决定结果；
重复行和 NULL 如何影响结果；
INNER、LEFT/RIGHT/FULL OUTER、SEMI、ANTI 的逻辑差异；
SQL 的逻辑处理顺序与实际执行计划的区别；
Nested Loop、Hash Join、Merge Join 等物理算法；
为什么一条看似合理的 SQL 会产生重复、漏数据或性能问题。

本文示例以 PostgreSQL 当前稳定版本语义为主，并标注 MySQL 8.4 的差异。除非特别说明，示例均假定查询在单个数据库连接、单个 SQL 语句内执行；跨数据库、跨服务的数据一致性不由 Join 自动提供。

一、先建立一个可运行的示例数据集

下面的数据包含几个有意设计的边界：

Alice 有两个订单；
Bob 没有订单；
Carol 有一个已支付订单；
Dave 的订单金额为 NULL；
Eve 没有订单；
orders.customer_id 中有一个不存在的客户，模拟脏数据或外键未约束场景。

PostgreSQL

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    customer_id integer PRIMARY KEY,
    customer_name text NOT NULL,
    region text
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    customer_id integer,
    status text NOT NULL,
    amount numeric(10, 2),
    created_at date NOT NULL
);

INSERT INTO customers(customer_id, customer_name, region) VALUES
    (1, 'Alice', 'East'),
    (2, 'Bob',   'West'),
    (3, 'Carol', 'East'),
    (4, 'Dave',  NULL),
    (5, 'Eve',   'West');

INSERT INTO orders(order_id, customer_id, status, amount, created_at) VALUES
    (101, 1, 'PAID',    100.00, '2025-01-01'),
    (102, 1, 'CANCELLED', 50.00, '2025-01-02'),
    (103, 3, 'PAID',    200.00, '2025-01-03'),
    (104, 4, 'PAID',     NULL,  '2025-01-04'),
    (105, 99, 'PAID',    300.00, '2025-01-05');

MySQL 8.4 可以使用同样的表结构和 INSERT 语句；text、日期和聚合行为在这些示例中没有关键差异。

二、Join 的形式化模型

设左表为关系 $R$ ，右表为关系 $S$ ，Join 条件为谓词 $\theta(r,s)$ 。

最基本的 Join 可以理解为：

R \bowtie_\theta S = \{(r,s) \mid r \in R,\ s \in S,\ \theta(r,s)=TRUE\}

这里有一个重要细节：SQL 不是二值逻辑，而是三值逻辑。谓词结果可能是：

TRUE
FALSE
UNKNOWN

Join 条件只有在结果为 TRUE 时才算匹配。FALSE 和 UNKNOWN 都不匹配。

例如：

SELECT *
FROM customers c
JOIN orders o
  ON c.customer_id = o.customer_id;

对于 c.customer_id = o.customer_id：

两个值相等：TRUE；
两个非空值不等：FALSE；
任一侧为 NULL：通常是 UNKNOWN。

因此，普通等值 Join 不会把 NULL 与 NULL 配对。

2.1 重复行会产生乘法效果

Join 不是“找到一个对象后只保留一行”，而是保留所有满足条件的行对。

如果客户 Alice 有 2 个订单，则：

customers 中 Alice 的行数 = 1
orders 中 Alice 的行数 = 2
匹配后的行数 = 1 × 2 = 2

如果左表某个键有 3 行，右表相同键有 4 行，等值 Join 将产生：

3 \times 4 = 12

这正是很多“统计金额变大”“结果重复”的根源。

三、Inner Join：只保留成功匹配的行

3.1 基本语义

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status,
    o.amount
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

结果为：

customer_id	customer_name	order_id	status	amount
1	Alice	101	PAID	100.00
1	Alice	102	CANCELLED	50.00
3	Carol	103	PAID	200.00
4	Dave	104	PAID	NULL

Bob 和 Eve 没有匹配订单，因此消失；订单 105 的客户不存在，也消失。

INNER 可以省略：

SELECT ...
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id;

二者语义相同。

3.2 Join 条件不一定是等值条件

SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.amount >= 100;

这里同时要求：

客户 ID 相等；
订单金额至少为 100。

注意，o.amount >= 100 对 NULL 的结果是 UNKNOWN，所以 Dave 的订单不会匹配。

Join 条件也可以是范围条件：

SELECT *
FROM orders AS o1
JOIN orders AS o2
  ON o1.customer_id = o2.customer_id
 AND o1.order_id < o2.order_id;

这会找出同一客户的订单对。o1.order_id < o2.order_id 可以避免同一对订单反向重复出现。

3.3 不带条件的 Join 是笛卡尔积

SELECT *
FROM customers
CROSS JOIN orders;

如果客户有 5 行、订单有 5 行，结果就是 25 行。

以下写法通常等价于 CROSS JOIN：

SELECT *
FROM customers, orders;

逗号连接容易在新增表或条件时漏写 Join 条件，因此在维护代码中通常应显式使用 CROSS JOIN 或 JOIN ... ON。

四、Outer Join：保留无法匹配的一侧

Outer Join 的核心不是“匹配更多”，而是：

在保留某一侧原始行的同时，为无法匹配的另一侧列填充 NULL。

4.1 Left Outer Join

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT OUTER JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

OUTER 可以省略：

... FROM customers AS c
LEFT JOIN orders AS o ...

结果逻辑上为：

customer_id	customer_name	order_id	status
1	Alice	101	PAID
1	Alice	102	CANCELLED
2	Bob	NULL	NULL
3	Carol	103	PAID
4	Dave	104	PAID
5	Eve	NULL	NULL

其中 Bob 和 Eve 的行被保留，只是右表列为 NULL。

形式化地说，Left Join 可以分成两步：

先计算 Inner Join；
对每个没有任何匹配的左侧行，追加一行，右侧列全部为 NULL。

4.2 Right Outer Join

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
RIGHT JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Right Join 保留右表的所有行，因此订单 105 会出现，其客户列为 NULL。

多数情况下可以通过交换左右表改写为 Left Join：

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM orders AS o
LEFT JOIN customers AS c
    ON c.customer_id = o.customer_id;

这两种写法在结果关系上通常等价，但列顺序、别名和后续条件必须同步调整。

4.3 Full Outer Join

Full Outer Join 同时保留左右两侧的未匹配行：

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
FULL OUTER JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id NULLS LAST, o.order_id;

结果包括：

有订单的客户；
没有订单的客户 Bob、Eve；
没有对应客户的订单 105。

PostgreSQL 原生支持 FULL OUTER JOIN。

MySQL 8.4 没有原生 FULL OUTER JOIN 语法。可以用两个方向的 Outer Join 组合，但必须避免把已匹配的行重复一次。例如：

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id

UNION ALL

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM orders AS o
LEFT JOIN customers AS c
    ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

第二部分只保留“右侧订单没有匹配客户”的行，因此不会重复第一部分已经返回的匹配结果。

这里不能随意把 UNION ALL 改为 UNION：

UNION 会去重；
去重可能掩盖真实重复数据；
去重还需要额外的排序或哈希处理；
正确的 Full Join 模拟应通过逻辑条件避免重复，而不是依赖去重。

五、Semi Join：只判断存在性，不展开右表

Semi Join 的含义是：

返回左表中至少匹配右表一行的行，但不输出右表的列，也不因为右表存在多行匹配而复制左表行。

SQL 没有跨产品统一的 SEMI JOIN 关键字。通常使用 EXISTS 表达。

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

结果：

customer_id	customer_name
1	Alice
3	Carol
4	Dave

Alice 有两个订单，但结果中只出现一次，因为问题是“是否存在订单”，而不是“订单有几条”。

5.1 `EXISTS` 中的 `SELECT 1` 是什么含义

WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)

EXISTS 只关心子查询是否产生至少一行，不关心投影值。因此下面通常具有相同语义：

WHERE EXISTS (
    SELECT o.order_id
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)

SELECT 1 是一种清晰的表达习惯，表示“只检查存在性”。

5.2 用 Inner Join 模拟 Semi Join 的风险

下面的写法看起来也能找出有订单的客户：

SELECT c.customer_id, c.customer_name
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id;

但结果会有重复的 Alice：

customer_id	customer_name
1	Alice
1	Alice
3	Carol
4	Dave

可以加 DISTINCT：

SELECT DISTINCT c.customer_id, c.customer_name
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id;

但这改变了问题的表达方式：

先展开所有匹配对；
再对结果去重。

EXISTS 直接表达存在性，优化器也可能在找到第一条匹配后停止继续扫描右侧数据。PostgreSQL 的执行计划中可能出现 Hash Semi Join、Nested Loop Semi Join 等节点；MySQL 优化器也可能将 EXISTS 或等价的 IN 改写为 semijoin 策略。具体采用哪种策略由优化器和数据分布决定，不能仅凭 SQL 文字保证。

5.3 `IN` 与 Semi Join

以下查询在没有 NULL 陷阱时通常具有相同的存在性意图：

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE c.customer_id IN (
    SELECT o.customer_id
    FROM orders AS o
);

对于这个例子，orders.customer_id 包含 99，但不包含 NULL，所以结果与 EXISTS 一致。

不过，在相关条件复杂、需要表达多列匹配，或需要明确控制 NULL 语义时，EXISTS 通常更容易审查：

WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'PAID'
)

六、Anti Join：只保留不存在匹配的行

Anti Join 的含义是：

返回左表中没有任何右表匹配的行。

最直接的写法是 NOT EXISTS：

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

结果：

customer_id	customer_name
2	Bob
5	Eve

这就是“没有任何订单的客户”。

6.1 `LEFT JOIN ... IS NULL`

Anti Join 还可以写成：

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

其逻辑是：

Left Join 保留所有客户；
有订单的客户会填充 o.order_id；
没有订单的客户右侧列为 NULL；
用 WHERE o.order_id IS NULL 筛出未匹配客户。

这里必须选择一个“匹配订单时一定非空”的列，例如 o.order_id 是主键。如果写成：

WHERE o.amount IS NULL

就会把两类行混在一起：

没有匹配订单，右侧补出的 amount 为 NULL；
确实匹配到了订单，但该订单的 amount 本身为 NULL。

本例中 Dave 的订单金额为 NULL，因此会被错误地当成“没有订单”。

6.2 `NOT IN` 的 NULL 陷阱

下面的查询看起来像 Anti Join：

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders AS o
);

当前示例中子查询结果为：

1, 1, 3, 4, 99

没有 NULL，所以结果与 NOT EXISTS 一致。

但如果右表出现 NULL：

INSERT INTO orders(order_id, customer_id, status, amount, created_at)
VALUES (106, NULL, 'PAID', 10.00, '2025-01-06');

此时对 Bob 计算：

2 NOT IN (1, 1, 3, 4, 99, NULL)

等价于：

2 <> 1
AND 2 <> 1
AND 2 <> 3
AND 2 <> 4
AND 2 <> 99
AND 2 <> NULL

最后一项是 UNKNOWN，整个 AND 结果为 UNKNOWN。WHERE 只保留 TRUE，因此 Bob 和 Eve 都不会返回。

更可靠的写法是：

WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)

如果确实需要 NOT IN，就必须明确排除子查询中的 NULL，但这要求你确认这种排除符合业务语义：

WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders AS o
    WHERE o.customer_id IS NOT NULL
)

七、三值逻辑与 NULL：Join 中最容易被忽略的语义

7.1 普通等值比较不会匹配两个 NULL

SELECT *
FROM customers AS c
JOIN some_table AS s
  ON c.region = s.region;

如果两侧 region 都是 NULL，条件不是 TRUE，而是 UNKNOWN，所以不会匹配。

PostgreSQL 提供 null-safe 比较：

ON c.region IS NOT DISTINCT FROM s.region

它把两个 NULL 视为相等，同时把一个 NULL 与一个非空值视为不相等。

MySQL 提供 null-safe equality operator：

ON c.region <=> s.region

这不是标准 SQL 中通用的写法。跨数据库 SQL 不应直接假定两者可互换。

7.2 `COUNT(*)` 与 `COUNT(column)` 的区别

考虑：

SELECT
    c.customer_id,
    COUNT(*) AS row_count,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_amount
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;

对于 Bob：

Left Join 仍产生一行；
因此 COUNT(*) = 1；
o.order_id 是补出的 NULL，因此 COUNT(o.order_id) = 0；
SUM(o.amount) 没有非空输入，结果通常为 NULL。

如果业务要求没有订单时金额为 0，应显式写：

COALESCE(SUM(o.amount), 0)

但不要用 COUNT(*) 代替“匹配订单数”。

八、`ON` 与 `WHERE` 的区别：Outer Join 的经典陷阱

8.1 条件写在 ON 中：保留左侧所有行

查询“所有客户，以及其已支付订单”：

SELECT
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID'
ORDER BY c.customer_id, o.order_id;

结果中 Bob、Eve 仍然存在，只是没有已支付订单时右侧列为 NULL。

8.2 条件写在 WHERE 中：可能退化为 Inner Join

SELECT
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'
ORDER BY c.customer_id, o.order_id;

对 Bob 来说，Left Join 产生的 o.status 是 NULL，而：

NULL = 'PAID'

结果为 UNKNOWN，被 WHERE 删除。因此 Bob、Eve 消失了。

这条查询在结果上接近：

SELECT
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID';

8.3 什么时候应该放在 WHERE

如果业务要求：

只返回至少有一个已支付订单的客户。

那么 WHERE 是正确的，因为没有已支付订单的客户本来就不应出现。

如果业务要求：

返回所有客户，并显示其已支付订单；没有则显示空值。

那么过滤条件应放进 ON。

判断方式不是“条件习惯上放哪”，而是先明确 Outer Join 是否必须保留未匹配的左侧行。

九、SQL 逻辑处理顺序与 Join 的位置

SQL 的逻辑处理顺序不是数据库实际执行顺序，但有助于推导结果：

FROM
JOIN ... ON
外连接补齐未匹配行
WHERE
GROUP BY
聚合函数
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT/OFFSET

例如：

SELECT
    c.customer_id,
    COUNT(o.order_id) AS paid_order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID'
WHERE c.region = 'East'
GROUP BY c.customer_id
HAVING COUNT(o.order_id) > 0
ORDER BY paid_order_count DESC;

推导过程是：

FROM 找到客户和订单；
ON 只把支付状态为 PAID 且客户 ID 相等的订单视为匹配；
LEFT JOIN 为没有已支付订单的客户补出右侧 NULL；
WHERE c.region = 'East' 删除非 East 客户；
按客户分组；
COUNT(o.order_id) 统计实际匹配到的订单；
HAVING 删除支付订单数为 0 的客户；
选择输出列并排序。

WHERE 和 HAVING 都能过滤，但作用阶段不同：

WHERE 在分组前过滤行；
HAVING 在分组后过滤组。

把右表条件放到 WHERE，往往会先破坏 Outer Join 的“保留未匹配行”语义。

十、Join 之后再聚合：如何避免重复统计

10.1 直接 Join 多个一对多表会产生乘法

假设增加支付记录表：

CREATE TABLE payments (
    payment_id integer PRIMARY KEY,
    order_id integer NOT NULL,
    paid_amount numeric(10, 2) NOT NULL
);

如果一个客户有 2 个订单，每个订单有 3 条支付记录，那么：

customers
  JOIN orders
  JOIN payments

会为该客户产生：

2 \times 3 = 6

行，而不是 2 或 3 行。

例如：

SELECT
    c.customer_id,
    SUM(o.amount) AS order_total,
    SUM(p.paid_amount) AS payment_total
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
JOIN payments AS p
  ON p.order_id = o.order_id
GROUP BY c.customer_id;

如果订单和支付之间都是一对多，o.amount 会被支付记录重复展开，导致订单总额被放大。

10.2 先在各自粒度聚合

正确思路是先把每个事实表聚合到目标粒度，再 Join：

WITH order_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS order_total
    FROM orders
    GROUP BY customer_id
),
payment_totals AS (
    SELECT
        o.customer_id,
        SUM(p.paid_amount) AS payment_total
    FROM orders AS o
    JOIN payments AS p
      ON p.order_id = o.order_id
    GROUP BY o.customer_id
)
SELECT
    c.customer_id,
    COALESCE(ot.order_total, 0) AS order_total,
    COALESCE(pt.payment_total, 0) AS payment_total
FROM customers AS c
LEFT JOIN order_totals AS ot
  ON ot.customer_id = c.customer_id
LEFT JOIN payment_totals AS pt
  ON pt.customer_id = c.customer_id;

这里的关键不是 CTE 本身，而是粒度：

order_totals 每个客户最多一行；
payment_totals 每个客户最多一行；
最后 Join 不再产生订单和支付明细之间的乘法。

是否物化 CTE、是否内联 CTE，由数据库版本和优化器决定；逻辑正确性来自聚合粒度，而不是 CTE 关键字。

十一、Join 算法：逻辑 Join 如何变成物理执行

SQL 只描述结果，不规定数据库必须采用哪种算法。优化器会根据：

估算行数；
Join 条件；
索引；
可用内存；
排序状态；
统计信息；
过滤条件；
并行能力；

选择物理执行方式。

常见算法包括 Nested Loop、Hash Join 和 Merge Join。

十二、Nested Loop Join

12.1 基本过程

Nested Loop 的逻辑是：

for each row in outer input:
    scan inner input
    emit rows satisfying join condition

假设左侧输入为：

A = [a1, a2, a3]

右侧输入为：

B = [b1, b2, b3, b4]

最朴素的过程是：

取 a1，扫描 b1 b2 b3 b4
取 a2，扫描 b1 b2 b3 b4
取 a3，扫描 b1 b2 b3 b4

如果外侧有 $N$ 行，内侧有 $M$ 行，且内侧每次都完整扫描，比较成本大约为：

O(NM)

12.2 带索引的 Nested Loop

如果外侧行数较少，且内侧 Join 键有索引，过程会变成：

for each outer row:
    use index to find matching inner rows

例如：

CREATE INDEX orders_customer_id_idx
    ON orders(customer_id);

查询：

SELECT c.customer_name, o.order_id
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.customer_id = 1;

优化器可能：

通过客户主键找到 Alice；
对 orders.customer_id 使用索引查找订单；
找到 101、102；
输出结果。

此时成本更接近：

O(N \times (\text{索引定位成本} + \text{匹配行数}))

它适合：

外侧结果很小；
内侧有选择性良好的索引；
Join 后匹配行数较少。

如果外侧有数百万行，内侧索引却会被重复探测数百万次，Nested Loop 可能迅速变慢。

12.3 预取和批量优化

实际数据库可能对 Nested Loop 使用批量预取、缓存或其他变体。执行计划中常见：

PostgreSQL：Nested Loop、Index Scan、Memoize 等；
MySQL：Nested Loop 是传统 Join 执行基础，也可能结合索引条件下推、批量访问等优化。

这些是实现细节，不应把某个计划节点名称当成 SQL 语义保证。

十三、Hash Join

13.1 基本过程

Hash Join 适合等值 Join，例如：

SELECT *
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id;

典型过程分为两个阶段。

假设选择较小的 customers 作为构建侧：

阶段一：Build

对客户表每一行计算：

h = hash(c.customer\_id)

构建哈希表：

bucket[hash(1)] -> Alice
bucket[hash(2)] -> Bob
bucket[hash(3)] -> Carol
bucket[hash(4)] -> Dave
bucket[hash(5)] -> Eve

阶段二：Probe

扫描订单表，对每个订单计算：

order 101 -> hash(1) -> 找到 Alice -> 输出
order 102 -> hash(1) -> 找到 Alice -> 输出
order 103 -> hash(3) -> 找到 Carol -> 输出
order 104 -> hash(4) -> 找到 Dave -> 输出
order 105 -> hash(99) -> 找不到 -> 丢弃

理想情况下，复杂度接近：

O(|R| + |S|)

而不是逐行互相比较。

13.2 Hash Join 的限制

Hash Join 通常要求可哈希的等值条件。以下范围条件通常不能直接使用普通 Hash Join：

ON a.start_time < b.end_time

如果构建侧哈希表无法放入内存，数据库可能：

分批构建和探测；
写入临时文件；
产生磁盘 I/O；
性能明显下降。

PostgreSQL 执行计划可能显示：

Hash Join
  Hash Cond: (o.customer_id = c.customer_id)

并在内存不足时出现批次数增加。MySQL 8.x 也支持 Hash Join，但具体使用条件、成本模型和计划输出应以对应版本的 EXPLAIN 或 EXPLAIN ANALYZE 为准。

13.3 Hash Join 不等于一定更快

如果只有一个客户满足过滤条件，并且订单表有高选择性索引，带索引的 Nested Loop 可能比扫描两张表并构建哈希表更快。

算法优劣取决于输入规模和选择性，而不是名称本身。

十四、Merge Join

14.1 基本过程

Merge Join 要求两侧按照 Join 键有序，过程类似合并两个有序数组。

左侧：

1, 3, 4, 8

右侧：

1, 1, 3, 5

扫描过程：

比较左侧 1 和右侧 1，相等，输出匹配组合；
右侧还有一个 1，继续输出；
左侧移动到 3，右侧移动到 3，输出；
左侧 4 大于右侧 5？实际按顺序继续推进较小的一侧；
找不到 4 的匹配时跳过它；
最终得到所有相等键的组合。

如果输入已经有序，Merge Join 可以线性扫描：

O(|R| + |S|)

如果没有顺序，就需要先排序，成本大致为：

O(|R|\log|R| + |S|\log|S|)

14.2 什么时候有优势

Merge Join 适合：

两侧已经通过索引或上游算子有序；
两侧都较大；
需要处理大量匹配；
排序成本可接受；
后续操作也需要相同排序顺序。

PostgreSQL 可能显示：

Merge Join
  Merge Cond: (c.customer_id = o.customer_id)

MySQL 的 Join 执行策略与 PostgreSQL 不完全相同，不能假定两个数据库会为同一 SQL 选择同一种算法。

十五、Semi Join 和 Anti Join 的物理执行

Semi/Anti 是逻辑类型，不一定对应用户可直接写出的关键字。

15.1 Semi Join 的早停性质

对于：

WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)

只要找到一个订单，当前客户的存在性就已经确定，不需要继续找同一客户的其他订单。

这与普通 Inner Join 不同：

Inner Join 需要输出所有匹配订单；
Semi Join 只需要知道是否至少有一个匹配。

15.2 Anti Join 的排除性质

对于：

WHERE NOT EXISTS (...)

数据库需要证明不存在匹配。它可能：

使用索引快速确认没有匹配；
构建右侧键集合，再做排除；
使用 Hash Anti Join；
使用 Nested Loop Anti Join；
在某些情况下对右侧输入去重或利用唯一性信息。

实际采用哪种方式，取决于优化器的估算和可用访问路径。

十六、基数估计：为什么优化器可能选错 Join 算法

优化器需要估计每个中间结果有多少行。一个简化的等值 Join 估算公式是：

|R \bowtie S| \approx \frac{|R| \times |S|} {\max(\text{NDV}_R,\text{NDV}_S)}

其中：

$|R|$ ：左表行数；
$|S|$ ：右表行数；
$\text{NDV}_R$ ：左表 Join 键的不同值数量；
$\text{NDV}_S$ ：右表 Join 键的不同值数量。

例如：

R 有 1,000,000 行
S 有 500,000 行
左侧 Join 键 NDV = 100,000
右侧 Join 键 NDV = 50,000

粗略估算：

\frac{1{,}000{,}000 \times 500{,}000} {100{,}000} = 5{,}000{,}000

但这个公式隐含了分布均匀、键值重叠等假设。真实数据可能存在：

热点客户；
大量重复键；
日期和客户之间的相关性；
状态列与 Join 键的相关性；
数据倾斜；
过期统计信息。

因此估算可能严重偏离实际。

16.1 统计信息过期的表现

执行计划可能估算：

rows=100

实际却返回：

actual rows=10,000,000

后续影响包括：

错误选择 Nested Loop；
分配过小的 Hash Join 内存；
Join 顺序不合理；
中间结果爆炸；
临时文件或磁盘排序增加。

PostgreSQL 可以通过：

ANALYZE customers;
ANALYZE orders;

更新统计信息。MySQL 可使用：

ANALYZE TABLE customers;
ANALYZE TABLE orders;

统计信息更新不是万能修复；如果根因是数据倾斜、列间相关性或错误的业务粒度，还需要进一步检查模型和查询。

十七、如何阅读 Join 执行计划

PostgreSQL

先查看估算计划：

EXPLAIN
SELECT
    c.customer_name,
    o.order_id
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.region = 'East';

需要实际执行时间和实际行数时：

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    c.customer_name,
    o.order_id
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.region = 'East';

重点关注：

estimated rows 与 actual rows 的差距；
Nested Loop、Hash Join、Merge Join；
是否发生 Seq Scan；
是否有大量 Rows Removed by Filter；
Hash 节点的批次数；
Buffers 中的命中和读取；
实际执行时间是否主要花在某个子树。

EXPLAIN ANALYZE 会真正执行语句。对 SELECT 通常风险较低，但带有函数副作用、锁、临时表操作或其他非纯行为的语句必须谨慎。对 UPDATE、DELETE、INSERT 使用时，应在事务中执行并明确回滚，除非确实要提交。

MySQL 8.4

估算计划：

EXPLAIN
SELECT
    c.customer_name,
    o.order_id
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.region = 'East';

需要分析实际执行情况时，可使用该版本提供的 EXPLAIN ANALYZE 能力，但应先确认目标环境的具体版本和语法支持。不同于 PostgreSQL，MySQL 传统 EXPLAIN 输出以表访问顺序、访问类型、候选键、实际使用键、估算行数等字段为主。

常见关注点包括：

type 是否从高效的索引访问退化为大范围扫描；
possible_keys 与 key 是否符合预期；
rows 估算是否严重偏小；
Extra 中是否出现临时表、额外排序等信息；
Join 顺序是否导致大表被反复访问。

不要因为看到某个索引就认为一定使用了它；要看实际使用的 key 和实际执行成本。

十八、索引与 Join 条件

18.1 等值 Join 的常见索引

对于：

FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id

通常会考虑：

CREATE INDEX orders_customer_id_idx
    ON orders(customer_id);

但是否有收益取决于查询形状：

如果要扫描几乎所有订单，顺序扫描加 Hash Join 可能更好；
如果只查少量客户，索引 Nested Loop 可能更好；
如果还过滤状态，可能考虑组合索引：

CREATE INDEX orders_customer_status_idx
    ON orders(customer_id, status);

索引列顺序要与访问条件和选择性配合，不能机械地把所有 WHERE 列都塞进一个索引。

18.2 对 Join 列做函数可能破坏索引利用

ON LOWER(c.email) = LOWER(u.email)

如果没有对应的函数索引或生成列索引，数据库可能无法直接使用普通 email 索引。

还要注意：

字符集和排序规则；
大小写折叠规则；
隐式类型转换；
数值与字符串比较；
日期时区转换。

这些问题不仅影响性能，还可能改变匹配结果。

十九、常见 Join 陷阱

19.1 漏写 Join 条件

SELECT *
FROM customers AS c
JOIN orders AS o;

这不是“自动按同名列连接”，而是语法错误或不完整写法，具体行为取决于数据库语法。

而下面明确是笛卡尔积：

SELECT *
FROM customers AS c
CROSS JOIN orders AS o;

漏写条件的另一种表现是：

SELECT *
FROM customers AS c, orders AS o
WHERE c.region = 'East';

这里没有客户 ID 条件，因此每个 East 客户都会与所有订单组合。

19.2 把 `DISTINCT` 当作修复重复的工具

SELECT DISTINCT c.customer_id
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id;

DISTINCT 可能让结果“看起来正确”，但它没有解决：

Join 关系是否本应是一对一；
订单重复是否是脏数据；
聚合是否已经被放大；
是否可以使用 EXISTS；
是否缺少更准确的 Join 条件。

如果业务问题是“是否存在”，优先表达为 EXISTS；如果业务问题是“统计订单”，就明确统计粒度。

19.3 错把 `LEFT JOIN` 写成了 Inner Join

FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'

这会过滤掉右侧补出的 NULL 行。它可能是正确的，也可能是业务错误，取决于是否要保留没有支付订单的客户。

19.4 使用 `USING` 后列名发生变化

SELECT *
FROM customers AS c
JOIN orders AS o
USING (customer_id);

USING (customer_id) 要求两侧有同名列，并且结果中通常只保留一个合并后的 customer_id 列，而不是分别保留 c.customer_id 和 o.customer_id。

这会影响：

SELECT * 的列结构；
程序按列序号读取结果；
ORM 映射；
后续引用方式。

显式 ON c.customer_id = o.customer_id 更适合需要稳定结果列结构的接口。

19.5 `NATURAL JOIN` 隐式依赖列名

SELECT *
FROM customers
NATURAL JOIN orders;

NATURAL JOIN 会自动使用两张表中同名的列作为 Join 条件。

如果未来两张表都新增了 tenant_id、status 或其他同名列，Join 条件可能无声变化，查询结果也随之变化。这种隐式耦合通常不适合作为生产接口 SQL。

19.6 外键不存在不代表 Join 一定正确

示例中的订单 105 没有对应客户。如果数据库建立了：

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

则该数据通常无法插入；但现实系统中可能存在：

历史数据未清洗；
外键未启用；
软删除；
跨租户数据；
延迟同步；
外部系统 ID。

Inner Join 会静默丢弃孤儿行，Left/Right Join 可以帮助审计：

SELECT o.*
FROM orders AS o
LEFT JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

这就是一个 Anti Join，用来找没有对应客户的订单。

19.7 多租户系统遗漏租户条件

如果业务键只在租户内唯一，Join 必须包含租户列：

SELECT *
FROM customers AS c
JOIN orders AS o
  ON o.tenant_id = c.tenant_id
 AND o.customer_id = c.customer_id;

只写：

ON o.customer_id = c.customer_id

可能把不同租户的行错误匹配，造成数据泄露或统计污染。这不是单纯的性能问题，而是正确性和安全边界问题。

19.8 时间范围 Join 的边界错误

例如订单属于有效期内的价格：

SELECT o.order_id, p.price
FROM orders AS o
JOIN product_prices AS p
  ON p.product_id = o.product_id
 AND o.created_at >= p.valid_from
 AND o.created_at <  p.valid_to;

使用半开区间 [valid_from, valid_to) 可以避免相邻区间在边界时间同时匹配。

如果同一产品存在重叠价格区间，一条订单可能匹配多条价格记录。此时即使 Join 条件“看起来完整”，结果仍会重复；应通过约束、排他约束或数据质量检查保证区间不重叠。

二十、Join 顺序与优化器改写

对于 Inner Join，关系代数通常允许交换和结合：

R \bowtie S = S \bowtie R

(R \bowtie S) \bowtie T = R \bowtie (S \bowtie T)

因此优化器可能不按照 SQL 文字中的表顺序执行。

但 Outer Join 不能任意交换。例如：

A LEFT JOIN B ON ...

与：

B LEFT JOIN A ON ...

保留的行不同。带有 WHERE、IS NULL、聚合或非等值条件时，改写还会受到更多限制。

优化器可能进行谓词下推、Join 消除、半连接改写等操作，但只有在能够证明语义等价时才应这样做。工程师不应通过随意改变表书写顺序来“强迫”执行顺序，而应先使用执行计划验证瓶颈。

二十一、Outer Join 与谓词下推的语义边界

Inner Join 中，很多过滤条件可以在 Join 前下推：

SELECT *
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'PAID';

优化器可以先过滤支付订单，再 Join，只要结果等价。

但对 Left Join：

SELECT *
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'PAID';

如果把 o.status = 'PAID' 仅作为右表预过滤，然后仍然保留 Left Join，结果可能与原查询不同，除非同时考虑原 WHERE 对未匹配行的过滤效果。

更安全的等价重写是：

SELECT *
FROM customers AS c
JOIN (
    SELECT *
    FROM orders
    WHERE status = 'PAID'
) AS o
  ON o.customer_id = c.customer_id;

因为原始 WHERE o.status = 'PAID' 已经明确排除了没有匹配订单的客户。若原意是保留所有客户，则应写：

SELECT *
FROM customers AS c
LEFT JOIN (
    SELECT *
    FROM orders
    WHERE status = 'PAID'
) AS o
  ON o.customer_id = c.customer_id;

这两个查询意图不同，不能只根据“把过滤提前通常更快”来改写。

二十二、事务与一致性边界

单条 Join 查询看到的数据由数据库的事务隔离和快照规则决定。

在 PostgreSQL 的 MVCC 模型下，一条普通查询通常基于该语句开始时可见的数据快照。在 MySQL InnoDB 中，一致性读也受事务隔离级别和读类型影响。具体可见性仍应以目标引擎、隔离级别和语句类型为准。

重要边界包括：

Join 不会自动锁住被读取的所有行；
普通一致性读不等于 SELECT ... FOR UPDATE；
Join 结果不能自动保证后续业务操作仍基于相同数据；
跨两个数据库实例执行的 Join 不是普通 SQL Join；
跨服务查询需要应用层编排、数据同步或分布式查询系统；
两张表分别来自不同事务或不同快照时，不能假定它们具有同一时刻的一致性。

例如：

SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
FOR UPDATE;

锁定范围、是否允许对 Outer Join 的 nullable side 加锁、锁的具体行为，都与数据库和语句结构有关，不应只凭 SQL 关键字推断。需要修改前读取时，应结合目标引擎文档、执行计划和事务测试验证。

二十三、从需求选择 Join 形式

可以把常见需求映射为以下逻辑：

要输出两侧匹配明细

使用 Inner Join：

FROM customers c
JOIN orders o
  ON ...

要保留左表全部行

使用 Left Join：

FROM customers c
LEFT JOIN orders o
  ON ...

要判断左表是否至少有一条右表记录

使用 Semi Join 语义：

WHERE EXISTS (...)

要判断左表完全没有右表记录

使用 Anti Join 语义：

WHERE NOT EXISTS (...)

要输出两边所有数据，包括孤儿行

PostgreSQL 使用 Full Outer Join：

FULL OUTER JOIN

MySQL 8.4 需要用两个方向的 Outer Join 加 UNION ALL 模拟，并通过 IS NULL 只补充右侧孤儿行。

关键问题不是“哪种 Join 更高级”，而是结果是否保留未匹配行、是否需要右侧明细、是否允许重复展开。

二十四、一个完整诊断案例

假设需求是：

返回所有客户及其已支付订单总额，没有已支付订单的客户显示 0。

错误写法：

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(SUM(o.amount), 0) AS paid_total
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'
GROUP BY c.customer_id, c.customer_name;

问题是 WHERE o.status = 'PAID' 删除了没有订单的客户，因此 Bob 和 Eve 不会返回。

正确写法：

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(SUM(o.amount), 0) AS paid_total
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID'
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

结果逻辑上为：

customer_id	customer_name	paid_total
1	Alice	100.00
2	Bob	0
3	Carol	200.00
4	Dave	0
5	Eve	0

Dave 的支付订单金额为 NULL，所以 SUM 仍可能得到 NULL，最后由 COALESCE 转为 0。

如果需求改为：

只返回至少有一笔已支付订单的客户。

则可以使用 Semi Join：

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'PAID'
);

这比先 Join 明细、再 GROUP BY 或 DISTINCT 更直接地表达业务条件。

二十五、结语：先确定关系，再观察算法

正确使用 Join 的顺序应当是：

明确输出粒度：客户、订单，还是客户-订单组合；
明确是否保留未匹配的左侧或右侧行；
区分“需要明细”与“只判断存在性”；
检查 Join 键是否唯一、是否允许 NULL、是否需要租户或时间条件；
推导重复行和聚合是否会产生乘法；
再用执行计划确认数据库选择了什么物理算法；
最后根据实际基数、索引、统计信息和内存诊断性能。

INNER JOIN 解决的是匹配并展开，Outer Join 解决的是保留未匹配行，Semi Join 解决的是存在性，Anti Join 解决的是不存在性。它们不是不同的写法偏好，而是不同的关系运算。理解这一层差异，才能正确解释 NULL、重复、过滤位置和执行计划中的各种结果。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。

SQL Join 完整指南：Inner、Outer、Semi、Anti、算法和陷阱

一、先建立一个可运行的示例数据集

PostgreSQL

二、Join 的形式化模型

2.1 重复行会产生乘法效果

三、Inner Join：只保留成功匹配的行

3.1 基本语义

3.2 Join 条件不一定是等值条件

3.3 不带条件的 Join 是笛卡尔积

四、Outer Join：保留无法匹配的一侧

4.1 Left Outer Join

4.2 Right Outer Join

4.3 Full Outer Join

五、Semi Join：只判断存在性，不展开右表

5.1 EXISTS 中的 SELECT 1 是什么含义

5.2 用 Inner Join 模拟 Semi Join 的风险

5.3 IN 与 Semi Join

六、Anti Join：只保留不存在匹配的行

6.1 LEFT JOIN ... IS NULL

6.2 NOT IN 的 NULL 陷阱

七、三值逻辑与 NULL：Join 中最容易被忽略的语义

7.1 普通等值比较不会匹配两个 NULL

7.2 COUNT(*) 与 COUNT(column) 的区别

八、ON 与 WHERE 的区别：Outer Join 的经典陷阱

8.1 条件写在 ON 中：保留左侧所有行

8.2 条件写在 WHERE 中：可能退化为 Inner Join

8.3 什么时候应该放在 WHERE

九、SQL 逻辑处理顺序与 Join 的位置

十、Join 之后再聚合：如何避免重复统计

10.1 直接 Join 多个一对多表会产生乘法

10.2 先在各自粒度聚合

十一、Join 算法：逻辑 Join 如何变成物理执行

十二、Nested Loop Join

12.1 基本过程

12.2 带索引的 Nested Loop

12.3 预取和批量优化

十三、Hash Join

13.1 基本过程

阶段一：Build

阶段二：Probe

13.2 Hash Join 的限制

13.3 Hash Join 不等于一定更快

十四、Merge Join

14.1 基本过程

14.2 什么时候有优势

十五、Semi Join 和 Anti Join 的物理执行

15.1 Semi Join 的早停性质

15.2 Anti Join 的排除性质

十六、基数估计：为什么优化器可能选错 Join 算法

16.1 统计信息过期的表现

十七、如何阅读 Join 执行计划

PostgreSQL

MySQL 8.4

十八、索引与 Join 条件

18.1 等值 Join 的常见索引

18.2 对 Join 列做函数可能破坏索引利用

十九、常见 Join 陷阱

19.1 漏写 Join 条件

19.2 把 DISTINCT 当作修复重复的工具

19.3 错把 LEFT JOIN 写成了 Inner Join

19.4 使用 USING 后列名发生变化

19.5 NATURAL JOIN 隐式依赖列名

19.6 外键不存在不代表 Join 一定正确

19.7 多租户系统遗漏租户条件

19.8 时间范围 Join 的边界错误

二十、Join 顺序与优化器改写

二十一、Outer Join 与谓词下推的语义边界

二十二、事务与一致性边界

二十三、从需求选择 Join 形式

要输出两侧匹配明细

要保留左表全部行

要判断左表是否至少有一条右表记录

要判断左表完全没有右表记录

要输出两边所有数据，包括孤儿行

二十四、一个完整诊断案例

二十五、结语：先确定关系，再观察算法

系列导航与关联阅读

官方资料

5.1 `EXISTS` 中的 `SELECT 1` 是什么含义

5.3 `IN` 与 Semi Join

6.1 `LEFT JOIN ... IS NULL`

6.2 `NOT IN` 的 NULL 陷阱

7.2 `COUNT(*)` 与 `COUNT(column)` 的区别

八、`ON` 与 `WHERE` 的区别：Outer Join 的经典陷阱

19.2 把 `DISTINCT` 当作修复重复的工具

19.3 错把 `LEFT JOIN` 写成了 Inner Join

19.4 使用 `USING` 后列名发生变化

19.5 `NATURAL JOIN` 隐式依赖列名