第 11 章：表连接 (JOIN) 🤝 - PostgreSQL入门

摘要：到目前为止，我们所有的操作都只围绕着一张 friends 表。但在真实的世界里，数据总是被分散在不同的表里，并通过某种“关系”联系在一起。

欢迎来到关系型数据库的“灵魂”所在！

到目前为止，我们所有的操作都只围绕着一张 friends 表。但在真实的世界里，数据总是被分散在不同的表里，并通过某种“关系”联系在一起。

比如，一个博客系统，会有“用户表”和“文章表”。文章表里只会记录作者的 ID，而不会把作者的姓名、邮箱等信息再存一遍。当我们想查询“某篇文章的标题以及作者的姓名”时，就需要把这两张表连接起来。

JOIN 就是实现这一魔法的咒语。它能根据两张或多张表之间的共同字段，将它们像拼图一样组合在一起，形成一个临时的、更宽的表，让我们可以在上面进行查询。

在连接表之前，我们得先建立一些新的表来模拟真实场景。

场景设定：我们来为一个简单的博客系统设计数据表。

第一步：创建 users 表
这张表用来存放我们的用户。

CREATE TABLE users ( user_id INT PRIMARY KEY, -- 使用主键约束，保证ID唯一且非空 username VARCHAR(50) NOT NULL, email VARCHAR(100));PRIMARY KEY (主键): 这是表中最重要的约束，它保证 user_id 这一列的值是唯一且非空的，是每一行记录的唯一身份证。

第二步：创建 posts 表
这张表用来存放文章。

CREATE TABLE posts ( post_id INT PRIMARY KEY, author_id INT, -- 这个将用来关联 users 表 title VARCHAR(200), content TEXT, FOREIGN KEY (author_id) REFERENCES users(user_id) -- 定义外键);FOREIGN KEY (author_id) REFERENCES users(user_id): 这是外键约束。它建立了两张表之间的“关系”。它告诉数据库：posts 表的 author_id 这一列，必须引用 users 表中某个已经存在的 user_id。这保证了你不可能创建一篇“不存在的幽灵作者”的文章，维护了数据的引用完整性。

这就是一个典型的 一对多 (One-to-Many) 关系：一个 user 可以写多篇 post，但一篇 post 只能有一个 user 作者。

第三步：插入一些数据

-- 插入用户INSERT INTO users (user_id, username, email) VALUES(1, '老王', 'wang@example.com'),(2, '李雷', 'li@example.com'),(3, '韩梅梅', 'han@example.com');-- 插入文章INSERT INTO posts (post_id, author_id, title, content) VALUES(101, 1, 'PostgreSQL入门', '内容...'),(102, 2, 'JOIN的艺术', '内容...'),(103, 1, '深入理解索引', '内容...'),(104, 4, '一篇不存在作者的文章'); -- 这句会失败！因为users表里没有ID为4的用户。

请注意，最后一条 INSERT 会因为外键约束而失败，这是我们期望看到的！

INNER JOIN 是最常用、最直观的连接方式。它会返回两张表中连接字段能够匹配上的所有行。

可以把它想象成取两张表的“交集”。

问题：我们想查询所有文章的标题，以及其作者的用户名。

SELECT p.title, u.usernameFROM posts AS pINNER JOIN users AS u ON p.author_id = u.user_id;

语法解析 :

FROM posts AS p: 我们从 posts 表开始，并给它起了个别名 p。INNER JOIN users AS u: 我们要将它与 users 表（别名 u）进行内连接。ON p.author_id = u.user_id: 这是连接的核心！ON 关键字后面跟着连接条件。这里是“当 posts 表的 author_id 等于 users 表的 user_id 时，就把这两行拼在一起”。

执行结果:

title | username+ PostgreSQL入门 | 老王 JOIN的艺术 | 李雷深入理解索引 | 老王(3 rows)

看！我们成功地把两张表的信息“缝合”在了一起！

现在，有个新问题：如果我们想列出所有的用户，以及他们分别发表了哪些文章呢？如果某个用户没发表过文章，我们希望他仍然出现在列表里。

INNER JOIN 做不到这一点，因为它只返回能匹配上的。这时，LEFT JOIN 就派上用场了。

LEFT JOIN 会返回左表 (FROM 后面的表) 的所有行，以及右表中能匹配上的行。如果右表中没有匹配的行，那么从右表选择的列将显示为 NULL。

问题：列出所有用户，以及他们写的文章标题。

SELECT u.username, p.titleFROM users AS uLEFT JOIN posts AS p ON u.user_id = p.author_id;

执行结果:

LEFT JOIN 在“以一张主表为基准，去关联补充信息”的场景下非常有用。

RIGHT JOIN 和 LEFT JOIN 正好相反。它会返回右表 (JOIN 后面的表) 的所有行。如果左表中没有匹配的行，那么从左表选择的列将显示为 NULL。

实际上，A RIGHT JOIN B 完全等价于 B LEFT JOIN A。所以，RIGHT JOIN 并不常用，因为我们总是可以通过调整表的顺序，用 LEFT JOIN 来实现同样的目的，这样更容易理解。