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

Why not just join on the ID columns since they are both primary keys for their respective tables? — Sentinel, Sep 15 '15 at 14:30

score 3 · Accepted Answer · answered Sep 15 '15 at 13:28

3

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

answered Sep 15 '15 at 13:28

valex

23,966
7
43
60

1

This solution loses any benefit of indexes on field1 and/or field2 unless function based indexes are defined – Sentinel Sep 15 '15 at 14:31

score 3 · Answer 2 · answered Sep 15 '15 at 13:45

3

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));

answered Sep 15 '15 at 13:45

Husqvik

5,669
1
19
29

I had to update my query in to places with Husqvik answer (as oposed to the answer VALEX provided). I noticed however Sentinels comment about losing the indexing advantage. I only had 300 rows. But it is good to know. Thank you all! – ionescu77 Sep 27 '17 at 09:42

score 0 · Answer 3 · answered Sep 15 '15 at 13:22

The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join. See this link: https://technet.microsoft.com/en-us/library/ms190409(v=sql.105).aspx

select *
from t1, t2
where t1.id = t2.id and t1.field1 = t2.field1 and t1.field2 = t2.field2;

score 0 · Answer 4 · answered Sep 15 '15 at 13:25

0

One solution is to use NVL and convert NULL into a scalar value.

select *
from t1
full outer join t2 
  ON NVL(t1.id, 0) = NVL(t2.id, 0)
  AND NVL(t1.field1, 0) = NVL(t2.field1, 0)
  AND NVL(t1.field2, 0) = NVL(t2.field2, 0)
;

answered Sep 15 '15 at 13:25

Jordan Parmer

36,042
30
97
119

1

All indexes are lost unless matching function based indexes are created – Sentinel Sep 15 '15 at 14:32

score 0 · Answer 5 · answered Sep 15 '15 at 13:38

Internally Oracle's own code (for refreshing materialised views, for example) makes use of the Sys_Op_Map_NonNull() function for this, which would make your join:

select *
from t1
full outer join t2 on (t1.id                         = t2.id and
                       t1.field1                     = t2.field2 and
                       Sys_Op_Map_NonNull(t1.field2) = Sys_Op_Map_NonNull(t2.field2));

I'm not sure that its use is officially supported, or if they have started documenting it publically though.

score 0 · Answer 6 · answered Sep 15 '15 at 14:38

This solution maintains the use of the using clause, but eliminates the one column in the using clause containing nulls (field2). Instead field2 is coalesced in the select list.

select id
     , field1
     , coalesce(t1.field2,t2.field2) field2
     , extra_field
from t1
full outer join t2 using (id, field1); --field2 removed from using clause.

SQL : FULL OUTER JOIN on null columns

6 Answers6