Thread: join condition against where with coalesce

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

The reason I need to use the coalesce is because my goal is to do it with a full join and can't use
the and condition because it is not merge-joinable.

My test with the left join showed me that with the where it doesn't give any results, while I would
expect it to give me all the results in the first table.

Any thoughts?

I figured out my problem.
Table1 and Table2 have matches for every pk,fk just not on typeid=14,
therefore when I join on just the pk,fk and do a where looking for null, it
doesn't find any rows that qualify.

Doesn't help me solve my problem, but at least I know where I'm at.

Sim

Sim Zacks wrote:
> 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
>
> The reason I need to use the coalesce is because my goal is to do it
> with a full join and can't use
> the and condition because it is not merge-joinable.
>
> My test with the left join showed me that with the where it doesn't give
> any results, while I would expect it to give me all the results in the
> first table.
>
> Any thoughts?

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