Эффективное Четырехходовое соединение в Oracle SQL

У меня есть две таблицы, Table1 и Table2,, к которым я хочу присоединиться. Каждая таблица имеет два уникальных ключа, назовем их Key1 и Key2. То, что я хочу сделать, - это левое соединение Table2 с Table1 , где любой из ключей соответствует любой из четырех возможных комбинаций:

  • Таблица1.Ключ1 = Таблица 2.Key1
  • Таблица1.Ключ1 = Таблица 2.Key2
  • Таблица1.Ключ2 = Таблица 2.Key1
  • Таблица1.Ключ2 = Таблица 2.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
2 4
sql performance left-join

2 ответа:

Если вам нужны только комбинации строк, а не перестановки (т. е. если table1.key имеет то же значение, что и table2.key1 и table2.key2 , то возвращается только одна строка), то это должно работать: 

SELECT a.*,
       CASE WHEN a.Key1 = b.Key1 THEN 1 ELSE 0 END AS match1,
       CASE WHEN a.Key1 = b.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 = 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.key2,
         a.sales,
         a.revenue;
3

or в условиях соединения могут возникнуть проблемы с производительностью. Я бы предложил вместо этого использовать exists: 

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;

Отфильтровывание NULL На самом деле не имеет значения, потому что NULL все равно провалит сравнение.

Для лучшей производительности вам нужны индексы на table2(key1) и table2(key2).

4