Efficient Four-Way Join in Oracle SQL

Question

I have two tables, Table1 and Table2, that I want to join. Each table has two unique keys, lets call them Key1 and Key2. What I want to do is LEFT JOIN Table2 to Table1 where any of the keys match in any of the four possible combinations:

• Table1.Key1 = Table2.Key1
• Table1.Key1 = Table2.Key2
• Table1.Key2 = Table2.Key1
• Table1.Key2 = Table2.Key2

My question is: is there any efficient way to do this? Right now I've come up with something like this, but it takes forever to run.

CREATE TABLE NEW_TABLE AS
SELECT a.*,
       CASE WHEN a.Key1 = b.Key1 THEN 1 ELSE 0 END AS match1,
       CASE WHEN a.Key1 = c.Key2 THEN 1 ELSE 0 END AS match2,
       CASE WHEN a.Key2 = b.Key1 THEN 1 ELSE 0 END AS match3,
       CASE WHEN a.Key2 = c.Key2 THEN 1 ELSE 0 END AS match4
FROM Table1 a
LEFT JOIN (Select Key1 From Table2 Where Key1 is not null) b
    on a.Key1 = b.Key1 or a.Key2 = b.Key1
LEFT JOIN (Select Key2 From Table2 Where Key2 is not null) c
    on a.Key1 = c.Key2 or a.Key2 = c.Key2
;

Hopeless, I know...

EDIT: Example data and desired results below:

Table 1:

Key1     Key2     Sales    Revenue
qwer!@   dhfgfw   455      30005
asdf#$   dfg654   221      28711
edfr2#   gg%%^f   213      31667
gthy!2   awd^&5   133      13345
rf$#22   34ffgg   655      41237
bhjk%g   w3erff   122      10066
f&*yhj   dffghj   126      11004

Table 2:

Key1     Key2 
qwer!@   {null}
{null}   dfg654
ffgww2   ppolkk
{null}   gthy!2
jjjj33   l00kjl
nmnmnm   34ffgg
awd^&5   {null}

Desired Result:

Key1     Key2     Sales    Revenue   match1    match2    match3    match4
qwer!@   dhfgfw   455      30005     1         0         0         0
asdf#$   dfg654   221      28711     0         0         0         1
edfr2#   gg%%^f   213      31667     0         0         0         0
gthy!2   awd^&5   133      13345     1         0         1         0
rf$#22   34ffgg   655      41237     0         0         0         1
bhjk%g   w3erff   122      10066     0         0         0         0
f&*yhj   dffghj   126      11004     0         0         0         0

Answer 1

or in join conditions can be a performance problem. I would suggest that you use exists instead:

SELECT a.*,
       (case when exists (select 1 from table2 b where a.Key1 = b.Key1) then 1 else 0 end) as match1,
       (case when exists (select 1 from table2 b where a.Key1 = b.Key2) then 1 else 0 end) as match2,
       (case when exists (select 1 from table2 b where a.Key2 = b.Key1) then 1 else 0 end) as match3,
       (case when exists (select 1 from table2 b where a.Key2 = b.Key2) then 1 else 0 end) as match4
FROM Table1 a;

Filtering out the NULL doesn't really matter because NULL will fail the comparison anyway.

For best performance, you want indexes on table2(key1) and table2(key2).