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:
Show quoted text
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)=14The 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