Re: Incorrect result in certain expressions of anti-join

On Tuesday, January 7, 2025, Torbjörn Gannholm <torbjorn(dot)gannholm(at)gmail(dot)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.