29 内连接与左连接

连接匹配的行，并使用 INNER JOIN 和 LEFT JOIN 保留未匹配的行。

连接会根据匹配条件，把两个（或更多）表中的行组合起来。它可以把关系数据重建成应用程序所需的形状。INNER JOIN 只保留两侧都满足条件的行；LEFT JOIN（也称 LEFT OUTER JOIN）保留左侧的每一行，右侧没有匹配时用 NULL 填充。掌握连接，就是掌握 SQL。

本章要点

INNER JOIN 配合 ON：只保留匹配的行
LEFT JOIN：保留左侧全部行，未匹配时右侧为 NULL
使用表别名（c、o）提高可读性
了解在 JOIN 目标中筛选与在 WHERE 子句中筛选的区别
使用 USING (col) 简写 ON a.col = b.col

示例

示例 29.1 内连接与左连接

-- Two small tables for joins
DROP TABLE IF EXISTS orders_example;
DROP TABLE IF EXISTS customers_example;

CREATE TABLE customers_example (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE orders_example (
  id          integer PRIMARY KEY,
  customer_id integer,                      -- intentionally not a real FK so we can show unmatched rows
  total       numeric(10, 2) NOT NULL
);

INSERT INTO customers_example VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders_example   VALUES (1, 1, 50), (2, 1, 30), (3, 99, 10);  -- order 3 points at a non-existent customer

-- INNER JOIN: only rows that match on both sides
SELECT c.name, o.id AS order_id, o.total
FROM customers_example c
INNER JOIN orders_example o ON o.customer_id = c.id
ORDER BY o.id;

-- LEFT JOIN: every customer, even those without orders
SELECT c.name, o.id AS order_id, o.total
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
ORDER BY c.id, o.id;

-- Filtering on the JOIN vs in WHERE matters for LEFT JOIN.
-- "Customers and their orders > 40, even if they have none":
SELECT c.name, o.id, o.total
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id AND o.total > 40
ORDER BY c.id;

-- vs "customers who have orders > 40" — the WHERE turns the LEFT into an INNER:
SELECT c.name, o.id, o.total
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
WHERE o.total > 40
ORDER BY c.id;

ON 子句是连接条件。它可以是任意布尔表达式，不过通常写成 parent.id = child.parent_id。ON 中的条件属于连接本身——对于 LEFT JOIN，即使右侧没有满足 ON 的匹配行，左侧行也会保留下来。WHERE 中的条件会在连接之后应用，因此对 LEFT JOIN 使用 WHERE child.col = 'x' 会过滤掉未匹配的（NULL）行，实际上使连接表现为 INNER JOIN。

USING (col) 是在两侧列名相同时的简写：USING (customer_id) 等价于 ON a.customer_id = b.customer_id，而结果行中只会有一个 customer_id 列，不是两个。

第 3 个订单的 customer_id = 99（没有该 id 的客户）。INNER JOIN 会丢弃它；LEFT JOIN 会保留没有订单的客户（Bob、Carol）。两者结合起来才能看到完整情况。

运行示例

运行方式：

$ psql -f source/inner-and-left-join.sql postgres
...
 name  | order_id | total
-------+----------+-------
 Alice |        1 | 50.00
 Alice |        2 | 30.00
(2 rows)

 name  | order_id | total
-------+----------+-------
 Alice |        1 | 50.00
 Alice |        2 | 30.00
 Bob   |          |
 Carol |          |
(4 rows)
 ...

常见陷阱

使用 WHERE right_table.col = 'x' 过滤 LEFT JOIN，会意外丢掉你原本想保留的未匹配行。如果条件属于匹配条件，就把它放在 ON 中；如果想在连接后再筛选，就放在 WHERE 中。
在没有索引的列上连接时，规划器会改用哈希连接或归并连接，而这可能扫描整张表。请为外键列建立索引。
笛卡尔积：CROSS JOIN，或不带连接谓词的逗号分隔 FROM 列表，会产生 m × n 行。普通的 INNER JOIN 需要 ON 或 USING；PostgreSQL 不会对合法的交叉连接发出警告。
LEFT JOIN ... GROUP BY 是“统计每个父项的子项数量，同时包括没有子项的父项”的自然写法。使用 count(child.id)（而不是 count(*)），这样未匹配的行才会得到 0。

提示

选择简短且有意义的别名。customers_example c 和 orders_example o 比 customers_example cs1 和 orders_example o1 更易读。许多风格指南建议使用由表名派生的 1–3 个字符的别名。

动手练习

把 INNER JOIN 改成 LEFT JOIN，看看 Carol 如何带着 NULL 出现。试试上面的两种筛选写法，确认只有其中一种会保留未匹配的客户。然后使用 USING：SELECT * FROM customers_example c JOIN orders_example o ON o.customer_id = c.id; 会变成 SELECT * FROM customers_example c JOIN orders_example o USING (id);——等等，这里两张表的列名不同，所以你需要先重命名其中一列。换用两张共享同一个列名的表试试。

29 内连接与左连接

本章要点

示例

运行示例

常见陷阱

下载