Re: How to join when NULL is the common value ?

From: <roguedood_at_my-deja.com>
Date: Sat, 21 Oct 2000 14:53:27 GMT
Message-ID: <8ssal5$7i6$1@nnrp1.deja.com>

At risk of being kicked out of the "Pure Relational Theory" club...

The prior posts are right, it "shouldn't" really mean anything to join on NULL values. NULL is supposed to represent undefined data. That being said, sometimes you end up in a situation where you would like to join on two columns, and INCLUDE those records where one row is NULL and the row being compared to is NULL...

---

scott_at_SLAP.US.ORACLE.COM> create table foo(a number, b number); Table created.

scott_at_SLAP.US.ORACLE.COM> insert into foo values (1, 1); 1 row created.

scott_at_SLAP.US.ORACLE.COM> insert into foo values (2, null); 1 row created.

. . .

scott_at_SLAP.US.ORACLE.COM> create table foo3 as   2 select * from foo;

Table created.

sdillon_at_SLAP.US.ORACLE.COM> select a,nvl(b,-1)   2 from foo;

         A NVL(B,-1)
---------- ----------

         1          1
         2         -1
         3         -1
         4          1

4 rows selected.

sdillon_at_SLAP.US.ORACLE.COM> select a,nvl(b,-1)   2 from foo2;

         A NVL(B,-1)
---------- ----------

         1          1
         2         -1
         3         -1
         4          1

4 rows selected.

sdillon_at_SLAP.US.ORACLE.COM> select f.a,
  2 nvl(f2.b,-1) b
  3 from foo f, foo2 f2
  4 where f.a = f2.a
  5 and f.b = f2.b
  6 /

     A B
------ --------

     1        1
     4        1


sdillon_at_SLAP.US.ORACLE.COM> select f.a,
  2         nvl(f2.b,-1) b

  3 from foo f, foo2 f2
  4 where f.a = f2.a

  5     and (f.b = f2.b
  6      OR (f.b is null
  7*     AND f2.b is null))

scott_at_SLAP.US.ORACLE.COM> /

     A B
------ --------

     1        1
     2       -1
     3       -1
     4        1
================================

Hope that helps...

_smd_

In article <8sqcns$rjt$1_at_nnrp1.deja.com>,   dili66_at_my-deja.com wrote:
> Hi, At a first glance it appears that when someone
> would like to join two tables over some column then
> a row with a NULL value in one of the columns will
> not be joined with a row with a NULL value in
> the other table. Do you know if there is a switch
> to allow this type of join ?
>
> Thank you.
>
> D
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 21 2000 - 09:53:27 CDT