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

Apologies - just reread my post and I've confused matters with typos during
the abtraction of my code. For the purposes of the example given, please
read "get_colnames_for_id()" rather than "get_cathcode()"...

Cheers,

Ian

On Wed, Apr 2, 2008 at 5:49 PM, Ian Sillitoe <ian(dot)sillitoe(at)googlemail(dot)com>
wrote:

> This is probably a stupid question that has a very quick answer, however
> it would be great if someone could put me out of my misery...
>
> 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
>
>
> -- for a given id, return a bunch of columns that I can use for joins
>
> psql> select * from get_colnames_for_id('1.10.8');
>
> depth1 | depth2 | depth3 | depth4 | depth5 |
> --------+--------+--------+--------+--------+
> 1 | 10 | 8 | | |
> (1 row)
>
> --
> psql> select * from table where depth1 = 1 AND depth2 = 10 AND depth3 = 8
> AND depth4 IS NULL;
>
> id | depth1 | depth2 | depth3 | depth4 | depth5 | name |
> ---+--------+--------+--------+--------+--------+----------------------+
> 1 | 1 | 10 | 8 | | | name for node 1.10.8 |
>
> (1 row)
>
> -- I (wrongly) expected the following to be equivalent
>
> psql> select c.* from get_cathcode('1.10.8') c JOIN table t USING(depth1,
> depth2, depth3, depth4);
>
> id | depth1 | depth2 | depth3 | depth4 | depth5 | name |
> ---+--------+--------+--------+--------+--------+-----------+
> (0 rows)
>
> -- Whereas the following works...
>
> psql> select * from get_colnames_for_id('1.10.8.10');
>
> depth1 | depth2 | depth3 | depth4 | depth5 |
> --------+--------+--------+--------+--------+
> 1 | 10 | 8 | 10 | |
> (1 row)
>
> psql> select c.* from get_cathcode('1.10.8.10') c JOIN table t
> USING(depth1, depth2, depth3, depth4);
>
> id | depth1 | depth2 | depth3 | depth4 | depth5 | name
> |
>
> ---+--------+--------+--------+--------+--------+-------------------------+
> 2 | 1 | 10 | 8 | 10 | | name for node 1.10.8.10|
> (0 rows)
>
>
> So, I'm currently assuming from all this that joining on t1.col = t2.col
> doesn't make any sense when t1.col and t2.col are both NULL - since:
>
> psql> SELECT (NULL = NULL) IS TRUE;
> ?column?
> ----------
> f
> (1 row)
>
> psql> SELECT (NULL IS NULL) IS TRUE;
> ?column?
> ----------
> t
> (1 row)
>
> Unless I've missed something, the docs on
> http://www.postgresql.org/docs/8.1/static/functions-comparison.html seem
> to suggest that the concept is an example of bad programming and the
> workaround (of switching on the 'transform_null_equals' config) is a hack.
> Is this all true or did my logic just get screwed up at some point? Unless
> I've just missed something obvious, it seems useful to be able to join two
> tables based on a condition where they share a NULL column - is there
> another way of doing this?
>
> Many thanks,
>
> Ian
>
>
>