PostgreSQL:在LEFT JOIN中使用AND语句无法按预期工作 [英] PostgreSQL: Using AND statement in LEFT JOIN is not working as expected

问题描述

此查询返回表 la 中的所有元素，以及来自 lar 的字段的所有空值该表不是我所期望的.

This query returns all the elements in the table la and all nulls for fields coming from the lar table which is not what I expected.

SELECT
  la.listing_id,
  la.id,
  lar.*
FROM la
LEFT JOIN lar
ON lar.application_id = la.id AND la.listing_id = 2780;

此查询返回正确的预期结果，但两个查询不应该做相同的事情吗?

This query returns correct and expected results but shouldn't both queries do the same thing ?

SELECT
  la.listing_id,
  la.id,
  lar.*
FROM la
LEFT JOIN lar
ON lar.application_id = la.id
WHERE la.listing_id = 2780;

我在这里想念什么?

我想进行条件连接，因为我已经注意到，对于复杂的查询，PostgreSQL会执行连接，然后执行WHERE子句，这实际上非常慢.如何在执行JOIN之前使数据库过滤掉一些记录?

I want to make conditional joins as I have noticed that for complex queries Postgresql does the join then do the WHERE clause which is actually very slow. How to make the database filter out some records before doing the JOIN ?

推荐答案

关于LEFT JOIN和WHERE子句的混淆已被澄清很多次:

The confusion around LEFT JOIN and WHERE clause has been clarified many times:

• SQL/PostgreSQL左连接忽略"on = constant"；谓词，在左表上

此 有趣的问题 仍然存在:

This interesting question remains:

如何在执行JOIN之前使数据库过滤掉一些记录?

How to make the database filter out some records before doing the JOIN?

Postgres中没有明确的查询提示. (这是一个正在进行的辩论.)但是，仍有各种技巧可以使Postgres改变方向.

There are no explicit query hints in Postgres. (Which is a matter of ongoing debate.) But there are still various tricks to make Postgres bend your way.

但是首先，问问自己:为什么查询计划者开始估计所选的计划会更便宜?您的服务器配置基本正常吗?费用设定足够吗? autovacuum正在运行? Postgres版本过时了吗?您是否正在解决应该真正解决的潜在问题?

But first, ask yourself: Why did the query planner estimate the chosen plan to be cheaper to begin with? Is your server configuration basically sane? Cost settings adequate? autovacuum running? Postgres version outdated? Are you working around an underlying problem that should really be fixed?

如果您强迫Postgres按照自己的方式进行操作，则应确保在版本升级或更新到服务器配置后，它不会触发……您最好确切地知道自己在做什么.

If you force Postgres to do it your way, you should be sure it won't fire back, after a version upgrade or update to the server configuration ... You'd better know what you are doing exactly.

也就是说，您可以 强制Postgres在添加OFFSET 0的子查询中执行JOIN"之前过滤掉一些记录，这只是从逻辑上讲，它是噪音，但可以防止Postgres将其重新排列为常规联接的形式. (毕竟是查询提示)

That said, you can force Postgres to "filter out some records before doing the JOIN" with a subquery where you add OFFSET 0 - which is just noise, logically, but prevents Postgres from rearranging it into the form of a regular join. (Query hint after all)

SELECT la.listing_id, la.id, lar.*
FROM  (
   SELECT listing_id, id
   FROM   la
   WHERE  listing_id = 2780
   OFFSET 0
   ) la
LEFT   JOIN lar  ON lar.application_id = la.id;

或者您可以使用CTE(晦涩难懂，但价格昂贵).或其他技巧，例如设置某些配置参数.或者，在这种情况下，我将使用LATERAL 连接来达到相同的效果:

Or you can use a CTE (less obscure, but more expensive). Or other tricks like setting certain config parameters. Or, in this particular case, I would use a LATERAL join to the same effect:

SELECT la.listing_id, la.id, lar.*
FROM   la
LEFT  JOIN LATERAL (
   SELECT *
   FROM   lar
   WHERE  application_id = la.id
   )  lar ON true
WHERE  la.listing_id = 2780;

相关:

• 示例查询以显示基数估计错误在PostgreSQL中

此处是2ndQuadrant撰写的有关查询提示的广泛博客.五岁了，但仍然有效.

Here is an extensive blog on Query hints by 2ndQuadrant. Five year old but still valid.

这篇关于PostgreSQL:在LEFT JOIN中使用AND语句无法按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！