PHP小码农

Appearance

外连接（LEFT / RIGHT / FULL JOIN） ​

本节讲解外连接（Outer Join）的三种形态、ON 与 WHERE 在左连接中的位置差异、IS NULL 反连接模式、多表混合连接的判定规则，以及外连接的行数估算方法。学完本节，你将能准确预测外连接的输出行数，避免"外连接退化为内连接"的高频错误。

前置知识

阅读本节前，建议先了解：内连接（INNER JOIN）

基础概念 ​

外连接（Outer Join） 在匹配行之外，还会保留某一侧（或两侧）不匹配的行，未匹配的列用 NULL 补齐。

概念术语说明
左外连接LEFT OUTER JOIN保留左表全部行，右表无匹配则补 NULL
右外连接RIGHT OUTER JOIN保留右表全部行，左表无匹配则补 NULL
全外连接FULL OUTER JOIN两侧全部保留，任一侧无匹配则补 NULL
补空NULL Padding用 NULL 填充不匹配侧的列
反连接Anti Join找出"在右表不存在"的行，LEFT JOIN ... IS NULL 模式
半连接Semi Join只判断"是否存在"，不复制行
驱动侧Outer Side保留全部行的那一侧
补空侧Inner Side可能产生 NULL 的那一侧

三种外连接的语义差异可以用"是否保留不匹配行"概括：

连接类型左表不匹配行右表不匹配行补空侧行数（大致）
INNER JOIN丢弃丢弃无匹配组合数
LEFT JOIN保留丢弃右表≥ 内连接行数
RIGHT JOIN丢弃保留左表≥ 内连接行数
FULL JOIN保留保留两侧≥ max(左行数, 右行数)

关键认识：外连接的结果行数一定不少于对应的内连接。 因为它在内连接结果上追加了不匹配行。

语法与用法 ​

外连接的标准语法：

text
SELECT 列列表
FROM 左表 [AS 别名]
  { LEFT | RIGHT | FULL } [OUTER] JOIN 右表 [AS 别名]
  ON 连接条件
[WHERE 过滤条件];

准备本节贯穿使用的示例表：

sql
-- 用户表：dave 没有任何订单
CREATE TABLE users (
  id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  TEXT    NOT NULL,
  city  TEXT    NOT NULL
);

-- 订单表：含游客订单（user_id 为 NULL）与孤儿订单（user_id = 99）
CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT           NOT NULL UNIQUE,
  user_id    INTEGER,
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT           NOT NULL,
  created_at DATE           NOT NULL
);

INSERT INTO users (name, city) VALUES
  ('alice', 'beijing'),
  ('bob',   'shanghai'),
  ('carol', 'shenzhen'),
  ('dave',  'chengdu');           -- 无订单用户

INSERT INTO orders (order_no, user_id, amount, status, created_at) VALUES
  ('SO-1001', 1,    607.00, 'paid',    DATE '2026-01-05'),
  ('SO-1002', 2,    199.00, 'paid',    DATE '2026-01-08'),
  ('SO-1003', 1,    518.00, 'paid',    DATE '2026-02-09'),
  ('SO-1004', 3,    195.00, 'pending', DATE '2026-02-02'),
  ('SO-1005', 2,    490.00, 'paid',    DATE '2026-02-15'),
  ('SO-1006', NULL,  89.00, 'paid',    DATE '2026-03-01'),
  ('SO-1007', 99,   350.00, 'pending', DATE '2026-03-11');

三种外连接的输出对照：

sql
-- 1. LEFT JOIN：保留全部用户
SELECT u.name, o.order_no, o.amount
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
ORDER BY u.name, o.order_no;

-- 2. RIGHT JOIN：保留全部订单（视角相反）
SELECT u.name, o.order_no, o.amount
FROM users AS u
RIGHT JOIN orders AS o ON o.user_id = u.id
ORDER BY o.order_no;

-- 3. FULL JOIN：两侧全保留
SELECT u.name, o.order_no, o.amount
FROM users AS u
FULL JOIN orders AS o ON o.user_id = u.id
ORDER BY u.name NULLS LAST, o.order_no;

