PostgreSQL: PostgreSQL多表连接查询

最后更新：2026-08-26

1. 你将学到

INNER JOIN 内连接
LEFT / RIGHT / FULL OUTER JOIN 外连接
CROSS JOIN 交叉连接
NATURAL JOIN 自然连接（慎用）
自连接（Self-join）
USING 简化语法
多表连接（3+ 表）
PostgreSQL 特色：LATERAL JOIN
JOIN 性能基础（EXPLAIN 入门）

2. 故事

Charlie 是 SaaS 平台的后端工程师。产品经理要求他生成一份用户购买明细报告，需要关联 4 张表：

users —— 用户信息
orders —— 订单表
order_items —— 订单明细
products —— 产品表

一个用户可能有多笔订单，每笔订单有多件商品，每件商品对应一个产品。Charlie 需要选择正确的 JOIN 类型，确保不遗漏无订单用户，也不产生意外笛卡尔积。

3. Concept

(1) JOIN 类型全景

JOIN 类型	含义	保留哪侧	典型场景
INNER JOIN	只保留匹配行	两侧都不保留	必须匹配才取
LEFT JOIN	左表全保留	左表	主表 + 可选附属
RIGHT JOIN	右表全保留	右表	较少使用
FULL JOIN	两侧全保留	两侧	找差异/对账
CROSS JOIN	笛卡尔积	无条件	排列组合
LATERAL JOIN	子查询引用左表	—	每行关联 Top-N

flowchart LR
    subgraph Inner
        A1((A)) --- B1((B))
    end
    subgraph Left
        A2((A)) --- B2((B))
        A3((A)) -.-> B3((∅))
    end
    subgraph Full
        A4((A)) --- B4((B))
        A5((A)) -.-> B6((∅))
        B5((∅)) -.-> A6((A))
    end

    style A1 fill:#c8e6c9
    style B1 fill:#c8e6c9
    style A2 fill:#c8e6c9
    style A3 fill:#c8e6c9
    style B2 fill:#c8e6c9
    style A4 fill:#c8e6c9
    style A5 fill:#c8e6c9
    style B4 fill:#c8e6c9

(2) INNER JOIN

只返回两表中能匹配的行。

▶ 示例：查询有订单的用户

SQL

SELECT
  u.user_id,
  u.name,
  o.order_id,
  o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

TEXT 📖 仅展示

 user_id | name  | order_id | amount
---------+-------+----------+--------
       1 | Alice |      101 |  15000
       1 | Alice |      102 |   8000
       2 | Bob   |      201 |  25000

从未下过单的用户不会出现。

(3) LEFT JOIN

左表全部保留，右表无匹配则填 NULL。

▶ 示例：包含无订单用户

SQL

SELECT
  u.user_id,
  u.name,
  o.order_id,
  o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

TEXT 📖 仅展示

 user_id | name    | order_id | amount
---------+---------+----------+--------
       1 | Alice   |      101 |  15000
       1 | Alice   |      102 |   8000
       2 | Bob     |      201 |  25000
       3 | Charlie |          |

Charlie 没有订单，order_id 和 amount 为 NULL。

▶ 示例：找出无订单用户

SQL

SELECT u.user_id, u.name
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(4) RIGHT JOIN 与 FULL JOIN

▶ 示例：FULL JOIN 找出用户和订单的差异

SQL

SELECT
  u.user_id,
  u.name,
  o.order_id,
  o.user_id AS order_user_id
FROM users u
FULL JOIN orders o ON u.user_id = o.user_id
ORDER BY u.user_id NULLS LAST, o.order_id NULLS LAST;

TEXT 📖 仅展示

 user_id | name    | order_id | order_user_id
---------+---------+----------+---------------
       1 | Alice   |      101 |              1
       2 | Bob     |      201 |              2
       3 | Charlie |          |
         |         |      999 |             99

user_id=99 的订单没有对应用户，user_id=3 的用户没有订单——两边差异都可见。

▶ 示例：RIGHT JOIN 查看孤儿订单

SQL

SELECT o.order_id, o.user_id, u.name
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id IS NULL;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

