Re: joining on columns with null value

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 28 Feb 2005 10:15:11 -0700
Message-ID: <4223519F.2020408@centrexcc.com>

Works for me:

create table t1 (a number, b number, c varchar2(10));

insert into t1 values (1,1,'A');
insert into t1 values (1,null,'A');
insert into t1 values (1,2,'A');
insert into t1 values (2,1,'B');
insert into t1 values (2,null,'B');
insert into t1 values (2,2,'B');

commit;
create table t2 as select * from t1;
select t1.*, t2.* from t1, t2
where t1.a = t2.a
and t1.b = t2.b;

A B C A B C
--- --- -- --- --- --

   1 1 A 1 1 A
   1 2 A 1 2 A
   2 1 B 2 1 B
   2 2 B 2 2 B

select t1.*, t2.* from t1, t2
where t1.a = t2.a
and nvl(t1.b,0) = nvl(t2.b,0);

A B C A B C
--- --- -- --- --- --

   1 A 1 A
   1 1 A 1 1 A
   1 2 A 1 2 A
   2 B 2 B
   2 1 B 2 1 B
   2 2 B 2 2 B

Of course, you want to use a value in the nvl which is guaranteed NOT to occur as a real non-null value.

ryan_gaffuri_at_comcast.net wrote:
> I have to join two tables. all of the columns can be null. However, if there are nulls and the rest of the fields are the same, I want to join to succeed.I did a test case where i assumed that if i used the following, I would get a record returned even if there are nulls.
> where nvl(tableA.column,0) = nvl(tableB.column,0)
>
> If both of these fields are null and i have the rest of the columns equivalent, this will not give me a record returned.
>
> However, if i do:
>
> where tableA.column1||tableA.column2||tableA.column3 = tableB.column1||tableB.column2||tableB.column3
>
> then i get a successful join if one or more of the fields is null but atleast 1 is not.
> this is pretty ugly. Any better way to do this?
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 28 2005 - 12:18:39 CST