PHP小码农

Appearance

表连接基础 ​

本节讲解如何用 JOIN 把多张表的数据组合在一起，包括内连接、外连接、交叉连接、自连接、LATERAL 连接与 UNION 家族的差异。学完本节，你将能读懂并写出规范的连接查询，避免连接导致的行数膨胀与性能陷阱。

前置知识

阅读本节前，建议先了解：聚合查询与分组

基础概念 ​

真实业务的数据总是分散在多张表中（订单、商品、客户），要把它们组合起来就需要连接（Join）。

概念术语说明
连接Join按连接条件把两张表的行组合起来
连接条件Join Condition通常是 ON a.key = b.key，决定行如何配对
左表 / 右表Left / Right Table出现在 JOIN 关键字前面 / 后面的表
内连接INNER JOIN只保留两侧都匹配上的行
左外连接LEFT OUTER JOIN保留左表全部行，右表无匹配则补 NULL
右外连接RIGHT OUTER JOIN保留右表全部行，左表无匹配则补 NULL
全外连接FULL OUTER JOIN两侧全部保留，无匹配的一侧补 NULL
交叉连接CROSS JOIN笛卡尔积，每行两两组合
自连接Self Join同一张表与自身连接，需用别名区分
连接谓词Join Predicate连接条件表达式，ON 中可写任意布尔条件
连接顺序Join Order优化器决定的表连接先后顺序，不由书写顺序决定

连接的数学本质是笛卡尔积后按条件过滤。理解这一点就能解释两个最常见的困惑：

  • 若 ON 条件写错（例如忘写或写成恒真），结果行数会变成两表行数的乘积。
  • 若连接键在右表中不唯一（一对多），左表的每一行会被复制多次，聚合结果随之膨胀。

三种连接的语义差异，可以用"是否保留不匹配行"来概括：

连接类型左表不匹配行右表不匹配行行数（大致）
INNER JOIN丢弃丢弃匹配组合数
LEFT JOIN保留（右表列补 NULL）丢弃≥ 内连接行数
RIGHT JOIN丢弃保留（左表列补 NULL）≥ 内连接行数
FULL JOIN保留保留≥ 左、右表行数
CROSS JOIN无条件保留无条件保留左行数 × 右行数

语法与用法 ​

连接的基本语法：

text
SELECT 列列表
FROM 左表 [AS 别名]
[连接类型] JOIN 右表 [AS 别名]
  ON 连接条件
[WHERE 过滤条件];

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

sql
-- 客户表
CREATE TABLE customers (
  id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code    TEXT    NOT NULL UNIQUE,
  name    TEXT    NOT NULL,
  region  TEXT    NOT NULL
);

-- 订单表：customer_id 可为 NULL（游客下单），或指向不存在的客户（脏数据）
CREATE TABLE orders (
  id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    TEXT          NOT NULL UNIQUE,
  customer_id INTEGER,                       -- 故意不加外键，便于演示不匹配场景
  amount      NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status      TEXT          NOT NULL DEFAULT 'pending',
  created_at  DATE          NOT NULL
);

INSERT INTO customers (code, name, region) VALUES
  ('C-001', 'alice', 'east'),
  ('C-002', 'bob',   'north'),
  ('C-003', 'carol', 'south'),
  ('C-004', 'dave',  'east');      -- 没有任何订单的客户

