Postgres Pro
Downloads
Home   >   mailing lists

Thread: Can't join on null values

Can't join on null values

From
David Newall
Date:
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.

Re: Can't join on null values

From
Peter Eisentraut
Date:
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.

Re: Can't join on null values

From
Pavel Stehule
Date:
Hello,

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

Regards
Pavel Stehule

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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

Re: Can't join on null values

From
Stephan Szabo
Date:
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."

Re: Can't join on null values

From
Peter Eisentraut
Date:
Pavel Stehule wrote:
> its not error. Only SQL specific :-) NULL <> NULL

Au contraire, neither

NULL = NULL

nor

NULL <> NULL

is true.

Re: Can't join on null values

From
davidn-postgres@rebel.net.au
Date:
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