连接(join)按条件匹配两张表的行,组合成新的行:SELECT ... FROM a JOIN b ON a.id = b.a_id。JOIN(INNER JOIN)只保留匹配的行,LEFT JOIN 保留第一张表的每一行,FULL JOIN 保留两张表的每一行。
返回三行。Grace 没有订单,订单 13 属于一位不存在的客户,所以两者都被排除了。下面的连接都使用这两张表。
连接类型
| 连接 | 返回的行 |
|---|---|
INNER JOIN | 满足条件的配对 |
LEFT JOIN | 左表的每一行;没有匹配时右表的列为 NULL |
RIGHT JOIN | 右表的每一行;和 LEFT JOIN 正好相反 |
FULL JOIN | 两边的每一行,空缺处用 NULL 填充 |
CROSS JOIN | 左表每一行和右表每一行配对 |
LEFT JOIN:保留没有匹配的行
Grace 出现在结果中,订单数为 0。要数 o.id,而不是 *:count(*) 会把 Grace 那一行 NULL 算成 1。
- 没有匹配的行(反连接):加上
WHERE o.id IS NULL,只保留没有订单的客户。WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)效果相同,而且意图更清楚。 - ON 和 WHERE:针对右表的条件应该写在
ON中。LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 20会把 Grace 去掉,因为她的 total 是NULL,通不过WHERE。这个连接就变成了内连接。写成ON o.customer_id = c.id AND o.total > 20,才能保留所有客户。
FULL JOIN:两边缺失的行
FULL JOIN 能找出两个方向上的不匹配,比较两份列表时很有用:
结果中有没有订单的 Grace,以及没有客户的订单 13。如果 orders.customer_id 上有外键,第二行就不会出现。
CROSS JOIN:所有组合
CROSS JOIN 没有条件。它把一边的每一行和另一边的每一行配对,所以 3 种尺码和 2 种颜色得到 6 行:
这里的两个列表是像表一样命名的 VALUES 列表。如果是真正的表,就写成 FROM sizes CROSS JOIN colors,FROM sizes, colors 的效果也一样。ON 条件永远为真的连接也会这样让行数成倍增加,这是查询突然返回过多行的常见原因。
LATERAL:每一行一个子查询
FROM 中的子查询通常看不到同一个 FROM 中的其他表。加上 LATERAL 就能看到,而且它会对前面那张表的每一行执行一次。这正好解决“每组取前 N 条”的问题:
Ada 得到她金额最大的两个订单,Linus 得到他唯一的一个,而因为用了 LEFT JOIN ... ON true,Grace 得到一行 NULL。CROSS JOIN LATERAL 则会去掉没有订单的客户。如果 orders (customer_id, total) 上有索引,每个子查询只需读取几个索引条目。如果每组只取一行,DISTINCT ON 往往写起来更短。
常见问题
PostgreSQL 中 JOIN 和 INNER JOIN 一样吗?
一样。单独写 JOIN 就是 INNER JOIN,LEFT JOIN 就是 LEFT OUTER JOIN;INNER 和 OUTER 关键字可以省略。
为什么 LEFT JOIN 返回的行比左表还多?
左表的一行会和右表每个匹配的行各出现一次。一位有三个订单的客户会出现三次。用 GROUP BY 聚合,或者连接一个每个键只有一行的子查询。
ON 和 USING 有什么区别?
ON 可以写任何条件。USING (id) 是两张表中同名列相等的简写,而且输出中只有一个 id 列,而不是两个。
在 PostgreSQL 中,连接的顺序会影响性能吗?
通常不会。当查询中的表不超过 join_collapse_limit(默认 8)张时,查询规划器会调整内连接的顺序,找出代价低的计划。外连接只有在某些情况下才能调整顺序,因为移动它们可能会改变结果。