Sql/Postgresql Left Join Ignores "On = Constant" Predicate, on Left Table

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

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

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.

Using LEFT JOIN instead of NOT IN in PostgreSQL

The correctly rewritten query needs the WHERE conditions of the former subquery as join conditions to the LEFT JOIN like:

SELECT table_a.id
FROM   table_a
LEFT   JOIN  reversion_version U0 ON U0.object_id = table_a.id::text
                                 AND U0.content_type_id = 49
                                 AND U0.db = 'default'
WHERE  U0.object_id IS NULL;

The way you tried was a logical contradiction: it would ask for rows in table_a with no matching row in reversion_version and then impose additional conditions on the non-existent rows. That can never return any rows.

It must be the other way round: find rows in table_a with no matching row in reversion_version that would fulfill said conditions. Hence move those conditions from the WHERE clause to the join clause of the LEFT JOIN. Subtle, but fundamental difference.

See:

• SQL / PostgreSQL left join ignores "on = constant" predicate, on left table
• Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
• Select rows which are not present in other table

There might be more to be said about performance, but not without the necessary details of your setup ...

Postgresql query many to many relationship with left join and use where clouse not show data

Including a where clause on an outer joined table, effectively converts the join into an inner join. If there are no matching values in table_pivot, then
you will get no results at all. This is standard sql.

Select rows in left join which depend on sum of a field in the other table?

You can filter with a correlated subquery:

select l.*
from left_table l
where l.amount = (select sum(r.amount) from right_table r where r.id = l.id)

Double Left Joins Ignoring Filter

You need to move condition to ON clasue:

SELECT COUNT(customer.cust_id) as visit_count, locations.location_name 
FROM locations 
LEFT JOIN VISITS ON locations.location_name = visits.location_checkin 
LEFT JOIN customer ON visits.cust_id = customer.cust_id 
      AND customer.adm = false 
      AND customer.super_adm = false
WHERE locations.group_id = 1 
GROUP BY locations.id, locations.location_name -- here added location_name to match SELECT

If you use WHERE on outer table column reference it will work as normal INNER JOIN

Postgresql should left join use WHERE or ON is enough?

I'm not sure if your example it too simple, but you shouldn't need a subquery at all for this one - and definitely not the group by.

Suppose you do need a subquery, then for this specific example, it leads to exactly the same query plan whether you add the where clause or not. The idea of the query planner is that it tries to find a way to make your query as fast as possible. Oftentimes this means ordering the execution of joins and where clauses in such a way, that the result set is increased sooner rather than later. I generated exactly the same query, only with reservations and customers, I hope that's okay.

EXPLAIN
SELECT *
FROM reservations
  LEFT OUTER JOIN (
    SELECT *
    FROM customers
  ) AS customers ON customers.id = reservations.customer_id
WHERE customer_id = 1;

Nested Loop Left Join  (cost=0.17..183.46 rows=92 width=483)
  Join Filter: (customers.id = reservations.customer_id)
  ->  Index Scan using index_reservations_on_customer_id on reservations  (cost=0.09..179.01 rows=92 width=255)
        Index Cond: (customer_id = 1)
  ->  Materialize  (cost=0.08..4.09 rows=1 width=228)
        ->  Index Scan using customers_pkey on customers  (cost=0.08..4.09 rows=1 width=228)
              Index Cond: (id = 1)

The deepest arrows are executed first. This means that even though I didn't have the equivalent of where add.id = 1 in my subquery, it still knew that the equality customers.id = customer_id = 1 should be true, so it decided to filter on customers.id = 1 before even attempting to join anything

---

Related Topics