LEFT JOIN 的输出：

text
 name  | order_no | amount
-------+----------+--------
 alice | SO-1001  | 607.00
 alice | SO-1003  | 518.00
 bob   | SO-1002  | 199.00
 bob   | SO-1005  | 490.00
 carol | SO-1004  | 195.00
 dave  | (null)   | (null)
(6 rows)

FULL JOIN 的输出（7 行用户侧 + 2 行孤儿订单）：

text
 name  | order_no | amount
-------+----------+--------
 alice | SO-1001  | 607.00
 alice | SO-1003  | 518.00
 bob   | SO-1002  | 199.00
 bob   | SO-1005  | 490.00
 carol | SO-1004  | 195.00
 dave  | (null)   | (null)
 (null)| SO-1006  |  89.00
 (null)| SO-1007  | 350.00
(8 rows)

详细说明 ​

LEFT 与 RIGHT 的等价互换 ​

RIGHT JOIN 与 LEFT JOIN 只是视角不同：把左右表互换，RIGHT JOIN 就变成 LEFT JOIN。

sql
-- 以下两条语句完全等价
SELECT u.name, o.order_no
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

SELECT u.name, o.order_no
FROM orders AS o
RIGHT JOIN users AS u ON o.user_id = u.id;

生产代码建议统一使用 LEFT JOIN：阅读时只需关注"保留左边的表"，不需要来回切换视角，可减少理解成本。只有在表达"以右表为主体"且改写会显著降低可读性时才用 RIGHT JOIN。

ON 与 WHERE 的位置差异（外连接最大的坑） ​

这是外连接中最高频的错误：把右表的过滤条件写到 WHERE 中，会让 LEFT JOIN 静默退化为 INNER JOIN。

  • ON 中的条件在连接时生效，只决定"哪些右表行可以配对"；不匹配的左表行仍被保留（补 NULL）。
  • WHERE 中的条件在连接之后生效，会直接过滤掉结果行；右表列为 NULL 的行因为条件求值为 NULL 而被丢弃。
sql
-- 写法 A：条件写在 ON —— 保留所有用户
SELECT u.name, o.order_no, o.status
FROM users AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id
 AND o.status = 'paid'
ORDER BY u.name, o.order_no;
text
 name  | order_no | status
-------+----------+--------
 alice | SO-1001  | paid
 alice | SO-1003  | paid
 bob   | SO-1002  | paid
 bob   | SO-1005  | paid
 carol | (null)   | (null)
 dave  | (null)   | (null)
(6 rows)
sql
-- 写法 B：条件写在 WHERE —— 未付款用户被整体过滤，退化为内连接
SELECT u.name, o.order_no, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY u.name, o.order_no;
text
 name  | order_no | status
-------+----------+--------
 alice | SO-1001  | paid
 alice | SO-1003  | paid
 bob   | SO-1002  | paid
 bob   | SO-1005  | paid
(4 rows)

判定法则：写 LEFT JOIN 时，如果 WHERE 中出现对右表列的判定，先问自己"这一列可能是 NULL 吗"。若可能，则该条件会让外连接退化；应把它移到 ON，或在 WHERE 中显式允许 NULL。

sql
-- 若本意是"只保留有已付款订单的用户"，两种正确写法：

-- 写法一：条件放 ON，再用 WHERE 校验存在性
SELECT u.name, o.order_no, o.status
FROM users AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id AND o.status = 'paid'
WHERE o.id IS NOT NULL                 -- 明确要求"确实存在已付款订单"
ORDER BY u.name, o.order_no;

-- 写法二：先过滤再连接（语义最清晰）
WITH paid AS (SELECT * FROM orders WHERE status = 'paid')
SELECT u.name, p.order_no, p.status
FROM users AS u
LEFT JOIN paid AS p ON p.user_id = u.id
WHERE p.id IS NOT NULL
ORDER BY u.name, p.order_no;

若希望"保留没有已付款订单的用户"，则不要加 WHERE o.id IS NOT NULL：

