PHP小码农

Appearance

内连接（INNER JOIN） ​

本节讲解内连接（Inner Join）的语义、显式 JOIN ... ON 与隐式逗号连接的差异、USING 简写、多表连接顺序，以及如何用 EXPLAIN 观察优化器对连接算法的选择。学完本节，你将能写出语义清晰、可被索引加速的内连接查询。

前置知识

阅读本节前，建议先了解：VACUUM 详解

基础概念 ​

内连接（Inner Join） 是最基本的连接方式：把两侧表中满足连接条件的行两两配对，只保留匹配成功的行。不匹配的行（无论来自左表还是右表）一律被丢弃。

概念术语说明
内连接INNER JOIN只保留两侧都匹配的行
连接条件Join ConditionON 或 USING 后的布尔表达式
连接谓词Join Predicate连接条件中的表达式，通常是等值比较
连接算法Join AlgorithmNested Loop / Hash Join / Merge Join
驱动表Outer TableNested Loop 中被逐行扫描的表
内表Inner TableNested Loop 中被反复探测的表
连接顺序Join Order优化器决定的实际执行顺序，与书写顺序无关
行数膨胀Row Inflation一对多连接导致左表行被复制

内连接的数学定义是笛卡尔积后按条件过滤：

text
A INNER JOIN B ON P  ≡  σ_P (A × B)

理解这个定义就能解释两件事：

  • 若 ON 条件写错或漏写（变成恒真），结果就是完整的笛卡尔积，行数为两表行数之积。
  • 若 ON 条件很弱（非等值条件），中间结果会非常庞大，再过滤则代价极高。

三种 JOIN 书写形式在内连接下完全等价，PostgreSQL 会解析成同一个连接关系：

写法语法是否推荐说明
显式 INNER JOIN ... ONA JOIN B ON A.id = B.aid✅ 推荐语义清晰，条件与表绑定
显式 JOIN ... USINGA JOIN B USING (id)⚠️ 慎用连接列同名才能用，输出列合并
隐式逗号连接A, B WHERE A.id = B.aid❌ 不推荐连接与过滤混淆，易漏条件
CROSS JOIN ... ONA CROSS JOIN B ON A.id = B.aid❌ 不推荐语义矛盾，可读性差

语法与用法 ​

内连接的标准语法：

text
SELECT 列列表
FROM 左表 [AS 别名]
  [INNER] JOIN 右表 [AS 别名] ON 连接条件
  [JOIN 第三表 ON 条件 ...]
[WHERE 过滤条件]
[ORDER BY 排序键];

准备本节贯穿使用的示例表：

sql
-- 用户表
CREATE TABLE users (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT    NOT NULL,
  city     TEXT    NOT NULL,
  vip      BOOLEAN NOT NULL DEFAULT false
);

-- 订单表
CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT           NOT NULL UNIQUE,
  user_id    INTEGER,                        -- 允许 NULL：游客订单
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT           NOT NULL,
  created_at DATE           NOT NULL
);

-- 订单明细表
CREATE TABLE order_items (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty        INTEGER NOT NULL CHECK (qty > 0),
  price      NUMERIC(10, 2) NOT NULL
);

-- 商品表
CREATE TABLE products (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku      TEXT    NOT NULL UNIQUE,
  name     TEXT    NOT NULL,
  category TEXT    NOT NULL
);

INSERT INTO users (name, city, vip) VALUES
  ('alice', 'beijing',  true),
  ('bob',   'shanghai', false),
  ('carol', 'shenzhen', true),
  ('dave',  'chengdu',  false);       -- 无订单用户

INSERT INTO orders (order_no, user_id, amount, status, created_at) VALUES
  ('SO-1001', 1,    607.00, 'paid',    DATE '2026-01-05'),
  ('SO-1002', 2,    199.00, 'paid',    DATE '2026-01-08'),
  ('SO-1003', 1,    518.00, 'paid',    DATE '2026-02-09'),
  ('SO-1004', 3,    195.00, 'pending', DATE '2026-02-02'),
  ('SO-1005', 2,    490.00, 'paid',    DATE '2026-02-15'),
  ('SO-1006', NULL,  89.00, 'paid',    DATE '2026-03-01'),  -- 游客订单
  ('SO-1007', 99,   350.00, 'pending', DATE '2026-03-11');  -- 用户 99 不存在

