Oracle SQL中高效的四向连接

我有两个表,Table1和Table2,我想加入.每个表都有两个唯一的键,我们称之为Key1和Key2.我想做的是LEFT JOIN Table2到Table1,其中任何一个键匹配四种可能的组合中的任何一种：

> Table1.Key1 = Table2.Key1
> Table1.Key1 = Table2.Key2
> Table1.Key2 = Table2.Key1
> Table1.Key2 = Table2.Key2

我的问题是：有没有有效的方法来做到这一点？现在我想出了类似的东西,但它需要永远运行.

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
;

绝望,我知道……

编辑：以下示例数据和所需结果：

表格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

表2：

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

期望的结果：

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

解决方法

如果你只需要行的组合而不是排列(即如果table1.key与table2.key1和table2.key2具有相同的值,那么只返回一行),那么这应该有效：

SELECT a.*,CASE WHEN a.Key1 = b.Key2 THEN 1 ELSE 0 END AS match2,CASE WHEN a.Key2 = b.Key2 THEN 1 ELSE 0 END AS match4
FROM Table1 a
LEFT JOIN Table2 b
    on a.Key1 in (b.Key1,b.key2) or a.key2 in (b.key1,b.key2);

插入提供的数据,此解决方案确实有效,但需要进行汇总以提供您正在寻找的结果：

SELECT   a.key1,a.key2,a.sales,a.revenue,MAX (CASE WHEN a.key1 = b.key1 THEN 1 ELSE 0 END) AS match1,MAX (CASE WHEN a.key1 = b.key2 THEN 1 ELSE 0 END) AS match2,MAX (CASE WHEN a.key2 = b.key1 THEN 1 ELSE 0 END) AS match3,MAX (CASE WHEN a.key2 = b.key2 THEN 1 ELSE 0 END) AS match4
FROM     table1 a
         LEFT JOIN table2 b
            ON a.key1 IN (b.key1,b.key2) OR a.key2 IN (b.key1,b.key2)
GROUP BY a.key1,a.revenue;