sql
-- 保留所有用户，仅列出其已付款订单（无则补空）
SELECT u.name, o.order_no, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'paid'
ORDER BY u.name, o.order_no;

IS NULL 反连接模式（找不存在的记录） ​

反连接（Anti Join） 的经典写法：先左连接，再用 IS NULL 筛出右侧未匹配的行。

sql
-- 找出从未下单的用户
SELECT u.id, u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL                    -- 右表主键为 NULL 说明没配上
ORDER BY u.id;
text
 id | name
----+-------
  4 | dave
(1 row)

判定列必须选右表的非空列（通常是主键）。WHERE o.id IS NULL 之所以可靠，是因为真实数据行里 o.id 不可能为 NULL。

sql
-- 错误！用可空列做判定会混入真实 NULL 行
SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.user_id IS NULL;      -- 游客订单 SO-1006 的 user_id 本身就是 NULL

-- 正确：用右表主键（非空列）
SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

反连接也可以用 NOT EXISTS 表达，语义更直接，且通常执行计划更优：

sql
-- 与上面等价的 NOT EXISTS 写法
SELECT u.id, u.name
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.user_id = u.id
)
ORDER BY u.id;

多表混合连接的判定规则 ​

外连接是左结合的，多个外连接串联时，NULL 补空会逐层传播。

sql
-- 三表：用户 LEFT JOIN 订单 LEFT JOIN 商品
-- 用户 → 订单 → 明细，逐层保留左侧
SELECT u.name, o.order_no, i.product_id, i.qty
FROM users       AS u
LEFT JOIN orders      AS o ON o.user_id = u.id
LEFT JOIN order_items AS i ON i.order_id = o.id
ORDER BY u.name, o.order_no, i.id;

混合内连接与外连接时，内连接会"截断"NULL 的传播：

sql
-- 内连接放在外连接之后，会把补空行滤掉
SELECT u.name, o.order_no, i.product_id
FROM users AS u
LEFT JOIN orders      AS o ON o.user_id = u.id
JOIN      order_items AS i ON i.order_id = o.id;   -- 内连接：要求必须有明细
-- 结果等价于：用户 INNER JOIN 订单 INNER JOIN 明细

因此，ON 中显式写条件、并在混合场景下加括号控制结合方向，是更稳妥的写法：

sql
-- 用括号明确结合顺序：先做 (users LEFT JOIN orders)，再与 items 做外连接
SELECT u.name, o.order_no, i.product_id
FROM (users AS u
      LEFT JOIN orders AS o ON o.user_id = u.id)
LEFT JOIN order_items AS i ON i.order_id = o.id
ORDER BY u.name, o.order_no;

FULL JOIN 的用途与代价 ​

FULL JOIN 保留两侧全部行，最典型的用途是双向数据对照（数据质量检查）。

sql
-- 找出"没有订单的用户"与"找不到用户的订单"
SELECT
  coalesce(u.name, '(无此用户)')     AS user_name,
  coalesce(o.order_no, '(无订单)')   AS order_no,
  o.user_id
FROM users AS u
FULL JOIN orders AS o ON o.user_id = u.id
WHERE u.id IS NULL OR o.id IS NULL
ORDER BY user_name, order_no;
text
 user_name   | order_no | user_id
-------------+----------+---------
 (无此用户)   | SO-1006  |  (null)
 (无此用户)   | SO-1007  |      99
 dave        | (无订单)  |  (null)
(3 rows)

FULL JOIN 的代价：

方面说明
计划空间不满足交换律，优化器难以重排，多表 FULL JOIN 计划空间大
算法选择通常走 Hash Full Join 或 Merge Full Join，两侧都要完整扫描
行数控制结果行数难以预估，聚合前必须确认基数
使用频率生产中主要用于核对、对照、迁移校验，不用于常规业务查询

外连接的结果行数估算 ​

准确预估行数是写好外连接的前提。原则如下：

text
LEFT JOIN 行数 = 内连接匹配行数 + 左表未匹配行数

若已知右表连接键的基数，可用公式判断是否发生行膨胀：

