Re: join condition against where with coalesce - Mailing list pgsql-general

Sim Zacks <sim@compulab.co.il> writes:
> Should there be any difference between:
> select * from table1 a left join table2 b on a.pk=b.fk and b.typeid=14
> and
> select * from table1 a left join table2 b on a.pk=b.fk
> where coalesce(b.typeid,14)=14

Quite a lot: every A row is guaranteed to appear in the output of the
first query, but not in the second.  Consider for instance an A row that
only joins to b row(s) having typeid 13.

            regards, tom lane