Can't join on null values

David Newall wrote:

PostgreSQL version: 7.4.3 (RPMs from ftp.au.postgresql.org)

Operating Sysem: Fedora Core 1

CREATE TABLE t1 (i INTEGER, j INTEGER);
INSERT INTO t1 VALUES (1, NULL);
CREATE TABLE t2 AS SELECT * FROM t1;
SELECT * FROM t1 JOIN t2 USING (i, j);
i | j
---+---
(0 rows)

I believe the one row, which is identically present in both table,
should be selected. The problem occurs because of the NULL value.

A join happens when two values are equal in the sense of the operator =.
But "NULL = NULL" is not true, so the behavior is correct.

Hello,

its not error. Only SQL specific :-) NULL <> NULL

Regards
Pavel Stehule

On Tue, 6 Jul 2004, David Newall wrote:

On Tue, 6 Jul 2004, David Newall wrote:

PostgreSQL version: 7.4.3 (RPMs from ftp.au.postgresql.org)

Operating Sysem: Fedora Core 1

CREATE TABLE t1 (i INTEGER, j INTEGER);
INSERT INTO t1 VALUES (1, NULL);
CREATE TABLE t2 AS SELECT * FROM t1;
SELECT * FROM t1 JOIN t2 USING (i, j);
i | j
---+---
(0 rows)

I believe the one row, which is identically present in both table,
should be selected. The problem occurs because of the NULL value.

NULL is not equal to NULL so I don't think the values for j meet
the join condition "for which the corresponding join columns have equal
values."

Pavel Stehule wrote:

its not error. Only SQL specific :-) NULL <> NULL

Au contraire, neither

NULL = NULL

nor

NULL <> NULL

is true.

People,

Thanks for your help with my problem with NULL values. Also, particular
thanks for a hint on where to find a copy of SQL-92 standard, something
I didn't already have. It was annoying to discover that UNIQUE didn't
have what I felt was the "obvious" meaning, but it doesn't and PostgreSQL
does operate correctly.

David