INSERT INTO orders (order_no, customer_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 不存在

五种连接的写法对照：

sql
-- 1. INNER JOIN：只保留两侧都匹配的行
SELECT o.order_no, c.name, o.amount
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id;

-- 2. LEFT JOIN：保留全部订单，客户信息缺失时补 NULL
SELECT o.order_no, c.name, o.amount
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id;

-- 3. RIGHT JOIN：保留全部客户，订单缺失时补 NULL
SELECT o.order_no, c.name, o.amount
FROM orders AS o
RIGHT JOIN customers AS c ON c.id = o.customer_id;

-- 4. FULL JOIN：两侧全部保留
SELECT o.order_no, c.name, o.amount
FROM orders AS o
FULL JOIN customers AS c ON c.id = o.customer_id;

-- 5. CROSS JOIN：笛卡尔积
SELECT c.name, s.status
FROM customers AS c
CROSS JOIN (VALUES ('pending'), ('paid')) AS s (status);

详细说明 ​

内连接：INNER JOIN ​

内连接只输出两侧都满足 ON 条件的行。INNER 关键字可以省略，直接写 JOIN 也是内连接。

sql
-- 查询每个订单及其客户名（只查有客户信息的订单）
SELECT o.order_no, o.amount, c.name, c.region
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.order_no;
text
 order_no | amount | name  | region
----------+--------+-------+--------
 SO-1001  | 607.00 | alice | east
 SO-1002  | 199.00 | bob   | north
 SO-1003  | 518.00 | alice | east
 SO-1004  | 195.00 | carol | south
 SO-1005  | 490.00 | bob   | north
(5 rows)

结果只有 5 行：SO-1006（customer_id 为 NULL）与 SO-1007（customer_id = 99 不存在）都被排除。

ON 条件不限于等值比较，可以写任意布尔表达式；但等值连接之外的写法（如不等值连接）往往性能较差：

sql
-- 等值连接（最常见，可走索引 / 哈希连接）
SELECT o.order_no, c.name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;

-- 不等值连接：找出金额高于该客户其他订单的订单
SELECT a.order_no, a.amount, b.order_no AS other_no, b.amount AS other_amount
FROM orders AS a
JOIN orders AS b
  ON a.customer_id = b.customer_id
 AND a.amount > b.amount
ORDER BY a.order_no, b.order_no;

-- USING 语法糖：连接列同名时可简写
SELECT o.order_no, c.name
FROM orders AS o
JOIN customers AS c USING (id);        -- 等价于 ON c.id = o.id

USING 与 NATURAL JOIN 会合并同名列，输出中同名连接列只出现一次。实际项目里更推荐显式 ON：语义清晰，列名变更时不会静默改变连接行为。

外连接：LEFT / RIGHT / FULL ​

外连接的关键在于保留不匹配的行并用 NULL 补齐。LEFT JOIN 表达"以左表为主体"的语义，是最常用的外连接形式。

sql
-- 列出所有客户及其订单（没有订单的客户也要列出）
SELECT c.code, c.name, o.order_no, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.code, o.order_no;
text
 code  | name  | order_no | amount
-------+-------+----------+--------
 C-001 | alice | SO-1001  | 607.00
 C-001 | alice | SO-1003  | 518.00
 C-002 | bob   | SO-1002  | 199.00
 C-002 | bob   | SO-1005  | 490.00
 C-003 | carol | SO-1004  | 195.00
 C-004 | dave  | (null)   | (null)
(6 rows)

RIGHT JOIN 与 LEFT JOIN 只是视角不同，把左右表互换即可等价改写。实际开发中建议统一用 LEFT JOIN，减少理解成本：

sql
-- 以下两条语句完全等价
SELECT c.name, o.order_no
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

SELECT c.name, o.order_no
FROM orders AS o
RIGHT JOIN customers AS c ON c.id = o.customer_id;

FULL JOIN 会保留两侧所有行，用于排查"孤儿数据"：

sql
-- 找出没有订单的客户，以及找不到对应客户的订单
SELECT c.code, c.name, o.order_no, o.customer_id
FROM customers AS c
FULL JOIN orders AS o ON o.customer_id = c.id
WHERE c.id IS NULL OR o.id IS NULL
ORDER BY c.code NULLS LAST, o.order_no;
text
 code  | name  | order_no | customer_id
-------+-------+----------+-------------
 C-004 | dave  | (null)   |      (null)
 (null)|(null) | SO-1006  |      (null)
 (null)|(null) | SO-1007  |          99
(3 rows)

ON 与 WHERE 的位置差异 ​

在外连接中，ON 与 WHERE 的过滤时机不同，结果差异巨大：

  • ON 中的条件在连接时生效，只影响"哪些右表行能配上"；不匹配的左表行仍会被保留（补 NULL）。
  • WHERE 中的条件在连接之后生效，会直接过滤掉结果行。
sql
-- 写法 A：把状态条件放在 ON 中 —— 仍保留所有客户
SELECT c.name, o.order_no, o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid'
ORDER BY c.name, o.order_no;
text
 name  | order_no | status
-------+----------+--------
 alice | SO-1001  | paid
 alice | SO-1003  | paid
 bob   | SO-1002  | paid
 bob   | SO-1005  | paid
 carol | (null)   | (null)
 dave  | (null)   | (null)
(6 rows)
sql
-- 写法 B：把状态条件放在 WHERE 中 —— 未付款客户被整体过滤掉
SELECT c.name, o.order_no, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.name, o.order_no;
text
 name  | order_no | status
-------+----------+--------
 alice | SO-1001  | paid
 alice | SO-1003  | paid
 bob   | SO-1002  | paid
 bob   | SO-1005  | paid
(4 rows)

写法 B 的 WHERE o.status = 'paid' 对 NULL 求值为 NULL（非 true），因此 carol、dave 这两行"右表全 NULL"的行被丢弃。这实际上把外连接退化成了内连接——这是使用 LEFT JOIN 时最常见的逻辑错误。

如果确实想"只保留有已付款订单的客户，但允许客户订单列表为空"，正确做法是：

sql
-- 正确：先筛出符合条件的订单，再外连接
WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT c.name, o.order_no
FROM customers AS c
LEFT JOIN paid_orders AS o ON o.customer_id = c.id
ORDER BY c.name, o.order_no;

-- 或者：把条件写到 ON，同时用 WHERE 判断"是否存在已付款订单"
SELECT c.name, o.order_no
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
WHERE o.id IS NOT NULL          -- 只保留真正有已付款订单的客户
ORDER BY c.name, o.order_no;

在内连接中，ON 与 WHERE 的条件效果等价（优化器会把它们一起下推），但为了可读性仍建议：连接条件写在 ON，过滤条件写在 WHERE。

sql
-- 以下两条在 INNER JOIN 下结果相同
SELECT o.order_no, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid';
SELECT o.order_no, c.name FROM orders o JOIN customers c ON c.id = o.customer_id AND o.status = 'paid';

多表连接与连接顺序 ​

实际查询常涉及三张以上表。连接是左结合的：A JOIN B JOIN C 等价于 (A JOIN B) JOIN C，ON 只能引用已出现的表。

sql
-- 三表连接示例（引入商品表）
CREATE TABLE products (
  id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku     TEXT    NOT NULL UNIQUE,
  name    TEXT    NOT NULL,
  price   NUMERIC(10, 2) 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),
  unit_price  NUMERIC(10, 2) NOT NULL
);

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

