Re: where with NULL values are not selected

* Dawid Kuroczko <qnex42(at)gmail(dot)com> wrote:

<snip>
> A hint:
> qnex=# SELECT 'ok' WHERE NULL=NULL;
> ?column?
> (0 rows)
>
> In other words -- NULL is not equal to NULL. NULL is not a value, NULL
> is a state. If you want to join those columns, you cannot use NULL
> as a joining key. It's the way SQL works.

Thats the statement of the day ;-)
In fact, postgres does not store empty fields, so you can save
space in long living and large tables (ie. journals) by setting
unneeded fields to NULL.

> Anyway, a quick-and-dirty solution might be:
>
> select table1.column from table1, table2 where
> coalesce(table1.column,-1)=coalesce(table2.column, -1);
>
> ...assuming column doesn't have '-1' value.

better: add the missing case (A and B are NULL) to the
where clause:

SELECT ... FROM table1, table2 WHERE
(table1.column=table2.column) OR
((table1.column IS NULL) AND
(table2.column IS NULL));

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
---------------------------------------------------------------------
Realtime Forex/Stock Exchange trading powered by postgresSQL :))
http://www.fxignal.net/
---------------------------------------------------------------------