When join condition contains `file.id = -1` it returns all rows. Why? [closed]-CodePudding

I call this query accidentally on Postgres and it returned the count of all table rows!

But why? The ON part is always false then the result should be empty!

select
  count(*)
from
  skyroom_files f
  left join users u on f.user_id = u.id
  and f.id = -1;

I tries another values for the last part and it always returns the count of all rows!

CodePudding user response：

Left join selects all rows in left table and return left table count. Use inner join

select
  count(*)
from
  skyroom_files f
  inner join users u on f.user_id = u.id
  and f.id = -1;

CodePudding user response：

The result of a left join is defined as

the result of the inner join
in addition, all rows from the left relation that do not appear in the above result, supplemented with NULL values for the columns of the right relation

In your case, the inner join is empty, so you get all rows from the left table, supplemented with NULL values.

With outer joins, it makes a difference if you use a condition as join condition or as WHERE condition.