INSERT INTO order_items (order_id, product_id, qty, unit_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);

-- 订单 → 客户 → 明细 → 商品 四表连接
SELECT
  o.order_no,
  c.name       AS customer,
  p.name       AS product,
  i.qty,
  i.qty * i.unit_price AS line_amount
FROM orders AS o
JOIN customers   AS c ON c.id = o.customer_id
JOIN order_items AS i ON i.order_id = o.id
JOIN products    AS p ON p.id = i.product_id
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
-- 用 EXPLAIN 观察实际连接顺序与算法
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_no, c.name, p.name
FROM orders AS o
JOIN customers   AS c ON c.id = o.customer_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';

PostgreSQL 支持三种连接算法，优化器按代价自动选择：

算法适用条件特点
Nested Loop外侧行数少，内侧连接列有索引逐行探测，小表驱动大表时高效
Hash Join等值连接，两侧数据量大建哈希表，需内存（work_mem）
Merge Join两侧已按连接键有序需排序或利用索引顺序，适合大表等值连接

自连接与 LATERAL ​

自连接（Self Join） 指同一张表连接自身，必须用不同别名区分，否则会报"表名重复"错误。

sql
-- 自连接：找出同一客户在更晚日期下的后续订单
SELECT
  a.order_no AS earlier_no,
  a.created_at AS earlier_date,
  b.order_no AS later_no,
  b.created_at AS later_date
