问题描述
在PostgreSQL文档中，当解释 EXPLAIN 命令的基础时：
From PostgreSQL document, when explaining basics of EXPLAIN command:
联接过滤器条件来自外部加入的ON子句。然后在外部联接中，纯过滤条件从何而来？
"Join Filter conditions come from the outer join's ON clause". Then in outer join, where does a plain filter condition come from?
能否举一些例子？
谢谢。
推荐答案
以下是一个可能启发您的小例子：
Here is a little example that might enlighten you:
CREATE TABLE a(a_id) AS VALUES (1), (3), (4);
CREATE TABLE b(b_id) AS VALUES (1), (2), (5);
现在我们必须强制执行嵌套循环联接：
Now we have to force a nested loop join:
SET enable_hashjoin = off;
SET enable_mergejoin = off;
我们的查询是：
SELECT *
FROM a
LEFT JOIN b ON a_id = b_id
WHERE a_id > coalesce(b_id, 0);
a_id | b_id
------+------
3 |
4 |
(2 rows)
计划为：
QUERY PLAN
------------------------------------------
Nested Loop Left Join
Join Filter: (a.a_id = b.b_id)
Filter: (a.a_id > COALESCE(b.b_id, 0))
-> Seq Scan on a
-> Materialize
-> Seq Scan on b
上进行 Seq扫描是在连接后 应用的条件。
The “plain filter” is a condition that is applied after the join.
经常会错误地认为 WHERE中的条件子句与 JOIN… ON 子句中的条件相同。内连接只有这种情况。对于外部联接，结果中还包括不符合条件的外侧行。
It is a frequent mistake to believe that conditions in the WHERE clause are the same as conditions in a JOIN … ON clause. That is only the case for inner joins. For outer joins, rows from the outer side that don't meet the condition are also included in the result.
这使得必须具有两个不同的过滤器。
That makes it necessary to have two different filters.
这篇关于在外部联接中，普通过滤条件从何而来？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！