Re: where with NULL values are not selected

* Dawid Kuroczko <qnex42@gmail.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@metux.de
---------------------------------------------------------------------
  Realtime Forex/Stock Exchange trading powered by postgresSQL :))
                                            http://www.fxignal.net/
---------------------------------------------------------------------