Re: (FAQ?) JOIN condition - 'WHERE NULL = NULL'

>>> "Ian Sillitoe" <ian(dot)sillitoe(at)googlemail(dot)com> 03/04/08 5:49 AM >>>

I'm trying to JOIN two tables (well a table and a resultset from a PL/pgsql
function) where a joining column can be NULL

In a join, no value can be ascribed to a null field, so the equivalence fails. You can do tests like IS NULL, which strictly speaking is test for meeting a condition (that of not having any value), not a test for equivalence. As (simplistically) the condition NULL does equal the condition NULL, (NULL = NULL) is true.

The simplest approach is perhaps to have a value which does not occur naturally (like -1), as a substitute for nulls in the relevant columns. I believe this can be achieved via a view in your case, (pun intended :-), but which may be less efficient if performance is an issue:

create view depth_v as
select d.id,
d.name,
case when (d.depth1 is null) then -1 else d.depth1 end as depth1,
case when (d.depth2 is null) then -1 else d.depth2 end as depth2,
case when (d.depth3 is null) then -1 else d.depth3 end as depth3,
case when (d.depth4 is null) then -1 else d.depth4 end as depth4,
case when (d.depth5 is null) then -1 else d.depth5 end as depth5
from depth_table d;

You could then join against this view instead of your underlying table, eg:

select c.* from get_cathcode('1.10.8') c JOIN depth_v t USING(depth1, depth2, depth3, depth4);

The view will not have any NULL values in the depth fields, so the join should work.

see: http://www.postgresql.org/docs/8.2/static/functions-conditional.html

(Incidentally, if you are storing bathymetry or CTD data, I'd be interested in seeing your db structures, as I may be doing some work in that area soon :-)

HTH,

Brent Wood