INSERT INTO products (sku, name, category) VALUES
  ('SKU-1001', '机械键盘',     'peripherals'),
  ('SKU-1002', '无线鼠标',     'peripherals'),
  ('SKU-1003', 'USB-C 扩展坞', 'accessories');

INSERT INTO order_items (order_id, product_id, qty, price) VALUES
  (1, 1, 1, 349.00),
  (1, 2, 2, 129.00),
  (2, 3, 1, 199.00),
  (3, 1, 1, 349.00),
  (3, 3, 1, 199.00);

三种等价写法对照：

sql
-- 写法一：显式 INNER JOIN（推荐）
SELECT o.order_no, u.name, o.amount
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id;

-- 写法二：省略 INNER 关键字，JOIN 即内连接
SELECT o.order_no, u.name, o.amount
FROM orders AS o
JOIN users AS u ON u.id = o.user_id;

-- 写法三：隐式逗号连接（不推荐）
SELECT o.order_no, u.name, o.amount
FROM orders AS o, users AS u
WHERE u.id = o.user_id;
text
 order_no | name  | amount
----------+-------+--------
 SO-1001  | alice | 607.00
 SO-1002  | bob   | 199.00
 SO-1003  | alice | 518.00
 SO-1004  | carol | 195.00
 SO-1005  | bob   | 490.00
(5 rows)

SO-1006（游客，user_id 为 NULL）与 SO-1007（用户 99 不存在）都被内连接丢弃。

详细说明 ​

显式 JOIN 与隐式逗号连接的对比 ​

两者的执行结果完全相同，但工程价值差异巨大。

对比项显式 JOIN ... ON隐式逗号连接
连接条件位置紧跟被连接的表混在 WHERE 中
漏写条件的后果语法错误，立即发现静默变成笛卡尔积
与过滤条件的关系清晰分开混在一起，难分辨
外连接表达直接支持无法表达（需 (+) 方言，PG 不支持）
可读性高低
官方建议推荐不推荐
sql
-- 显式写法：连接条件与过滤条件一目了然
SELECT o.order_no, u.name, o.amount
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY o.order_no;

-- 隐式写法：一旦漏写 u.id = o.user_id，结果变成 7 × 4 = 28 行
SELECT o.order_no, u.name, o.amount
FROM orders AS o, users AS u
WHERE o.status = 'paid'          -- 错误！缺少连接条件
ORDER BY o.order_no;

漏写连接条件不会报错，只会返回错误的结果集——这是隐式写法最大的风险。生产代码中应一律使用显式 JOIN ... ON。

USING 简写与输出列合并 ​

当两张表的连接列同名时，可以用 USING (列名) 简写。它与 ON 的差别不仅是写法，还有输出列的行为。

sql
-- 前提：两表的连接列同名（此处用 id 演示）
-- 用 ON 写法：两表的 id 都会出现在输出中
SELECT *
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;

-- USING 写法：连接列只输出一次（自动合并）
-- 注意 USING 要求两表连接列同名的场景，这里用自造示例说明
SELECT *
FROM (SELECT 1 AS id, 'a' AS x) AS t1
JOIN (SELECT 1 AS id, 'b' AS y) AS t2 USING (id);
text
 id | x | y
----+---+---
  1 | a | b
(1 row)

USING 的两个特性：

  • 输出列合并：同名连接列在结果中只出现一次，SELECT * 不会出现重复列。
  • 限定名不可用：合并后的列不能用表别名限定（t1.id 会报错），只能写裸列名。
sql
-- 正确：USING 合并后的列不能加表前缀
SELECT id, x, y
FROM (SELECT 1 AS id, 'a' AS x) AS t1
JOIN (SELECT 1 AS id, 'b' AS y) AS t2 USING (id);

-- 错误！列 id 已被合并，带限定名会报错
-- SELECT t1.id, t2.id FROM t1 JOIN t2 USING (id);
-- 报错：column reference "id" is ambiguous 或列不存在

USING 也支持多列：

sql
-- 多列 USING：两列需同时匹配
SELECT *
FROM (SELECT 1 AS a, 2 AS b, 'x' AS x) AS t1
JOIN (SELECT 1 AS a, 2 AS b, 'y' AS y) AS t2 USING (a, b);

多表连接与连接顺序 ​

