PostgreSQL: Using AND statement in LEFT JOIN is not working as expected

Question

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;

What am I missing here?

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 ?

You are asking two different things. The querys are different and wont do the same. Regarding the speed probably you need index. Try `EXPLAIN ANALYZE ` — Juan Carlos Oropeza, Nov 24 '16 at 19:33
You are right should be the same. I was thinking if you filter on the second table `lar`. But filter on first table should be the same. Let me do some testing. — Juan Carlos Oropeza, Nov 24 '16 at 19:53
They're not the same at all. 1st query returns everything from la; 2nd query returns only rows from la where listing_id = 2780 — Gareth Lyons, Nov 24 '16 at 20:07
Please add a minimal, verifyable set of table definitions + test data to your yestion (I cannot read the graphs) — wildplasser, Nov 24 '16 at 20:08
I will join you on the crazy house, this doesnt make sense http://rextester.com/JIIJ61448 — Juan Carlos Oropeza, Nov 24 '16 at 20:08
This is either an artifact or a bug. But if it is a bug, it would have been found earlier, IMHO. The Pg version? — wildplasser, Nov 24 '16 at 20:12
http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Nov 24 '16 at 20:13
They are different because the second one essentially turns the outer join into an inner join as the comparison on a null value of the outer table will effectively remove all those rows where the outer join did not return a match for the outer table (as all columns will be null) — , Nov 24 '16 at 20:20

score 2 · Answer 1 · edited May 23 '17 at 11:59

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

SQL / PostgreSQL left join ignores "on = constant" predicate, on left table

This interesting question remains:

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

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.

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?

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.

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;

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;

Sample Query to show Cardinality estimation error in PostgreSQL

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

Juan Carlos Oropeza · Answer 2 · 2016-11-24T20:26:28.230

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

So no matter you try to filter with AND la.listing_id = 2780; you still get all the rows from first table. But only those with la.listing_id = 2780; will have something <> NULL on the right side

The behaviour is different if you try INNER JOIN in that case only the matching columns are created and the AND condition will filter the rows.

So to make the first query work you need add WHERE la.listing_id IS NOT NULL

The problem with second query is will try to JOIN every row and then will filter only the one you need.

PostgreSQL: Using AND statement in LEFT JOIN is not working as expected

2 Answers2

Linked