场景左侧每行匹配右表行数结果行数特征
一对零0左表行数不变，右表列补 NULL
一对多n > 1左表行被复制 n 次，行数扩大
多对多两者都不唯一行数可能远超任一表行数
sql
-- 事前估算：检查连接键唯一性
SELECT
  count(*)                       AS total_rows,
  count(DISTINCT user_id)        AS distinct_keys,
  count(*) / greatest(count(DISTINCT user_id), 1) AS avg_per_key
FROM orders
WHERE user_id IS NOT NULL;

-- 实际结果行数
SELECT count(*)
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

实战示例 ​

下面用完整流程做"用户及其订单"与"缺数据检测"。

sql
-- 步骤 1：列出所有用户的下单情况（含 0 单用户）
SELECT
  u.id,
  u.name,
  count(o.id)                  AS order_cnt,
  coalesce(sum(o.amount), 0)   AS total_amount,
  max(o.created_at)            AS last_order_date
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY total_amount DESC;
text
 id | name  | order_cnt | total_amount | last_order_date
----+-------+-----------+--------------+-----------------
  1 | alice |         2 |      1125.00 | 2026-02-09
  2 | bob   |         2 |       689.00 | 2026-02-15
  3 | carol |         1 |       195.00 | 2026-02-02
  4 | dave  |         0 |         0.00 | (null)
(4 rows)

注意 count(o.id) 与 count(*) 的差异：dave 无订单时外连接仍产生一行，count(*) 会返回 1，而 count(o.id) 因 o.id 为 NULL 正确返回 0。

sql
-- 步骤 2：只统计已付款订单，但保留所有用户（条件放 ON）
SELECT
  u.name,
  count(o.id)                                 AS paid_cnt,
  coalesce(sum(o.amount), 0)                  AS paid_amount
FROM users AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id AND o.status = 'paid'
GROUP BY u.name
ORDER BY paid_amount DESC;
text
 name  | paid_cnt | paid_amount
-------+----------+-------------
 alice |        2 |     1125.00
 bob   |        2 |      689.00
 carol |        0 |        0.00
 dave  |        0 |        0.00
(4 rows)
sql
-- 步骤 3：反连接 —— 找出从未下单的用户
SELECT u.id, u.name, u.city
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL
ORDER BY u.id;
text
 id | name |  city
----+------+--------
  4 | dave | chengdu
(1 row)
sql
-- 步骤 4：FULL JOIN 双向核对 —— 数据质量报告
SELECT
  CASE
    WHEN u.id IS NULL THEN '孤儿订单'
    WHEN o.id IS NULL THEN '沉默用户'
  END                                 AS issue_type,
  coalesce(u.name, '(未知用户)')       AS user_name,
  coalesce(o.order_no, '-')            AS order_no,
  o.amount
FROM users AS u
FULL JOIN orders AS o ON o.user_id = u.id
WHERE u.id IS NULL OR o.id IS NULL
ORDER BY issue_type, order_no;
text
 issue_type | user_name  | order_no | amount
------------+------------+----------+--------
 沉默用户    | dave       | -        | (null)
 孤儿订单    | (未知用户)  | SO-1006  |  89.00
 孤儿订单    | (未知用户)  | SO-1007  | 350.00
(3 rows)
sql
-- 步骤 5：验证外连接行数是否有膨胀
SELECT
  (SELECT count(*) FROM users)                                    AS user_rows,
  (SELECT count(*) FROM users u LEFT JOIN orders o ON o.user_id = u.id) AS joined_rows,
  (SELECT count(*) FROM users u JOIN orders o ON o.user_id = u.id)      AS inner_rows;
text
 user_rows | joined_rows | inner_rows
-----------+-------------+------------
         4 |           6 |          5

joined_rows (6) = inner_rows (5) + 未匹配用户 (1)，验证了 LEFT JOIN 的行数公式。

注意事项 ​

  • LEFT JOIN 中把右表条件写进 WHERE 会退化为内连接。 这是外连接第一号陷阱，判定依据是"该列是否可能为 NULL"。
