Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl_a (id number, ndx1 number, ndx2 number, val number);
CREATE TABLE tbl_b (id number, ndx1 number, ndx2 number, val number);
INSERT INTO tbl_a VALUES (100, 1, 1, 2000);
1 rows affected

INSERT INTO tbl_a VALUES (200, 1, 1, 4000);
1 rows affected

INSERT INTO tbl_a VALUES (300, 1, 1, 6000);
1 rows affected

INSERT INTO tbl_a VALUES (400, 1, 1, 8000);
1 rows affected

INSERT INTO tbl_b VALUES (100, 1, 1, null);
1 rows affected

INSERT INTO tbl_b VALUES (200, 1, 1, 10000);
1 rows affected

INSERT INTO tbl_b VALUES (300, 1, 2, 40000);
1 rows affected

select *
from tbl_a;


ID
NDX1
NDX2
VAL


100
1
1
2000

200
1
1
4000

300
1
1
6000

400
1
1
8000


select *
from tbl_b;


ID
NDX1
NDX2
VAL


100
1
1
null

200
1
1
10000

300
1
2
40000


select id, ndx1, ndx2, val
from tbl_a
union 
select id, ndx1, ndx2, val
from tbl_b
;


ID
NDX1
NDX2
VAL


100
1
1
2000

200
1
1
4000

300
1
1
6000

400
1
1
8000

100
1
1
null

200
1
1
10000

300
1
2
40000


select a.id, a.ndx1, a.ndx2, a.val, b.ndx2, b.val
from tbl_a a
left outer join tbl_b b
on (a.id = b.id
    and a.ndx1 = b.ndx1)
;


ID
NDX1
NDX2
VAL
NDX2
VAL


100
1
1
2000
1
null

200
1
1
4000
1
10000

300
1
1
6000
2
40000

400
1
1
8000
null
null


select 
  a.id as id,
  a.ndx1 as ndx1,
  nvl(a.ndx2, b.ndx2) as ndx2,
  case when (a.ndx1 = b.ndx1
             and decode(a.ndx2, b.ndx2, 1, 0) = 1)
         then nvl(b.val, a.val)
       when (a.ndx1 = b.ndx1
             and a.ndx2 is null)
         then b.val
       when (b.ndx1 is null)
         then a.val
  end as val
from tbl_a a
left outer join tbl_b b
on (a.id = b.id
    and a.ndx1 = b.ndx1)
order by id, ndx1, ndx2
;


ID
NDX1
NDX2
VAL


100
1
1
2000

200
1
1
10000

300
1
1
null

400
1
1
8000
ID	NDX1	NDX2	VAL
100	1	1	2000
200	1	1	4000
300	1	1	6000
400	1	1	8000
100	1	1	null
200	1	1	10000
300	1	2	40000