I called this query on PostgreSQL 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 tried another values for the last part and it always returns the count of all rows!

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.

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;