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

>>> "Ian Sillitoe" <ian.sillitoe@googlemail.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
strictlyspeaking 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
therelevant columns. I believe this can be achieved via a view in your case, (pun intended :-), but which may be less
efficientif 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
doingsome work in that area soon :-) 


HTH,

  Brent Wood