Menu

PostgreSQL 连接查询:INNER、LEFT、FULL、CROSS 与 LATERAL

连接按条件把两张表的行组合在一起。INNER JOIN 保留匹配的行,LEFT JOIN 保留第一张表的每一行,FULL JOIN 两边都保留,CROSS JOIN 两两配对,LATERAL 为每一行运行一次子查询。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

连接(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)张时,查询规划器会调整内连接的顺序,找出代价低的计划。外连接只有在某些情况下才能调整顺序,因为移动它们可能会改变结果。

Coddy 编程语言插图

和 Coddy 一起学编程

开始