30 右连接与全连接

RIGHT JOIN 是 LEFT JOIN 的镜像：右侧每一行都会保留，即使左侧没有匹配。FULL OUTER JOIN 保留两侧的每一行，未匹配的列用 NULL 填充。实际使用中，LEFT JOIN 比 RIGHT JOIN 常见得多——把“主”表放在前面，再用 LEFT JOIN 引入补充数据，通常更自然。需要同时查看匹配项和两类孤立行时（例如对账报告），FULL JOIN 就很有用。

本章要点

• RIGHT JOIN：保留右侧全部内容
• FULL JOIN：保留两侧全部内容
• 为什么 LEFT JOIN 通常更易读
• 找出未匹配的行：WHERE other.id IS NULL
• 两侧可能为 NULL 时如何排序

示例

-- Uses customers_example and orders_example from the previous lesson
-- (run inner-and-left-join.sql first if you have not)

-- RIGHT JOIN: every order, even those without a matching customer
SELECT c.name, o.id AS order_id, o.total
FROM customers_example c
RIGHT JOIN orders_example o ON o.customer_id = c.id
ORDER BY o.id;

-- FULL OUTER JOIN: every customer and every order
SELECT c.name, o.id AS order_id, o.total
FROM customers_example c
FULL OUTER JOIN orders_example o ON o.customer_id = c.id
ORDER BY c.id NULLS LAST, o.id NULLS LAST;

-- "Orphans" — find unmatched rows on either side
-- Customers with no orders:
SELECT c.id, c.name
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Orders with no customer:
SELECT o.id, o.customer_id
FROM customers_example c
RIGHT JOIN orders_example o ON o.customer_id = c.id
WHERE c.id IS NULL;

RIGHT JOIN A ON ... 与交换表后的 LEFT JOIN A 完全等价——A LEFT JOIN B ON cond 与 B RIGHT JOIN A ON cond 相同。大多数风格指南建议把 RIGHT JOIN 改写为 LEFT JOIN，这样“主”表会出现在 FROM 子句的前面。

FULL OUTER JOIN 是对称的连接形式：两侧每一行都会出现，未匹配的列为 NULL。第 3 个订单（customer_id = 99）会以 NULL name 出现；Bob 和 Carol 会以 NULL 订单列出现。ORDER BY ... NULLS LAST 会让结果更易读。

经典的反连接模式——“A 中没有在 B 中匹配的行”——使用 LEFT JOIN ... WHERE B.id IS NULL。PostgreSQL 能识别这种写法，通常会把它规划成高效的反连接。

运行示例

运行方式（需要上一课创建的表）：

$ psql -f source/right-and-full-join.sql postgres
 name  | order_id | total
-------+----------+-------
 Alice |        1 | 50.00
 Alice |        2 | 30.00
       |        3 | 10.00
(3 rows)

 name  | order_id | total
-------+----------+-------
 Alice |        1 | 50.00
 Alice |        2 | 30.00
 Bob   |          |
 Carol |          |
       |        3 | 10.00
(5 rows)
 ...

常见陷阱

• 先写 RIGHT JOIN，然后还得在脑子里把列顺序翻过来。交换表的顺序，改用 LEFT JOIN，可读性更好。
• FULL JOIN 的结果两侧都可能有 NULL；如果 ORDER BY 没有指定 NULLS LAST/FIRST，它们可能会散落在结果中，令人困惑。
• 使用 WHERE other.id IS NULL 处理 LEFT JOIN 是反连接的惯用写法。NOT EXISTS (...) 和 NOT IN (...) 是替代方案——在含有 NULL 时，NOT EXISTS 通常最安全。
• 用 FULL JOIN 连接多个表通常不是你想要的结果——类似笛卡尔积的行为会叠加。最好只在简单场景中使用。

下载

SQL 文件：right-and-full-join.sql · 全部示例文件 · 准备工作