When join condition in the SQL query contains `file.id = -1` it returns all rows. Why?

Question

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!

It shouldn't, unless all f.id is -1. You are requesting all from f where id = -1. It doesn't matter matching user_id exists or not. And if u.id is not unique it might even return more rows. — Cetin Basoz, Sep 22 '21 at 12:10
see [https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram](https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram) — Stu, Sep 22 '21 at 12:13
Oh yea. It's because `left join` returns the row even if there is no matches! — rostamiani, Sep 22 '21 at 12:20

score 2 · Accepted Answer · answered Sep 22 '21 at 14:48

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.

score 1 · Answer 2 · edited Sep 22 '21 at 14:29

1

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;

edited Sep 22 '21 at 14:29

ZygD

answered Sep 22 '21 at 12:20

Zahidli

2 Answers2