FROM orders AS a
JOIN orders AS b
  ON b.customer_id = a.customer_id
 AND b.created_at > a.created_at
ORDER BY a.order_no, b.order_no;
text
 earlier_no | earlier_date | later_no | later_date
------------+--------------+----------+------------
 SO-1001    | 2026-01-05   | SO-1003  | 2026-02-09
 SO-1002    | 2026-01-08   | SO-1005  | 2026-02-15
(2 rows)

自连接配合窗口函数可以实现很多"行间比较"，但窗口函数通常更简洁（本节只需了解连接能力）：

sql
-- 用窗口函数实现同样效果（PostgreSQL 8.4+）
SELECT order_no, created_at,
       lag(order_no)  OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_no,
       lag(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_date
FROM orders
ORDER BY order_no;

LATERAL 是 PostgreSQL 的强大扩展，它让右侧子查询可以引用左侧表的列，相当于对左表每一行执行一次右侧子查询。

sql
-- PostgreSQL 9.3+
-- 每个客户取金额最高的 2 笔订单（Top-N per group）
SELECT c.code, c.name, t.order_no, t.amount
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.order_no, o.amount
  FROM orders AS o
  WHERE o.customer_id = c.id
  ORDER BY o.amount DESC
  LIMIT 2
) AS t ON true                    -- LATERAL 子查询常用 ON true
ORDER BY c.code, t.amount DESC NULLS LAST;
text
 code  | name  | order_no | amount
-------+-------+----------+--------
 C-001 | alice | SO-1001  | 607.00
 C-001 | alice | SO-1003  | 518.00
 C-002 | bob   | SO-1005  | 490.00
 C-002 | bob   | SO-1002  | 199.00
 C-003 | carol | SO-1004  | 195.00
 C-004 | dave  | (null)   | (null)
(6 rows)

LATERAL 与普通子查询的区别：

对比项普通子查询LATERAL 子查询
能否引用左侧表列不能能
求值次数一次（可视为独立集合）概念上每行一次
LIMIT 语义全局限制每行分别限制
典型用途独立派生表Top-N per group、展开函数
常见写法JOIN (SELECT ...) ON ...JOIN LATERAL (SELECT ... WHERE x = outer.x) ON true

LATERAL 也用于展开返回集合的函数（集合返回函数，Set-Returning Function，简称 SRF）：

sql
-- 把数组/函数结果展开成多行，并与左表连接
SELECT c.name, tag
FROM customers AS c
CROSS JOIN LATERAL (
  SELECT unnest(ARRAY['new', 'vip', 'active']) AS tag
) AS t
ORDER BY c.name, tag;

UNION 家族：纵向合并结果集 ​

连接是横向拼列，UNION 是纵向拼行。两者完全不同，常被初学者混淆。

sql
-- UNION：合并两个结果集并去重
SELECT name AS entity, 'customer' AS type FROM customers
UNION
SELECT order_no, 'order' FROM orders
ORDER BY type, entity;

-- UNION ALL：合并但不去重（更快，推荐默认使用）
SELECT name AS entity, 'customer' AS type FROM customers
UNION ALL
SELECT order_no, 'order' FROM orders
ORDER BY type, entity;

-- INTERSECT：取交集
SELECT customer_id FROM orders WHERE status = 'paid'
INTERSECT
SELECT id FROM customers WHERE region = 'east';

-- EXCEPT：取差集（在第一个结果集中但不在第二个中）
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL;

UNION 与 JOIN、UNION ALL 的差异：

