SQL/PostgreSQL左连接忽略"on = constant"；谓词，在左表 [英] SQL / PostgreSQL left join ignores "on = constant" predicate, on left table

问题描述

SQL 左连接查询将忽略 left 表上的ON column = constant谓词.
但是，它关心 right 表上的另一个ON column = constant谓词.

An SQL left join query ignores an ON column = constant predicate on the left table.
However, it cares about another ON column = constant predicate on the right table.

如果我将左表的ON column = constant移到WHERE子句，则查询将按预期工作.

If I move the left table's ON column = constant to the WHERE clause instead, the query works as intended.

对于左表，如果将column = constant放在查询的WHERE部分中，还是放在查询的JOIN ... ON部分中，为什么如此?

Why does it matter, for the left table, if I place the column = constant in the WHERE part of the query, or in the JOIN ... ON part of the query?

(发生的情况是，左表ON column = constant条件被上推到"JOIN Filter"步骤，在这里似乎被忽略了.)

(What happens, is that the left table ON column = constant condition gets pushed up to a "JOIN Filter" step, where it is seemingly being ignored.)

详细信息:

EXPLAIN ANALYZE
select * from DW1_PAGE_PATHS t left join DW1_PAGES g
   on t.TENANT = g.TENANT
  and t.PAGE_ID = g.GUID
  and g.GUID = 'abcdefg'  -- works
  and t.CANONICAL = 'C'  -- "ignored", unless moved to `where` clause
where t.TENANT = '72'
  and PARENT_FOLDER like '/%';

这里(下方)是执行计划.请注意，t.CANONICAL = 'C'已被向上推至"JOIN筛选器"步骤，而g.GUID = 'abcdefg'筛选器直接在扫描右表时发生.

Here (below) is the exec plan. Note that t.CANONICAL = 'C' has been pushed up to the "JOIN Filter" step, whereas the g.GUID = 'abcdefg' filter happens directly when the right table is being scanned.

 Nested Loop Left Join  (cost=... actual time=...)
   Join Filter: (((t.canonical)::text = 'C'::text)
             AND ((t.tenant)::text = (g.tenant)::text)
             AND ((t.page_id)::text = (g.guid)::text))
   ->  Seq Scan on dw1_page_paths t
         Filter: (((parent_folder)::text ~~ '/%'::text)
              AND ((tenant)::text = '72'::text))
   ->  Seq Scan on dw1_pages g
         Filter: (((tenant)::text = '72'::text)
              AND ((guid)::text = 'abcdefg'::text))

(另一个问题:为什么 t.canonical ='C'的联合过滤器"为什么不能过滤出 canonical 不是'C'的行?不会.)

(Another question: Why won't the "Join Filter" with t.canonical = 'C' filter out rows for which canonical is not 'C'? It does not.)

(PostgreSQL版本psql (9.1.6, server 9.1.1).)

(PostgreSQL version psql (9.1.6, server 9.1.1).)

这里是指向类似查询的链接，但是答案不能解释为什么，如果将左表ON column = constant移到where子句中，它将起作用: 在使用LEFT OUTER JOIN时添加条件

Here is a link to a similar query, but the answers don't explain why it works if you move the left table ON column = constant to a where clause instead: Add condition while using LEFT OUTER JOIN

推荐答案

要点是，LEFT [OUTER] JOIN的ON子句仅调节是否连接了右表中的行.

The point is that the ON clause for a LEFT [OUTER] JOIN only regulates whether a row from the right table is joined.

它不过滤左侧表格中的行.如果要执行此操作，则表达式必须进入WHERE子句(您已经发现)或[INNER] JOIN的ON子句.
都是设计使然.

It does not filter rows from the left table. If you want to do that, the expression has to go into a WHERE clause (as you found out already) or the ON clause of a [INNER] JOIN.
That's all by design.

这篇关于SQL/PostgreSQL左连接忽略"on = constant"；谓词，在左表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！