SQL : FULL OUTER JOIN on null columns

Question

I want to use a FULL OUTER JOIN between two tables on several columns, but when both columns are null, they are not considered as equal during the join, so I obtain two different rows. How can I write my join, so null columns are considered as equal ?

I have set up a simplified example :

create table t1 (
 id number(10) NOT NULL,
 field1 varchar2(50),
 field2 varchar2(50),
 CONSTRAINT t1_pk PRIMARY KEY (id)
);

create table t2 (
  id number(10) NOT NULL,
  field1 varchar2(50),
  field2 varchar2(50),
  extra_field number(1),
  CONSTRAINT t2_pk PRIMARY KEY (id)
);

insert into t1 values(1, 'test', 'test2');
insert into t2 values(1, 'test', 'test2', null);

insert into t1 values(2, 'test1', 'test1');
insert into t2 values(2, 'test1', 'test1', null);

insert into t1 values(3, 'test0', null);
insert into t2 values(3, 'test0', null, 1);

insert into t2 values(4, 'test4', 'test0', 1);

select *
from t1
full outer join t2 using (id, field1, field2);

Result obtained :

Result expected :

SQLFiddle

Answer 1

Use NVL() and a Unique String to substitute NULL:

select t1.id,t1.field1,t1.field2,t2.extra_field
from t1
full outer join t2 ON
t1.id=t2.id 
AND NVL(t1.field1,'UID_INSTEAD_OF_NULL')=NVL(t2.field1,'UID_INSTEAD_OF_NULL')
AND NVL(t1.field2,'UID_INSTEAD_OF_NULL')=NVL(t2.field2,'UID_INSTEAD_OF_NULL')

SQLFiddle demo

Answer 2

NVL can be applied on the result so no function is needed in the join condition

select
    nvl(t1.id, t2.id) id,
    nvl(t1.field1, t2.field1) field1,
    nvl(t1.field2, t2.field2) field2,
    extra_field
from t1
full outer join t2 on t1.id = t2.id AND t1.field1 = t2.field1 AND (t1.field2 = t2.field2 OR (t1.field2 IS NULL AND t2.field2 IS NULL));