sql
-- 错误！条件在 WHERE 中，carol、dave 被过滤
SELECT u.name, o.order_no
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'paid';

-- 正确：条件在 ON 中
SELECT u.name, o.order_no
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'paid';
  • WHERE 右表列 IS NULL 与反连接的区别。 判定列必须选右表的非空列，否则会把"右表该列本身就是 NULL"的行误判为未匹配。
sql
-- 错误！o.user_id 本身可空，会误判游客订单
SELECT u.name FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.user_id IS NULL;

-- 正确：用右表主键
SELECT u.name FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;
  • 外连接后 count(*) 不能判断"是否存在匹配行"。 无匹配时仍产生一行，应使用 count(右表列)。
sql
-- 错误：dave 无订单，count(*) 仍算 1
SELECT u.name, count(*) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.name;

-- 正确
SELECT u.name, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.name;
  • RIGHT JOIN 与 LEFT JOIN 混用会显著降低可读性。 尽量统一为 LEFT JOIN，通过调换表顺序实现等价语义。
  • FULL JOIN 的 ON 条件不能用 WHERE 替代。 因为两侧都可能补空，不存在"哪一侧是主体"。
  • 多个外连接串联时，内连接会截断 NULL 传播。 混合场景建议用括号明确结合顺序。
  • 外连接阻止某些优化。 内连接满足交换律与结合律，优化器可自由重排；外连接不满足，计划空间受限，复杂外连接查询可能得不到最优计划。
  • IS NULL 反连接在有大量匹配行时性能较差。 它会先生成全部匹配行再过滤，此时 NOT EXISTS 通常更优。

最佳实践 ​

  1. 写 LEFT JOIN 时，把"属于连接关系的条件"全部放进 ON。 包括对右表的业务状态过滤——只要该过滤的目的是"限定匹配哪些右表行"。

  2. 判定反连接时统一用右表主键的 IS NULL。 主键非空，判定不会有歧义。

sql
-- 标准的反连接模板
SELECT l.*
FROM left_table AS l
LEFT JOIN right_table AS r ON r.fk_id = l.id
WHERE r.id IS NULL;

  1. 优先用 LEFT JOIN 而非 RIGHT JOIN。 需要"以右表为主体"时，把表写在左边。

  2. 为外连接的连接列建索引。 补空侧（右表）的连接列尤其需要索引，否则每行探测都要全表扫描。

sql
CREATE INDEX idx_orders_user_id   ON orders (user_id);
-- 复合索引（连接列 + 常用于 ON 的过滤列）能同时加速连接与条件判断
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
  1. 用 EXPLAIN 确认外连接走的是 Hash Left Join 还是 Nested Loop Left Join。 当补空侧是大表且无索引时，计划里会出现 Seq Scan + 高 loops，这是性能隐患。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, o.order_no, o.amount
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'paid' OR o.status IS NULL;
  1. 聚合前先确认外连接是否造成行膨胀。 一对多外连接后直接 sum 会重复计数，应先聚合右表再连接。
sql
-- 正确：先聚合订单，再左连接到用户
WITH user_amount AS (
  SELECT user_id, sum(amount) AS amount, count(*) AS cnt
  FROM orders
  WHERE status = 'paid'
  GROUP BY user_id
)
SELECT u.name, coalesce(a.cnt, 0) AS order_cnt, coalesce(a.amount, 0) AS total
FROM users AS u
LEFT JOIN user_amount AS a ON a.user_id = u.id
ORDER BY total DESC;

  1. FULL JOIN 只用于核对类场景。 业务查询中若出现 FULL JOIN，先确认能否用两次 LEFT JOIN 或 UNION ALL 表达，后者往往更好理解也更好优化。

  2. 用 coalesce 给补空列提供可读的默认值。 报表输出时，NULL 通常需要显示为 0 或"(无)"。

sql
SELECT
  u.name,
  coalesce(o.order_no, '(无订单)')     AS order_no,
  coalesce(o.amount, 0)                AS amount
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
ORDER BY u.name;

下一节

继续学习：交叉连接（CROSS JOIN）

参考链接 ​