JOIN 类型	行为	用途对比
LEFT JOIN	保留左表全部	查主表+附属，找左表缺失
RIGHT JOIN	保留右表全部	可改写为 LEFT JOIN 换位
FULL JOIN	保留两侧全部	对账、找差异

(5) CROSS JOIN 与 NATURAL JOIN

▶ 示例：CROSS JOIN 生成所有组合

SQL

SELECT
  d.department_name,
  p.project_name
FROM departments d
CROSS JOIN projects p;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

3 个部门 × 5 个项目 = 15 行。

▶ 示例：NATURAL JOIN（慎用）

SQL

SELECT * FROM users
NATURAL JOIN orders;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

NATURAL JOIN 自动按两表同名列连接。危险：如果两表有多个同名列（如 created_at），会产生非预期的 AND 条件，建议显式写 ON 或 USING。

语法	优点	缺点
NATURAL JOIN	简短	列名变动可能改变连接逻辑
USING(col)	简短且明确	只适用于同名列等值连接
ON a.col = b.col	完全控制	冗长

(6) USING 语法

▶ 示例：USING 替代 ON

SQL

SELECT
  u.name,
  o.order_id,
  o.amount
FROM users u
JOIN orders o USING (user_id);

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

USING 会合并同名列为一列，SELECT * 不会重复输出 user_id。

(7) 自连接（Self-Join）

一张表与自己连接，用于层级或同表对比。

▶ 示例：员工与上级配对

SQL

SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

TEXT 📖 仅展示

 employee | manager
----------+---------
 Alice    | David
 Bob      | Alice
 Charlie  | Alice
 David    |

▶ 示例：同类别产品价格对比

SQL

SELECT
  a.product_name AS product_a,
  b.product_name AS product_b,
  a.price - b.price AS price_diff
FROM products a
JOIN products b ON a.category_id = b.category_id
  AND a.product_id < b.product_id
  AND ABS(a.price - b.price) < 100;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

4. Key Points

(1) 多表连接（3+ 表）

Charlie 的需求：关联 users → orders → order_items → products 四张表。

▶ 示例：4 表关联用户购买明细

SQL

SELECT
  u.name            AS user_name,
  o.order_id,
  o.created_at      AS order_date,
  p.product_name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS line_total
FROM users u
JOIN orders o           ON u.user_id = o.user_id
JOIN order_items oi     ON o.order_id = oi.order_id
JOIN products p         ON oi.product_id = p.product_id
ORDER BY u.name, o.order_id, oi.order_item_id;

TEXT 📖 仅展示

 user_name | order_id | order_date           | product_name | quantity | unit_price | line_total
-----------+----------+----------------------+--------------+----------+------------+------------
 Alice     |      101 | 2025-03-15 10:30:00  | Widget Pro   |        2 |      15000 |      30000
 Alice     |      101 | 2025-03-15 10:30:00  | Gadget Mini  |        5 |       3000 |      15000
 Alice     |      102 | 2025-04-02 14:20:00  | Widget Pro   |        1 |      15000 |      15000
 Bob       |      201 | 2025-05-10 09:00:00  | Server Rack  |        1 |      80000 |      80000

(2) LATERAL JOIN（PostgreSQL 特色）

LATERAL 允许子查询引用左侧表的列，相当于对左表每一行执行一次子查询。

▶ 示例：每个用户的最近 3 笔订单

SQL

SELECT
  u.name,
  recent.order_id,
  recent.amount,
  recent.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT o.order_id, o.amount, o.created_at
  FROM orders o
  WHERE o.user_id = u.user_id
  ORDER BY o.created_at DESC
  LIMIT 3
) recent ON true
ORDER BY u.name, recent.created_at DESC;

TEXT 📖 仅展示

CREATE TABLE

方式	能否引用左表	Top-N 支持	性能
普通子查询	否	需 ROW_NUMBER 窗口	一趟扫描
LATERAL	是	直接 LIMIT	每行执行子查询
窗口函数	—	ROW_NUMBER + 过滤	一趟扫描

▶ 示例：每个产品的最新评论

SQL

SELECT
  p.product_name,
  r.review_text,
  r.created_at
FROM products p
LEFT JOIN LATERAL (
  SELECT review_text, created_at
  FROM reviews r
  WHERE r.product_id = p.product_id
  ORDER BY created_at DESC
  LIMIT 1
) r ON true;

