Блог

Главная - Вопросы по программированию - Самосоединение при объединении двух таблиц

Самосоединение при объединении двух таблиц

#sql #self-join

#sql #самосоединение

Вопрос:

У меня есть 2 таблицы с отношениями родительского внешнего ключа Table1 — (id1, pIntID, fid) id1-первичный ключ, таблица внешнего ключа fid2 — (fid, значение) первичный ключ fid

значения будут примерно такими table1

 id1, pIntID, fid
1    IR1     AC
2    IR1     RJ
3    IR2     AC
4    IR2     AC

таблица2

 fid, value
AC   accept
RJ   reject

Я должен выбрать те PIN-коды из таблицы, у которой tablle2.value = Принять, но не отклонить. Я могу сделать это с помощью подзапроса, но это будет плохо работать с огромными данными, есть ли оптимальный способ сделать это?

это рабочий запрос:

 select t1.pIntid
from table1 t1 join
     table2 t2
     on t1.fid = t2.fid
where t2.value = 'accept' and
      t1.pIntid not in (select tx1.pintid
                        from table1 tx1 join
                             table2.tx2
                             on tx1.fid = tx2.fid
                        where tx2.value = 'reject'
                       )

Комментарии:

1. Прежде всего, в общем случае производительность не определяется тем, какой метод вы используете для выполнения операции. Во всех базах данных поставщиков базовые процессоры запросов оптимизируют запрос, используя более или менее одинаковые базовые алгоритмы объединения, независимо от того, как вы структурируете SQL. Итак, сначала, для ясности и удобства обслуживания, вы должны написать SQL так, чтобы он представлял функциональную цель, которую вы намереваетесь, а затем только исследовать альтернативные подходы, когда проблема с производительностью становится очевидной.

Ответ №1:

Это может быть немного запутанным, поскольку в предложении where есть ограничение, но я считаю, что это сработает:

 select t1.pintid
from table1 t1
join table2 t2 on t1.fid = t2.fid
where t2.value in ('accept', 'reject')
group by t1.pintid
having count(distinct t2.value) = 1
   and t2.value = 'accept'

Другим решением (более чистым, я думаю) было бы использовать NOT EXISTS для исключения тех, pintid которые содержат accept , но также reject и состояние:

 select t1.pintid
from table1 t1
join table2 t2 on t1.fid = t2.fid
where t2.value = 'accept'
  and not exists (
    select 1
    from table1 t1x
    join table2 t2x on t1x.fid = t2x.fid
    where t2x.value = 'reject'
      and t1.pintid = t1x.pintid
    )
group by t1.pintid

Ответ №2:

В качестве другой альтернативы вы могли бы использовать шаблон предотвращения объединения.

  SELECT t1.pIntid
   FROM table1 t1
   JOIN table2 t2
     ON t2.fid   = t1.fid
    AND t2.value = 'accept'
   LEFT
   JOIN table2 tx2
     ON tx2.fid   = t1.fid
    AND tx2.value = 'reject'
  WHERE tx2.fid IS NULL

Шаблон антисоединения — это внешнее объединение с условием в предложении WHERE, которое исключает строки, которые нашли совпадение.

Для оптимальной производительности мы хотим, чтобы в table2 был доступен соответствующий индекс покрытия. С fid и value в качестве ведущих столбцов в индексе.

Если в table2 есть несколько строк со значением=’accept’ для одного и того же fid, то операция объединения приведет к созданию «повторяющихся» строк.