对比项UNIONUNION ALLJOIN
结果方向纵向加行纵向加行横向加列
是否去重去重不去重不去重
性能需排序/哈希去重，较慢直接拼接，快取决于连接算法
列要求两侧列数、类型兼容同左无列数限制
语义集合的并追加关系的组合
sql
-- UNION 的列要求：列数相同，类型可兼容（按位置对应，列名取第一个查询的）
SELECT 1 AS a, 'x' AS b
UNION ALL
SELECT 2, 'y';

实战示例 ​

下面用一个完整流程，从基础连接逐步做到"客户消费报表"。

sql
-- 步骤 1：内连接 —— 只统计有客户的订单
SELECT o.order_no, c.name, o.amount, o.status
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.order_no;
text
 order_no | name  | amount | status
----------+-------+--------+---------
 SO-1001  | alice | 607.00 | paid
 SO-1002  | bob   | 199.00 | paid
 SO-1003  | alice | 518.00 | paid
 SO-1004  | carol | 195.00 | pending
 SO-1005  | bob   | 490.00 | paid
(5 rows)
sql
-- 步骤 2：外连接 —— 列出所有客户的下单情况，含 0 单客户
SELECT
  c.code,
  c.name,
  count(o.id)                        AS order_cnt,
  coalesce(sum(o.amount), 0)         AS total_amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.code, c.name
ORDER BY total_amount DESC;
text
 code  | name  | order_cnt | total_amount
-------+-------+-----------+--------------
 C-001 | alice |         2 |      1125.00
 C-002 | bob   |         2 |       689.00
 C-003 | carol |         1 |       195.00
 C-004 | dave  |         0 |         0.00
(4 rows)

注意 count(o.id)（3 行）比 count(*)（4 行）更准确：dave 没有订单时 count(*) 会数到 1，而 count(o.id) 因 o.id 为 NULL 而返回 0。

sql
-- 步骤 3：外连接 + 条件放 ON —— 只统计已付款订单，但保留所有客户
SELECT
  c.code,
  c.name,
  count(o.id)                      AS paid_cnt,
  coalesce(sum(o.amount), 0)       AS paid_amount
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.code, c.name
ORDER BY paid_amount DESC;
text
 code  | name  | paid_cnt | paid_amount
-------+-------+----------+-------------
 C-001 | alice |        2 |     1125.00
 C-002 | bob   |        2 |      689.00
 C-003 | carol |        0 |        0.00
 C-004 | dave  |        0 |        0.00
(4 rows)
sql
-- 步骤 4：FULL JOIN 做数据质量检查 —— 找出孤儿订单与无单客户
SELECT
  coalesce(c.code, '(无此客户)')  AS customer_code,
  c.name,
  coalesce(o.order_no, '(无订单)') AS order_no,
  o.customer_id
FROM customers AS c
FULL JOIN orders AS o ON o.customer_id = c.id
WHERE c.id IS NULL OR o.id IS NULL
ORDER BY customer_code, order_no;
text
 customer_code | name  |   order_no   | customer_id
---------------+-------+--------------+-------------
 (无此客户)     |(null) | SO-1006      |      (null)
 (无此客户)     |(null) | SO-1007      |          99
 C-004         | dave  | (无订单)      |      (null)
(3 rows)
sql
-- 步骤 5：四表连接 —— 生成订单明细报表
SELECT
  o.order_no,
  c.name                                    AS customer,
  p.name                                    AS product,
  i.qty,
  i.unit_price,
  i.qty * i.unit_price                      AS line_amount
FROM orders AS o
JOIN customers   AS c ON c.id = o.customer_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, line_amount DESC;
text
 order_no | customer |  product     | qty | unit_price | line_amount
----------+----------+--------------+-----+------------+-------------
 SO-1001  | alice    | 机械键盘      |   1 |     349.00 |      349.00
 SO-1001  | alice    | 无线鼠标      |   2 |     129.00 |      258.00
 SO-1002  | bob      | USB-C 扩展坞  |   1 |     199.00 |      199.00
 SO-1003  | alice    | 机械键盘      |   1 |     349.00 |      349.00
 SO-1003  | alice    | USB-C 扩展坞  |   1 |     199.00 |      199.00