TEXT 📖 仅展示

CREATE TABLE

(3) JOIN 性能基础

▶ 示例：EXPLAIN 查看连接计划

SQL

EXPLAIN
SELECT u.name, o.order_id
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.amount > 50000;

TEXT 📖 仅展示

 Hash Join
   Hash Cond: (o.user_id = u.user_id)
   ->  Seq Scan on orders
         Filter: (amount > 50000)
   ->  Hash
         ->  Seq Scan on users

JOIN 策略	适用场景	特点
Nested Loop	小表驱动大表	适合有索引的等值条件
Hash Join	等值连接，无索引	构建哈希表，大数据量首选
Merge Join	已排序数据	需要两端有序

▶ 示例：缺少索引的慢查询

SQL

EXPLAIN ANALYZE
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.email = o.customer_email;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

如果 email 列无索引，可能退化为 Nested Loop 全表扫描。

▶ 示例：添加索引提升 JOIN

SQL

CREATE INDEX idx_orders_user_id ON orders(user_id);

TEXT 📖 仅展示

CREATE TABLE

5. Practice

▶ 示例：LEFT JOIN 统计每个用户的订单数（含 0）

SQL

SELECT
  u.user_id,
  u.name,
  COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
ORDER BY order_count DESC;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

▶ 示例：FULL JOIN 对账两系统用户

SQL

SELECT
  a.user_id  AS system_a_id,
  a.email    AS system_a_email,
  b.user_id  AS system_b_id,
  b.email    AS system_b_email
FROM system_a_users a
FULL JOIN system_b_users b ON a.email = b.email
ORDER BY a.user_id NULLS LAST, b.user_id NULLS LAST;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：自连接找同日下单用户对

SQL

SELECT DISTINCT
  a.name AS user_a,
  b.name AS user_b
FROM orders oa
JOIN users a ON oa.user_id = a.user_id
JOIN orders ob ON DATE(oa.created_at) = DATE(ob.created_at)
JOIN users b ON ob.user_id = b.user_id
WHERE a.user_id < b.user_id;

TEXT 📖 仅展示

CREATE TABLE

6. Comprehensive Example

Charlie 的用户购买明细报告——4 表关联 + LATERAL 取最近订单：

SQL

SELECT
  u.name                  AS user_name,
  u.email,
  coalesce(order_summary.total_orders, 0)   AS total_orders,
  coalesce(order_summary.total_spent, 0)    AS total_spent,
  recent.order_id          AS latest_order_id,
  recent.created_at        AS latest_order_date,
  recent.amount            AS latest_amount
FROM users u
LEFT JOIN LATERAL (
  SELECT
    COUNT(*)    AS total_orders,
    SUM(amount) AS total_spent
  FROM orders o
  WHERE o.user_id = u.user_id
) order_summary ON true
LEFT JOIN LATERAL (
  SELECT order_id, created_at, amount
  FROM orders o
  WHERE o.user_id = u.user_id
  ORDER BY created_at DESC
  LIMIT 1
) recent ON true
ORDER BY total_spent DESC NULLS LAST;

TEXT 📖 仅展示

 user_name | email              | total_orders | total_spent | latest_order_id | latest_order_date     | latest_amount
-----------+--------------------+--------------+-------------+-----------------+-----------------------+--------------
 Bob       | bob@example.com    |            5 |      320000 |             205 | 2025-11-20 16:00:00   |        85000
 Alice     | alice@example.com  |            3 |      180000 |             102 | 2025-04-02 14:20:00   |        15000
 Charlie   | charlie@example.com|            0 |           0 |                 |                       |

7. JOIN 选择决策树

flowchart TD
    A[需要连接多表?] -->|否| Z[不需要 JOIN]
    A -->|是| B{是否需要保留<br/>无匹配的行?}
    B -->|否| C[INNER JOIN]
    B -->|是, 保留左表| D[LEFT JOIN]
    B -->|是, 保留右表| E[RIGHT JOIN]
    B -->|是, 两侧都保留| F[FULL JOIN]
    C --> G{需要 Top-N<br/>或引用左表列?}
    G -->|是| H[LATERAL JOIN]
    G -->|否| I[普通 INNER JOIN]
    D --> G

    style H fill:#c8e6c9
    style F fill:#fff9c4

