Define join condition

Question

I have two tables tab1 and code_list (see below). Tab1 is a large table (millions of records) and code_list is joined to tab1 using left join.

create table tab1 (
key1 varchar2(10)
, key2 varchar2(10)
)
;

insert into tab1 values ('_10', '__1');
insert into tab1 values ('_10', '__2');
insert into tab1 values ('_10', '__3');
insert into tab1 values ('_10', '_99');
insert into tab1 values ('_10', null);
insert into tab1 values ('_20', '__1');
insert into tab1 values ('_20', '__2');
insert into tab1 values ('_20', '__3');
insert into tab1 values ('_20', '_99');
insert into tab1 values ('_20', null);
commit;

create table code_list (
key1 varchar2(10)
, key2 varchar2(10)
, value varchar2(10)
)
;

insert into code_list values ('_10', '__1', 'value1');
insert into code_list values ('_10', '__2', 'value2');
insert into code_list values ('_10', '__3', 'value3');
insert into code_list values ('_10', null, 'value4');
insert into code_list values ('_20', '__1', 'value1');
insert into code_list values ('_20', '__2', 'value2');
insert into code_list values ('_20', '__3', 'value3');
insert into code_list values ('_20', null, 'value5');
commit;

I am trying to create and SQL statement that joins the two tables like this:

• join based on key1 column

• join based on key2 column

• if key2 in tab1 is null then join to null value in key2 column in code_list table
• if value in key2 column in tab1 does not exist in key2 column in code_list table then join to a row in code_list whose value in key2 column is NULL

So far I have following statement:

select t1.*
, t2.value
from tab1 t1
left join code_list t2 on t1.key1 = t2.key1 
and (nvl(t1.key2, 'x') = nvl(t2.key2, 'x'))
;

It works fine but it does not reflect the last from the above mentioned conditions (if value in key2 column in tab1 does not exist in key2 column in code_list table then join to a row in code_list whose value in key2 column is NULL).

It gives following result:

_10 __1 value1
_10 __2 value2
_10 __3 value3
_10 (null) value4
_20 __1 value1
_20 __2 value2
_20 __3 value3
_20 (null) value5
_10 _99 (null) 
_20 _99 (null) 

But I would like it to give the following result:

_10 __1 value1
_10 __2 value2
_10 __3 value3
_10 (null) value4
_20 __1 value1
_20 __2 value2
_20 __3 value3
_20 (null) value5
_10 _99 value4 
_20 _99 value5 

Last two rows are different.

Thanks for help.

Answer 1

select t1.*, nvl(t2.value,t3.value) value
from   tab1 t1 
         left join code_list t2 
           on  t1.key1 = t2.key1 
           and (nvl(t1.key2, 'x') = nvl(t2.key2, 'x'))
         left join code_list t3 
           on  t1.key1 = t3.key1 
           and t3.key2 is null

Don't forget to create functional index for a join condition for big table tab1: (key1,nvl(key2,'x'))