On Tuesday, January 7, 2025, Torbjörn Gannholm <
torbjorn.gannholm@gmail.com> wrote:
select * from bar LEFT JOIN foo ON foo.a = bar.a WHERE foo.a IS NULL;
select * from bar LEFT JOIN foo ON foo.a = bar.a AND foo.a IS NULL;
The where clause is evaluated after the join and thus the join sees all rows from foo and bar.
When you place the expression in the ON clause it gets evaluated before/during the join, and thus effectively pushed down and the rows of foo are filtered before the join happens.
Separately, I would advise using NOT EXISTS for an anti-join and not mention foo in the from clause at all. That is the canonical form.
David J.