(5 rows)
sql
-- 步骤 6：LATERAL —— 每位客户取金额最高的 2 笔订单
SELECT c.code, c.name, t.order_no, t.amount, t.status
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT order_no, amount, status
  FROM orders AS o
  WHERE o.customer_id = c.id
  ORDER BY o.amount DESC
  LIMIT 2
) AS t ON true
ORDER BY c.code, t.amount DESC NULLS LAST;
text
 code  | name  | order_no | amount | status
-------+-------+----------+--------+---------
 C-001 | alice | SO-1001  | 607.00 | paid
 C-001 | alice | SO-1003  | 518.00 | paid
 C-002 | bob   | SO-1005  | 490.00 | paid
 C-002 | bob   | SO-1002  | 199.00 | paid
 C-003 | carol | SO-1004  | 195.00 | pending
 C-004 | dave  | (null)   | (null) | (null)
(6 rows)
sql
-- 步骤 7：UNION ALL —— 把"客户"与"订单"合并成统一的实体清单
SELECT 'customer' AS entity_type, code AS biz_no, name AS label FROM customers
UNION ALL
SELECT 'order', order_no, status FROM orders
ORDER BY entity_type, biz_no;
text
 entity_type | biz_no  |  label
-------------+---------+---------
 customer    | C-001   | alice
 customer    | C-002   | bob
 customer    | C-003   | carol
 customer    | C-004   | dave
 order       | SO-1001 | paid
 order       | SO-1002 | paid
 order       | SO-1003 | paid
 order       | SO-1004 | pending
 order       | SO-1005 | paid
 order       | SO-1006 | paid
 order       | SO-1007 | pending
(11 rows)
sql
-- 步骤 8：EXCEPT —— 找出从未下单的客户
SELECT id, name FROM customers
EXCEPT
SELECT c.id, c.name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id;
text
 id | name
----+------
  4 | dave
(1 row)

注意事项 ​

  • 外连接中把右表条件写进 WHERE 会让 LEFT JOIN 退化为 INNER JOIN。 这是最高频的逻辑错误。判断依据是"该条件是否允许右表列为 NULL"。
sql
-- 错误！条件放在 WHERE 中，未匹配行（右表全 NULL）被整体过滤
SELECT c.name, o.order_no
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid';

-- 正确：条件放在 ON 中，未匹配客户仍保留
SELECT c.name, o.order_no
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid';
  • 连接键不唯一会导致行数膨胀与聚合错误。 一对多连接后直接 sum，会因行复制而重复计数。
sql
-- 错误！订单与明细一对多，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 order_amount AS (
  SELECT order_id, sum(qty * unit_price) AS amount
  FROM order_items
  GROUP BY order_id
)
SELECT sum(a.amount) AS correct_total
FROM orders AS o
JOIN order_amount AS a ON a.order_id = o.id;
  • 忘记写 ON 条件会得到笛卡尔积。 JOIN 后不写 ON（或写恒真条件）时，PostgreSQL 会给出两表行数的乘积，通常会造成查询卡死或内存暴涨。
sql
-- 错误！缺少 ON 条件，等价于 CROSS JOIN
SELECT a.order_no, c.name FROM orders AS a JOIN customers AS c;

-- 正确
SELECT a.order_no, c.name FROM orders AS a JOIN customers AS c ON c.id = a.customer_id;
  • count(*) 在外连接后不能用来判断"是否存在匹配行"。 无匹配时外连接仍会产生一行，count(*) 会返回 1。应使用 count(右表列) 或 count(*) FILTER (WHERE 右表主键 IS NOT NULL)。
