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@xxxxxxxxxxx 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? > > -- > //www.freelists.org/webpage/oracle-l > -- Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- //www.freelists.org/webpage/oracle-l