PostgreSQL Join

下面给你一套最系统、最清晰、最可实战操作的 PostgreSQL JOIN 详细教程。

涵盖 7 种 JOIN、维恩图解释、语法、示例、最佳实践、性能优化，适合你当前的后端开发（FastAPI + PostgreSQL）场景。

📘 PostgreSQL JOIN 详细教程（完整版）

⭐ 目录

JOIN 的作用
JOIN 的分类与对照
每一种 JOIN 的语法 + 维恩图 + 示例
JOIN 的筛选规则（WHERE 与 ON 的区别）
多表 JOIN
JOIN 性能优化
JOIN 常见错误与解决
JOIN 最佳实践总结

1. JOIN 是什么？有什么作用？

JOIN 用于：

将两张或多张表按某个字段关联连接
从不同表中组成更完整的数据集
实现复杂查询逻辑

它的本质：

JOIN 就是用一个字段作为“键”，把多张表的相关数据拼成同一行。

2. PostgreSQL JOIN 分类（7 种）

JOIN 类型	说明	是否保留不匹配记录
INNER JOIN	交集	❌ 不保留
LEFT JOIN	A 全部 + A∩B	✅ A
RIGHT JOIN	B 全部 + A∩B	✅ B
FULL JOIN	A ∪ B	✅ A & B
CROSS JOIN	笛卡尔积	全保留
SELF JOIN	自己 JOIN 自己	按语法决定
ANTI JOIN（NOT IN）	A - B	✅ A

3. 各种 JOIN 详细解释（含维恩图与示例）

假设表：

A(id, a_value)
B(id, b_value)

CREATE TABLE "public"."a" (
    "id" int4 NOT NULL,
    "name" varchar(255),
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."b" (
    "id" int4 NOT NULL,
    "name" varchar(255),
    PRIMARY KEY ("id")
);

INSERT INTO "public"."a" ("id", "name") VALUES (1, 'Jack');
INSERT INTO "public"."a" ("id", "name") VALUES (2, 'Tom');

INSERT INTO "public"."b" ("id", "name") VALUES (2, 'Tom');
INSERT INTO "public"."b" ("id", "name") VALUES (3, 'Rose');

3.1 INNER JOIN（交集）

🎯 两边都存在的记录才会返回。

语法：

SELECT *
FROM A
INNER JOIN B ON A.id = B.id;

示例结果：只返回两表都有的 id。

id	name	id	name
2	Tom	2	Tom

3.2 LEFT JOIN（左表全部 + 右表匹配的部分）

语法：

SELECT *
FROM A
LEFT JOIN B ON A.id = B.id;

没匹配到 B 时，B 的字段为 null。
常用场景：主数据在 A，附属数据在 B。

id	name	id	name
1	Jack
2	Tom	2	Tom

3.3 RIGHT JOIN（右表全部 + 左表匹配）

语法：

SELECT *
FROM A
RIGHT JOIN B ON A.id = B.id;

id	name	id	name
2	Tom	2	Tom
		3	Rose

3.4 FULL JOIN（并集：A ∪ B）

包括：

A ∩ B
A 独有
B 独有

语法：

SELECT *
FROM A
FULL JOIN B ON A.id = B.id;

常用于：合并两套数据源。

id	name	id	name
1	Jack
2	Tom	2	Tom
		3	Rose

3.5 CROSS JOIN（笛卡尔积）

A 的每一行 × B 的每一行

语法：

SELECT *
FROM A
CROSS JOIN B;

示例：

A 3 行 × B 4 行 = 12 行结果。

id	name	id	name
1	Jack	2	Tom
1	Jack	3	Rose
2	Tom	2	Tom
2	Tom	3	Rose

3.6 SELF JOIN（自连接）

语法：

SELECT a1.id, a2.id
FROM A AS a1
JOIN A AS a2 ON a1.id != a2.id;

id	id
1	2
2	1

常用于：

层级结构
父子关系
相邻比较

3.7 ANTI JOIN（A - B）

PostgreSQL 用：

方法 1：LEFT JOIN + NULL

SELECT A.*
FROM A
LEFT JOIN B ON A.id = B.id
WHERE B.id IS NULL;

id	name
1	Jack

方法 2：NOT EXISTS（性能最佳）

SELECT A.*
FROM A
WHERE NOT EXISTS (
    SELECT 1 FROM B WHERE B.id = A.id
);

id	name
1	Jack

4. ON vs WHERE 的区别（必懂）

❌ 错误用法（WHERE 会影响 JOIN 行为）：

SELECT *
FROM A
LEFT JOIN B ON A.id = B.id
WHERE B.name = 'Tom';

⚠️ 这会把不匹配的行过滤掉 -> 实际变成 INNER JOIN！

id	name	id	name
2	Tom	2	Tom

✅ 正确用法：

SELECT *
FROM A
LEFT JOIN B ON A.id = B.id AND B.name = 'Tom';

id	name	id	name
1	Jack
2	Tom	2	Tom

要点：

LEFT JOIN 的过滤条件写在 ON 中，不写 WHERE。
WHERE 会把 null 行过滤掉。

5. 多表 JOIN（链式）

SELECT *
FROM orders o
JOIN users u        ON o.user_id = u.id
JOIN products p     ON o.product_id = p.id
LEFT JOIN tags t    ON p.tag_id = t.id;

设计原则：

JOIN 顺序无所谓（优化器会处理）
但逻辑顺序应该清晰
一般：主表写在最前面

6. JOIN 性能优化

6.1 必须加索引

例：A.id JOIN B.id

A.id、B.id 都应有 index。

CREATE INDEX idx_a_id ON A(id);
CREATE INDEX idx_b_id ON B(id);

6.2 尽量避免

❌ LIKE ‘%xxx%’ JOIN
❌ 计算字段 JOIN，如 ON SUBSTR(code,1,3) = ‘123’

这些会导致无法使用索引。

6.3 使用 EXPLAIN 查看执行计划

EXPLAIN ANALYZE
SELECT ...

查看是否出现：

Seq Scan（慢）
Hash Join（适中）
Nested Loop（大表慎用）
Merge Join（排序）

7. JOIN 常见错误

7.1 使用 DISTINCT 误伤数据

错误：

SELECT DISTINCT a.id, b.name ...

DISTINCT 会跨表字段去重，导致丢数据。

7.2 在 WHERE 中过滤 LEFT JOIN -> 变 INNER JOIN

（前面已讲）

7.3 GROUP BY + JOIN 导致重复

解决：

GROUP BY a.id

或

DISTINCT ON (a.id)

8. JOIN 最佳实践总结

场景	使用
两表都必须有记录	INNER JOIN
主表为 A	LEFT JOIN
主表为 B	RIGHT JOIN
合并两个数据源（需要并集）	FULL JOIN
子集/差集（A - B）	NOT EXISTS
多级分类、父子树	SELF JOIN
生成组合	CROSS JOIN
去重后查询	DISTINCT ON