JOIN 是左结合的：A JOIN B JOIN C 等价于 (A JOIN B) JOIN C。ON 只能引用已经出现的表，因此书写顺序会影响语法合法性，但不影响结果（内连接满足交换律与结合律）。

sql
-- 四表连接：订单 → 用户 → 明细 → 商品
SELECT
  o.order_no,
  u.name                                        AS customer,
  p.name                                        AS product,
  i.qty,
  i.qty * i.price                               AS line_amount
FROM orders AS o
JOIN users       AS u ON u.id = o.user_id
JOIN order_items AS i ON i.order_id = o.id
JOIN products    AS p ON p.id = i.product_id
WHERE o.status = 'paid'
ORDER BY o.order_no, p.sku;
text
 order_no | customer |  product     | qty | line_amount
----------+----------+--------------+-----+-------------
 SO-1001  | alice    | 机械键盘      |   1 |      349.00
 SO-1001  | alice    | 无线鼠标      |   2 |      258.00
 SO-1002  | bob      | USB-C 扩展坞  |   1 |      199.00
 SO-1003  | alice    | 机械键盘      |   1 |      349.00
 SO-1003  | alice    | USB-C 扩展坞  |   1 |      199.00
(5 rows)

交换表的书写顺序，结果完全相同：

sql
-- 以上查询的等价写法：调换 users 与 orders 的顺序
SELECT o.order_no, u.name, i.qty
FROM users AS u
JOIN orders      AS o ON o.user_id = u.id
JOIN order_items AS i ON i.order_id = o.id
ORDER BY o.order_no, i.id;

书写顺序不影响结果，但优化器可能重排执行顺序。 当连接表数超过 geqo_threshold（默认 12）时，PostgreSQL 会启用 GEQO（遗传算法）来避免计划空间爆炸。

sql
-- 查看当前 GEQO 阈值（默认 12）
SHOW geqo_threshold;

-- 临时在会话内调整
SET geqo_threshold = 10;

ON 与 WHERE 在内连接中的等价性 ​

在内连接中，ON 与 WHERE 里的条件效果完全等价：优化器会把它们一起下推，不加区分。但为了代码可读性与一致性，仍建议分工：

  • 连接条件（描述表之间的关联关系）→ 写 ON。
  • 结果过滤条件（业务筛选）→ 写 WHERE。
sql
-- 两条语句在 INNER JOIN 下结果与执行计划都相同
SELECT o.order_no, u.name
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
WHERE o.status = 'paid';

SELECT o.order_no, u.name
FROM orders AS o
JOIN users AS u ON u.id = o.user_id AND o.status = 'paid';

注意：这个等价性仅对内连接成立。在外连接中，ON 与 WHERE 的条件位置会导致截然不同的结果，详见外连接一节。

连接条件与索引的关系 ​

为连接列建索引是内连接性能的第一要素。 不同连接算法对索引的依赖不同：

连接算法是否依赖索引索引的作用
Nested Loop强依赖内表索引内表连接列有索引时可快速探测
Hash Join不依赖索引无益，需扫描两侧建哈希表
Merge Join弱依赖两侧有索引可省去排序步骤
sql
-- 连接列（外键列）必须有索引
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

-- 复合索引：连接列在前，过滤/排序列在后
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

用 EXPLAIN 对比有无索引的差异：

sql
-- 有索引：小表驱动时通常选择 Nested Loop
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_no, u.name
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
WHERE u.city = 'beijing';
text
 Nested Loop  (cost=0.15..24.85 rows=5 width=40) (actual time=0.028..0.061 rows=3 loops=1)
   ->  Seq Scan on users u  (cost=0.00..1.04 rows=1 width=36) (actual time=0.012..0.014 rows=1 loops=1)
         Filter: (city = 'beijing'::text)
         Rows Removed by Filter: 3
   ->  Index Scan using idx_orders_user_id on orders o  (cost=0.15..23.79 rows=15 width=36) (actual time=0.013..0.041 rows=3 loops=1)
         Index Cond: (user_id = u.id)
 Planning Time: 0.312 ms
 Execution Time: 0.098 ms

EXPLAIN 观察三种连接算法 ​

PostgreSQL 有三种连接算法，优化器按代价自动选择。理解它们的适用条件，是读懂执行计划的关键。