sql
-- 错误：dave 没有订单，但 count(*) 也会算 1
SELECT c.name, count(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.name;

-- 正确：用右表非空列计数
SELECT c.name, count(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.name;
  • 连接列类型不一致会阻止索引使用。 例如 integer 与 bigint、text 与 varchar 之间的隐式转换，可能导致无法走索引。应保持连接列类型一致。
sql
-- 不推荐：customer_id 为 text 而 customers.id 为 integer，触发隐式转换
-- CREATE TABLE bad_orders (customer_id TEXT);
-- SELECT * FROM bad_orders b JOIN customers c ON c.id = b.customer_id;
  • LATERAL 漏写 ON true 会报语法错误。 因为 JOIN 语法要求 ON 或 USING（CROSS JOIN LATERAL 除外）。
  • UNION 会做去重排序，代价高于 UNION ALL。 明确不需要去重时一律用 UNION ALL。
  • NATURAL JOIN 与 USING 是隐式契约。 依赖列名相同，一旦表结构演进（新增同名列）会静默改变连接语义，生产代码应避免。
  • 连接太多表会让优化器计划空间爆炸。 PostgreSQL 默认用 GEQO（遗传算法）处理超过 geqo_threshold（默认 12）个表的连接。大查询应拆分为若干 CTE 或中间表。

最佳实践 ​

  1. 统一使用显式 JOIN ... ON。 不用 NATURAL JOIN，不用逗号连接（隐式交叉连接），避免 USING 带来的列合并歧义。
  2. 外连接优先用 LEFT JOIN。 需要"以右表为主体"时把表顺序调换，而不是写 RIGHT JOIN，可读性更好。
  3. 牢记"过滤条件的位置决定语义"：连接条件（描述表关系）写 ON，结果过滤（业务筛选）写 WHERE。在 LEFT JOIN 中尤其要反复确认。
  4. 连接前先确认连接键的唯一性。 用 count(*) 与 count(DISTINCT key) 对比，确认是否为"多对一"，避免行膨胀。
sql
-- 连接前先验证唯一性：两者相等说明连接键唯一
SELECT count(*) AS rows_all, count(DISTINCT id) AS rows_unique FROM customers;
  1. 为所有连接列建索引。 外键列、业务关联列都应有索引，否则连接会退化为全表扫描。
sql
-- 连接列（尤其是外键列）必须有索引
CREATE INDEX idx_orders_customer_id ON orders (customer_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. 需要"每组取前 N 行"时用 LATERAL + LIMIT。 它比窗口函数 ROW_NUMBER() 更直观，且在配合索引时能高效地"每行只取少量数据"。
sql
-- 每个客户最新的一笔订单
SELECT c.name, t.order_no, t.created_at
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT order_no, created_at
  FROM orders AS o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 1
) AS t ON true
ORDER BY c.name;
  1. 用 EXPLAIN (ANALYZE, BUFFERS) 检查连接算法与行数估算。 重点看 rows= 的估算值与 actual rows= 的差距：差距过大时统计信息可能过期，需要 ANALYZE。
sql
ANALYZE orders, customers, order_items;

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_no, c.name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'paid';
  1. 多表复杂查询用 CTE 分层。 每层只负责一件事（过滤、聚合、关联），既便于阅读，也让优化器有更多优化空间（PostgreSQL 12+ 对 CTE 默认不做物化，可内联）。
sql
-- PostgreSQL 12+：非递归 CTE 默认按需内联
WITH paid_orders AS (
  SELECT id, order_no, customer_id, amount
  FROM orders
  WHERE status = 'paid'
),
customer_paid AS (
  SELECT customer_id, count(*) AS cnt, sum(amount) AS amount
  FROM paid_orders
  GROUP BY customer_id
)
SELECT c.code, c.name,
       coalesce(p.cnt, 0)    AS paid_cnt,
       coalesce(p.amount, 0) AS paid_amount
FROM customers AS c
LEFT JOIN customer_paid AS p ON p.customer_id = c.id
ORDER BY paid_amount DESC;

下一节

继续学习：Linux 安装 PostgreSQL

参考链接 ​