❓ 常见问题

Q LEFT JOIN 后 WHERE 过滤右表列会变成 INNER JOIN 吗？

A 是的。WHERE o.col = 'x' 会过滤掉右表 NULL 行，等同于 INNER JOIN。应将条件移到 ON 子句。

Q 多表 JOIN 的顺序影响结果吗？

A INNER JOIN 顺序不影响结果（逻辑等价），但 LEFT JOIN 顺序会影响——左表侧是保留方，不能随意交换。

Q USING 和 ON 有何区别？

A USING(col) 要求两表有同名列且等值连接，结果合并该列为单列；ON 更灵活，支持不同列名和复杂条件。

Q LATERAL 和子查询有什么区别？

A 普通子查询不能引用同一 FROM 层的左表列；LATERAL 可以，相当于对左表每行执行一次子查询。

Q CROSS JOIN 有什么实际用途？

A 生成排列组合（如日期×维度）、生成序列、报表矩阵等。注意结果行数是两表行数之积。

Q NATURAL JOIN 为什么不推荐？

A 它隐式按所有同名列连接，新增同名列会改变连接逻辑，导致难以排查的 bug。显式写 ON 或 USING 更安全。

📖 小节

INNER JOIN 只保留匹配行，LEFT JOIN 保留左表全部
RIGHT JOIN 可改写为 LEFT JOIN 换位，FULL JOIN 保留两侧
CROSS JOIN 产生笛卡尔积，NATURAL JOIN 隐式连接需谨慎
自连接用于层级关系和同表对比，需给表起不同别名
USING 简化同名列等值连接，ON 支持任意条件
LATERAL JOIN 可引用左表列，适合 Top-N 场景
多表 JOIN 按关联关系逐层连接，注意 LEFT JOIN 顺序
EXPLAIN 可查看 JOIN 策略，索引是性能关键

📝 作业

⭐ 写一条 INNER JOIN 查询，关联 orders 和 order_items，输出每笔订单的商品总数（SUM quantity）
⭐ 用 LEFT JOIN 找出从未被购买过的产品（products LEFT JOIN order_items，过滤 NULL）
⭐⭐ 关联 users → orders → order_items → products 四张表，输出每位用户的购买明细，包含产品名称和行金额
⭐⭐ 用 LATERAL JOIN 查询每个产品类别中价格最高的 2 个产品
⭐⭐⭐ 写一条 SQL：用 FULL JOIN 对比两个系统的用户表（system_a_users / system_b_users），标记只存在于 A、只存在于 B、两边都存在的记录，并统计每类数量

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL多表连接查询

1. 你将学到

2. 故事

3. Concept

(1) JOIN 类型全景

(2) INNER JOIN

▶ 示例：查询有订单的用户

(3) LEFT JOIN

▶ 示例：包含无订单用户

▶ 示例：找出无订单用户

(4) RIGHT JOIN 与 FULL JOIN

▶ 示例：FULL JOIN 找出用户和订单的差异

▶ 示例：RIGHT JOIN 查看孤儿订单

(5) CROSS JOIN 与 NATURAL JOIN

▶ 示例：CROSS JOIN 生成所有组合

▶ 示例：NATURAL JOIN（慎用）

(6) USING 语法

▶ 示例：USING 替代 ON

(7) 自连接（Self-Join）

▶ 示例：员工与上级配对

▶ 示例：同类别产品价格对比

4. Key Points

(1) 多表连接（3+ 表）

▶ 示例：4 表关联用户购买明细

(2) LATERAL JOIN（PostgreSQL 特色）

▶ 示例：每个用户的最近 3 笔订单

▶ 示例：每个产品的最新评论

(3) JOIN 性能基础

▶ 示例：EXPLAIN 查看连接计划

▶ 示例：缺少索引的慢查询

▶ 示例：添加索引提升 JOIN

5. Practice

▶ 示例：LEFT JOIN 统计每个用户的订单数（含 0）

▶ 示例：FULL JOIN 对账两系统用户

▶ 示例：自连接找同日下单用户对

6. Comprehensive Example

7. JOIN 选择决策树

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好