算法适用条件代价特点计划节点名
Nested Loop驱动表行数少，内表连接列有索引驱动行数 × 内表探测代价Nested Loop
Hash Join等值连接，两侧数据量大扫描两侧 + 建哈希表（受 work_mem 限制）Hash Join
Merge Join两侧已按连接键有序需排序（或在有序索引上顺序扫描）Merge Join
sql
-- 查看当前 work_mem，它决定 Hash Join 能否在内存中完成
SHOW work_mem;
sql
-- 观察 Hash Join：大表之间等值连接
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_no, u.name, o.amount
FROM orders AS o
JOIN users AS u ON u.id = o.user_id;
text
 Hash Join  (cost=1.09..2.36 rows=7 width=44) (actual time=0.036..0.049 rows=5 loops=1)
   Hash Cond: (o.user_id = u.id)
   ->  Seq Scan on orders o  (cost=0.00..1.07 rows=7 width=40) (actual time=0.010..0.012 rows=7 loops=1)
   ->  Hash  (cost=1.04..1.04 rows=4 width=36) (actual time=0.021..0.021 rows=4 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on users u  (cost=0.00..1.04 rows=4 width=36) (actual time=0.008..0.010 rows=4 loops=1)
 Planning Time: 0.298 ms
 Execution Time: 0.088 ms

关键读法：

  • Hash Cond 展示哈希连接的键，仅等值条件才能用 Hash Join。
  • Batches: 1 表示哈希表完全装入内存；若 Batches > 1，说明内存不足而溢写磁盘，需要调大 work_mem。
  • loops=1 在 Nested Loop 的节点上可能大于 1（等于驱动行数），这是判断"内表被探测了多少次"的依据。
sql
-- 强制关闭某类算法（仅用于诊断，不要写进生产代码）
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
RESET ALL;                        -- 诊断完恢复默认

实战示例 ​

下面用完整流程做一份"商品销售汇总报表"。

sql
-- 步骤 1：基础内连接 —— 每笔订单行及其用户
SELECT o.order_no, u.name AS customer, o.created_at, i.qty, i.price
FROM orders AS o
JOIN users       AS u ON u.id = o.user_id
JOIN order_items AS i ON i.order_id = o.id
ORDER BY o.order_no, i.id;
text
 order_no | customer | created_at | qty | price
----------+----------+------------+-----+--------
 SO-1001  | alice    | 2026-01-05 |   1 | 349.00
 SO-1001  | alice    | 2026-01-05 |   2 | 129.00
 SO-1002  | bob      | 2026-01-08 |   1 | 199.00
 SO-1003  | alice    | 2026-02-09 |   1 | 349.00
 SO-1003  | alice    | 2026-02-09 |   1 | 199.00
(5 rows)
sql
-- 步骤 2：四表连接 + 聚合 —— 按商品分类统计销售额
SELECT
  p.category,
  count(*)                AS line_cnt,
  sum(i.qty)              AS total_qty,
  sum(i.qty * i.price)    AS gmv
FROM orders      AS o
JOIN order_items AS i ON i.order_id = o.id
JOIN products    AS p ON p.id = i.product_id
WHERE o.status = 'paid'                 -- 过滤条件写 WHERE
GROUP BY p.category
ORDER BY gmv DESC;
text
  category   | line_cnt | total_qty |   gmv
-------------+----------+-----------+---------
 peripherals |        3 |         4 | 1206.00
 accessories |        2 |         2 |  398.00
(2 rows)
sql
-- 步骤 3：多表连接 + 用户维度汇总
SELECT
  u.name                                                    AS customer,
  u.city,
  count(DISTINCT o.id)                                      AS order_cnt,
  sum(i.qty * i.price)                                      AS gmv
FROM users       AS u
JOIN orders      AS o ON o.user_id = u.id
JOIN order_items AS i ON i.order_id = o.id
WHERE o.status = 'paid'
GROUP BY u.name, u.city
ORDER BY gmv DESC;
text
 customer |   city   | order_cnt |   gmv
----------+----------+-----------+---------
 alice    | beijing  |         2 | 1436.00
 bob      | shanghai |         1 |  199.00
(2 rows)
sql
-- 步骤 4：验证连接结果，检查连接键是否唯一（避免行数膨胀）
SELECT
  count(*)              AS rows_all,
  count(DISTINCT u.id)  AS distinct_users
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;
text
 rows_all | distinct_users
----------+----------------
        5 |              2

rows_all 大于 distinct_users 说明是"一对多"（一个用户多笔订单），此时直接聚合 o.amount 会重复计数。

注意事项 ​

  • 不要漏写 ON 条件。 这是内连接最常见的错误，不会报错但结果错误（笛卡尔积）。
sql
-- 错误！缺少 ON 条件，结果为 7 × 4 = 28 行
SELECT o.order_no, u.name FROM orders AS o JOIN users AS u;

-- 正确
SELECT o.order_no, u.name FROM orders AS o JOIN users AS u ON u.id = o.user_id;
  • 不要用隐式逗号连接。 一旦漏写 WHERE 中的连接条件，错误会静默发生。
sql
-- 不推荐
SELECT o.order_no, u.name FROM orders o, users u WHERE u.id = o.user_id;
  • 连接键不唯一会导致聚合膨胀。 一对多连接后直接 sum 会重复累加，需先聚合再连接。
sql
-- 错误！orders 与 order_items 一对多，o.amount 被重复累加
SELECT sum(o.amount) AS wrong_total
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id;

-- 正确：先按订单聚合明细，再连接
WITH item_sum AS (
  SELECT order_id, sum(qty * price) AS amount
  FROM order_items
  GROUP BY order_id
)
SELECT sum(s.amount) AS correct_total
FROM orders AS o
JOIN item_sum AS s ON s.order_id = o.id;
  • 连接列类型必须一致。 类型不一致会触发隐式转换，导致索引失效。
sql
-- 不推荐：orders.user_id 是 text，users.id 是 integer
-- CREATE TABLE bad_orders (user_id TEXT);
-- SELECT * FROM bad_orders b JOIN users u ON u.id = b.user_id;   -- 索引失效
  • USING 合并列后不能再加表别名限定。 写成 t1.id 会报列不存在或歧义错误。
  • INNER JOIN 与 JOIN 完全等价。 INNER 只是可选的语义强调词。
  • 连接表过多会让计划空间爆炸。 超过 geqo_threshold 时优化器切换到遗传算法，可能得不到最优计划，建议用 CTE 拆分。
  • SELECT * 在多表连接时会输出重复列名。 客户端取值时后者会覆盖前者，应显式列出需要的列。

最佳实践 ​

  1. 统一使用显式 JOIN ... ON。 不用逗号连接，不用 CROSS JOIN ... ON 表达内连接，USING 仅在明确需要列合并时使用。

  2. 连接条件写 ON，过滤条件写 WHERE。 即使在内连接中两者等价，保持这个分工能让 SQL 的意图自解释。

  3. 为所有连接列建立索引。 外键列、业务关联列都应建索引，否则连接会退化为全表扫描。

sql
CREATE INDEX idx_orders_user_id          ON orders (user_id);
CREATE INDEX idx_order_items_order_id    ON order_items (order_id);
CREATE INDEX idx_order_items_product_id  ON order_items (product_id);
  1. 连接前验证连接键基数。 用 count(*) 与 count(DISTINCT key) 对比，判断是一对一还是一对多。
sql
-- 验证 orders.user_id 对 users.id 的基数关系
SELECT count(*) AS total, count(DISTINCT user_id) AS distinct_users FROM orders;

  1. 表别名用有意义的短名。 orders AS o、users AS u 比 t1、t2 更易读；别名在 ON、SELECT、ORDER BY 中保持一致。

  2. 多表复杂查询分层书写。 用 CTE 把"过滤、聚合、关联"分开，PostgreSQL 12+ 对非递归 CTE 默认内联，不会带来额外性能损失。

sql
-- PostgreSQL 12+：非递归 CTE 默认内联，可放心用于组织 SQL 结构
WITH paid_orders AS (
  SELECT id, order_no, user_id, amount FROM orders WHERE status = 'paid'
)
SELECT u.name, count(*) AS cnt, sum(p.amount) AS total
FROM users AS u
JOIN paid_orders AS p ON p.user_id = u.id
GROUP BY u.name;
  1. 用 EXPLAIN (ANALYZE, BUFFERS) 检查连接算法与行数估算。 重点看 rows= 与 actual rows= 的差距，差距过大说明统计信息过期，需要 ANALYZE。
sql
ANALYZE orders, users, order_items, products;

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_no, u.name
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
WHERE o.status = 'paid';

下一节

继续学习：外连接（LEFT / RIGHT / FULL JOIN）

参考链接 ​