PostgreSQL:外连接中的 ON 与 WHERE - - 何时移动条件会改变结果

背景:PostgreSQL 如何在外连接中处理 ON 和 WHERE

PostgreSQL 将查询的表表达式构建为一条流水线:FROM 子句生成一个中间虚拟表,随后 WHERE、GROUP BY 和 HAVING 对其进行变换。在 FROM 内部,限定连接的条件位于 ON(或 USING)子句中,它决定来自两个数据源的哪些行被视为匹配。对于 LEFT OUTER JOIN,PostgreSQL 首先执行内连接,然后为每一个没有匹配到任何行的左侧行添加一行,右侧列以空值填充。这意味着 ON 子句做两件事:它剔除不匹配的组合,并为未匹配的左侧行添加空值扩展行。

WHERE 没有这样的角色。它在 FROM 子句生成连接后的表之后运行,只是剔除不满足其搜索条件的行。文档明确说明了这一顺序:ON 子句中的限制条件在连接之前处理,而 WHERE 子句中的限制条件在连接之后处理。对于内连接,这一顺序在结果中不可见,但对于外连接则不然,因为作用于右侧列的 WHERE 谓词可能恰好拒绝掉外连接本应保留的那些空值扩展行。

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';; num | name | num | value;-----+------+-----+-------; 1 | a | 1 | xxx;(1 row)

建议:条件放在哪里,以及其中的权衡

一条实用规则:如果条件限制的是外连接的可空(右)侧,而你仍然希望保留来自被保留侧的未匹配行,就把它放在 ON 中。如果条件旨在过滤最终结果,包括丢弃空值扩展行,就把它放在 WHERE 中。作用于 LEFT JOIN 被保留(左)侧的条件,就哪些左侧行得以保留而言,放在两处效果相同,但放在 ON 中可以让连接保持自包含,并在日后更改连接类型时避免意外。对于内连接,文档指出连接条件可以写在 WHERE 或 JOIN 子句中,选择主要是风格问题;应由可读性和团队约定来决定。注意,外连接本身必须写在 FROM 子句中,不存在仅用 WHERE 的写法。

其权衡在于语义清晰与简洁之间。把非连接谓词混入 ON 可能使查询更难阅读,但把这样的谓词移到 WHERE 会悄悄地把一个

保留未匹配行

的查询变成类似内连接的结果。在审查或重构时,把任何跨越外连接 ON/WHERE 边界的谓词移动视为语义变更,而非表面调整。

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';; num | name | num | value;-----+------+-----+-------; 1 | a | 1 | xxx; 2 | b | |; 3 | c | |;(3 rows)

具体示例:LEFT JOIN 中作用于 t2.value 的条件

文档示例使用两张表,其中 t1 有行 (1,a)、(2,b)、(3,c),t2 有 (1,xxx) 和 (3,yyy)。当限制条件位于 ON 中时,连接只匹配 value 为 'xxx' 的 t2 行;t1 的第 2 行和第 3 行找不到符合条件的匹配,其 t2 列以 NULL 返回,共得到三行。当同样的限制条件位于 WHERE 中时,连接首先仅按 num 匹配,产生 (1,xxx) 和 (3,yyy) 两个配对的行,然后 WHERE 丢弃所有 t2.value 不是 'xxx' 的行 - - 它同样丢弃空值扩展行,因为 NULL = 'xxx' 不为真。只有一行得以保留。下面的示例输出遵循文档的示例;请用你自己的种子数据运行以确认。

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx'; -- returns 3 rows: (1,a,1,xxx), (2,b,NULL,NULL), (3,c,NULL,NULL) SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx'; -- returns 1 row: (1,a,1,xxx)

适用范围的限制

这一区别只对 LEFT、RIGHT 和 FULL 外连接重要,因为只有它们的 ON 子句既删除行又添加行。对于 INNER JOIN,ON 和 WHERE 等价,放置位置只是风格问题。这里的建议只涉及结果语义;它不涉及规划器会选择哪个计划或索引,性能问题仍需通过测量实际执行计划来评估。示例反映 PostgreSQL 18 文档;JOIN 语法是标准 SQL,但在依赖其他数据库系统的行为之前请先验证。还要记住,在 FROM 列表中 JOIN 的结合优先级高于逗号,因此逗号与 JOIN 混用的列表可能改变 ON 条件可以引用哪些表。

FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition -- the condition can reference T1 here,;-- but not in the comma-list equivalent;FROM T1, T2 INNER JOIN T3 ON condition