首页 / PostgreSQL 入门教程 / 多表连接

← 上一篇 下一篇 →
PostgreSQL 入门教程

多表连接

本教程共 50 篇 · 第 29 篇 · 更新于 2026-07-31 · 约 12 分钟阅读

PostgreSQLPostgreSQL 入门教程JOIN多表连接LEFT JOINSQL 查询

29. 多表连接

本节目标：学会用 JOIN 把两张（或多张）表按关联字段拼到一起，掌握 INNER / LEFT / RIGHT / FULL OUTER / CROSS JOIN、自连接，以及 USING 和 NATURAL JOIN 的用法，并弄清 ON 与 WHERE 的区别。

关系型数据库的一大特点，就是数据分散在多张表里。比如用户信息在 users 表，订单信息在 orders 表。想知道「每个用户下了哪些订单」，就得把两张表连起来看。

这种把多张表拼到一起的查询，SQL 里叫做「连接（JOIN）」。下面都用这两张表来演示。先把它俩建出来并塞点数据：

-- 用户表
CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age INT,
    created_at DATE
);

-- 订单表，user_id 对应用户表的主键 id
CREATE TABLE orders (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id INT,
    amount NUMERIC(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATE
);

INSERT INTO users (username, email, age, created_at) VALUES
    ('Alice', 'alice@test.com', 28, '2025-01-10'),
    ('Bob',   'bob@test.com',   35, '2025-02-11'),
    ('Cara',  'cara@test.com',  22, '2025-03-12'),
    ('Dan',   'dan@test.com',   41, '2025-04-13');

INSERT INTO orders (user_id, amount, status, created_at) VALUES
    (1, 99.90,  'paid',    '2025-05-01'),
    (1, 49.00,  'paid',    '2025-05-20'),
    (2, 199.00, 'pending', '2025-06-02'),
    (3, 15.50,  'paid',    '2025-06-15'),
    (NULL, 88.00, 'paid',   '2025-07-01');  -- 一笔游客订单，没有归属任何用户

连接到底在做什么

先打个比方。连接不是「把两张表揉成一张」，而是「按规则把左表的一行和右表的一行配对，凑成新的一行」。新行的列数 = 左表列数 + 右表列数。

比如 users 有 5 列、orders 有 5 列，连接后每行就有 10 列。配对规则由你写在 ON 后面的条件决定。

users 的一行  +  orders 的一行   =>   结果里的一行
(id,username)   (id,user_id,...)     (id,username,...,id,user_id,...)

记住这点很重要：连接不丢列，只是横向把两张表的列拼到同一行上。下面几种 JOIN 的区别，全在于「哪些行能配对成功、配不上的怎么处理」。

INNER JOIN（内连接）

内连接（inner join）只返回两边都能对上的行。拿 users 和 orders 来说，只有存在对应用户的订单才会显示出来。

SELECT u.username, o.amount, o.status
FROM users u
INNER JOIN orders o ON o.user_id = u.id
ORDER BY u.username;

ON 后面写的是连接条件（join condition），这里用 o.user_id = u.id 把两张表关联起来。INNER 可以省略，直接写 JOIN 默认就是内连接。

注意那笔 user_id 为 NULL 的游客订单不会出现，因为 NULL 对不上任何 id（NULL 不等于任何值，包括它自己）。同时，Dan 也没有出现在结果里——他没下过单，右表配不出对应的行。

Note

内连接的结果行数，最多等于「两边较小的匹配数」。一对多关系（一个用户多笔订单）里，结果会出现「用户重复、订单不同」的行，这是正常的。

LEFT JOIN（左连接）

左连接（left join）会保留左表（写在 FROM 后面的表）的全部行，右表对不上的部分用 NULL 补齐。

SELECT u.username, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.username;

Dan 没有任何订单，所以他的 amount 显示 NULL。这点在做统计时很关键：想要「所有用户的订单数」，必须用 LEFT JOIN，否则没下单的用户会被丢掉。

-- 统计每个用户的订单数（含 0 单的用户）
SELECT u.username, COUNT(o.id) AS 订单数
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username
ORDER BY 订单数 DESC;

LEFT JOIN 和 LEFT OUTER JOIN 是一回事，OUTER 可写可不写。

Tip

想找出「没有下过单的用户」？在左连接后面加一个 WHERE o.id IS NULL 即可。原理是：能配上的行 o.id 有值，配不上的行 o.id 是 NULL，用 IS NULL 就能筛出后者。

RIGHT JOIN（右连接）

右连接（right join）和左连接正好相反，保留右表的全部行，左表对不上补 NULL。它其实就是把左右表换个位置，日常用得比 LEFT JOIN 少。

SELECT u.username, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

那笔游客订单（user_id 为 NULL）这时会出现，而 username 是 NULL。多数时候，把表顺序调一下、改用 LEFT JOIN 会更易读，所以我一般优先写 LEFT JOIN。

FULL OUTER JOIN（全外连接）

全外连接（full outer join）把两边都保留：左表多余的、右表多余的，统统保留，对不上的那一侧补 NULL。

SELECT u.username, o.amount
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

结果里既有「没下单的 Dan」，也有「没归属用户的游客订单」。想找出「两边对不上号」的所有行，加 WHERE u.id IS NULL OR o.id IS NULL。

Warning

全外连接不是所有数据库都支持，PostgreSQL 支持得很好。但因为它返回的行可能两边都带 NULL，用来排查「孤儿数据」（引用断了的数据）很有用。

CROSS JOIN（交叉连接）

交叉连接（cross join）不做任何匹配，直接把左表的每一行和右表的每一行两两组合，产生「笛卡尔积（Cartesian product）」。

-- 先准备一张尺码表，再和用户做笛卡尔积
CREATE TABLE sizes (
    size VARCHAR(10)
);
INSERT INTO sizes (size) VALUES ('S'), ('M'), ('L');

SELECT u.username, s.size
FROM users u
CROSS JOIN sizes s;

如果 users 有 4 行、sizes 有 3 行，结果就是 12 行。它不带 ON 条件，用于生成组合（比如商品 × 尺码）。表很大时要小心，结果行数会爆炸——4 万行 × 4 万行就是 16 亿行。

Note

漏写 ON 条件的普通 JOIN 在 PostgreSQL 里会报错，不会悄悄变成交叉连接。但 CROSS JOIN 是显式写法，语义更清楚。

自连接（Self-Join）

表也能和自己连接，只要给同一张表起两个不同的别名。它常用来处理「层级数据」，比如员工和上级的关系。

CREATE TABLE employees (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES
    ('王总', NULL),
    ('李经理', 1),
    ('赵组长', 3),
    ('小钱', 2);

-- 查每个人对应的上级
SELECT e.name AS 员工, m.name AS 上级
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

自连接本质还是连接，只是左右两边是同一张物理表。用 LEFT JOIN 是因为「王总」的 manager_id 是 NULL，没有上级，用内连接会把他漏掉。

USING 与 NATURAL JOIN

当连接字段两边名字完全一样时，可以用 USING 简化 ON：

-- 等价于 ON a.id = b.id，且结果里 id 只出现一次
SELECT * FROM table_a a JOIN table_b b USING (id);

USING 和 ON 有个细微差别：USING 合并同名列，结果里那只列出现一次；ON 则会把两列都保留（如 a.id 和 b.id 都在）。

NATURAL JOIN 更「自动」——它会按同名同类型的列自动连接：

-- 先准备两张示范表
CREATE TABLE products (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
CREATE TABLE categories (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
INSERT INTO products (name) VALUES ('键盘'), ('鼠标');
INSERT INTO categories (name) VALUES ('外设'), ('配件');

SELECT * FROM products NATURAL JOIN categories;
Warning

NATURAL JOIN 很危险。它会使用所有同名列来连接，一旦两张表多了一个同名但无关的列（比如 last_update），结果就会出错甚至为空。生产环境我一般不推荐用它，明确写 ON 或 USING 更稳妥。

ON 与 WHERE 的区别

这是新手最容易踩的坑。同样是筛选，ON 和 WHERE 生效的时机不同：

  • ON 在连接发生时过滤「能不能配对」，配不上的左表行在 LEFT JOIN 里仍会保留（右表补 NULL）。
  • WHERE 在连接完成之后过滤「整行去留」，一旦条件不满足，整行都会被丢掉，包括 LEFT JOIN 本想保留的左表行。
-- ON 过滤：Dan 仍会出现，只是订单为 NULL
SELECT u.username, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';

-- WHERE 过滤：Dan 因为有订单为 NULL 的行不满足 o.status='paid'，被整行删掉
SELECT u.username, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

第二条语句其实退化成了内连接的效果。想保留左表全部行、又想过滤右表，条件一定要写在 ON 里。

三张及以上的表连接

连接不限于两张表，可以一条语句里连多张。比如再加一张 products 表，看每笔订单买了什么：

-- 准备订单明细表（products 表在前面 USING 一节已建好）
CREATE TABLE order_items (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL
);
INSERT INTO order_items (order_id, product_id) VALUES (1, 1), (1, 2), (2, 3);

SELECT u.username, o.amount, p.name
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

连接是按你写的顺序逐张拼的，每拼一张都要保证上一阶段的结果里有能关联的列。表多了可读性会下降，这时可以像第 31 章那样用 CTE 拆开。

常见错误

  1. 忘了写 ON，或 ON 条件写错，导致配不出预期的行。
  2. 在 LEFT JOIN 后把右表条件写进 WHERE，结果丢失了左表行。
  3. 连接键一边是 NULL，导致那行永远配不上（NULL 不等于任何值）。
  4. CROSS JOIN 忘记控制数据量，结果行数爆炸。

小结

连接的核心就是「用什么条件把哪些行配成一对」。内连接取交集，左/右连接保留一侧全部，全外连接保留两侧全部，交叉连接做穷举，自连接解决层级数据。写连接时，我习惯把 ON 条件写清楚，再配合 WHERE 过滤，可读性最好。记住 ON 管配对、WHERE 管结果去留，左连接配不上补 NULL 这个特性要善加利用。

上一篇
GROUP BY 与 HAVING
下一篇
子查询