How to select value between two tables based on column existence and grouping in Oracle

Question

I'm trying to select a value from one of two tables based on the existence and matching of values in a few columns between the two tables. It can be looked at as tbl_b is an override table for tbl_a when certain columns match (ndx1, ndx2) and a value exists in the tbl_b val column. I'm looking for an efficient way to get the proper values (see rules and anticipated results below). I'm having trouble getting the grouping correctly to get the anticipated results (attempts in fiddle).

I'm currently running on Oracle 19c.

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);
INSERT INTO tbl_a VALUES (200, 1, 1, 4000);
INSERT INTO tbl_a VALUES (300, 1, 1, 6000);
INSERT INTO tbl_a VALUES (400, 1, 1, 8000);
INSERT INTO tbl_b VALUES (100, 1, 1, null);
INSERT INTO tbl_b VALUES (200, 1, 1, 10000);
INSERT INTO tbl_b VALUES (300, 1, 2, 40000);

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

These are the rules I'm trying to implement to get the appropriate values. I'm treating it like tbl_b is an override table for tbl_a (when there are matching ndx1 and ndx2 values and a tbl_b.val exists).

1. If ndx1 and ndx2 exists and match in both tables, take b.val if it exists else go with a.val
   ex.) if a.ndx1=b.ndx1 and a.ndx2=b.ndx2 then nvl(b.val, a.val)
2. If ndx1 exists and match in both tables but ndx2 not in tbl_b then take a.val
   ex.) if a.ndx1=b.ndx1 and b.ndx2 is null then take a.val
3. If ndx1 exists and match in both tables but ndx2 not in tbl_a then take b.val
   ex.) if a.ndx1=b.ndx1 and a.ndx2 is null then take b.val
4. No consideration for b.ndx1 exists where a.ndx1 not exists (tbl_a is driver)

Anticipated Result Table:

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

fiddle

Answer 1

Use a FULL OUTER JOIN and then COALESCE the columns giving precedence to tbl_b over tbl_a:

SELECT COALESCE(b.id, a.id) AS id,
       COALESCE(b.ndx1, a.ndx1) AS ndx1,
       COALESCE(b.ndx2, a.ndx2) AS ndx2,
       COALESCE(b.val, a.val) AS val
FROM   tbl_a a
       FULL OUTER JOIN tbl_b b
       ON     a.id   = b.id
          AND a.ndx1 = b.ndx1
          AND a.ndx2 = b.ndx2
ORDER BY id, ndx1, ndx2

Which, for the sample data, outputs:

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

fiddle