ПРАВОЕ СОЕДИНЕНИЕ вместо подвыборки — реальный вариант использования?
Я избегал RIGHT OUTER JOIN, поскольку того же можно добиться, используя LEFT OUTER JOIN, если изменить порядок таблиц.
Однако в последнее время я работаю с необходимостью иметь большое количество объединений, и я часто сталкиваюсь с шаблоном, в котором ряд INNER JOIN добавляется LEFT JOIN к подзапросу, который сам содержит много INNER JOIN:
SELECT *
FROM Tab_1 INNER JOIN Tab_2 INNER JOIN Tab_3...
LEFT JOIN (SELECT *
FROM Tab_4 INNER JOIN Tab_5 INNER JOIN Tab_6....
)...
Сценарий тяжело читается. Я часто сталкиваюсь с подзаголовками. Некоторые из них являются коррелированными подзапросами, и производительность по всем направлениям не очень хорошая (вероятно, не только из-за того, как написаны сценарии).
Я мог бы привести его в порядок несколькими способами, такими как использование общих табличных выражений, представлений, промежуточных таблиц и т. д., но один RIGHT JOIN может устранить необходимость в подзапросах. Во многих случаях это улучшит производительность.
В приведенном ниже примере есть ли способ воспроизвести результат, полученный с помощью первых двух операторов SELECT, но используя только соединения INNER и LEFT?
DECLARE @A TABLE (Id INT)
DECLARE @B TABLE (Id_A INT, Id_C INT)
DECLARE @C TABLE (Id INT)
INSERT @A VALUES (1),(2)
INSERT @B VALUES (1,10),(2,20),(1,20)
INSERT @C VALUES (10),(30)
-- Although we want to see all the rows in A, we only want to see rows in C that have a match in B, which must itself match A
SELECT A.Id, T.Id
FROM
@A AS A
LEFT JOIN ( SELECT *
FROM @B AS B
INNER JOIN @C AS C ON B.Id_C = C.Id) AS T ON A.Id = T.Id_A;
-- NB Right join as although B and C MUST match, we only want to see them if they also have a row in A - otherwise null.
SELECT A.Id, C.Id
FROM
@B AS B
INNER JOIN @C AS C ON B.Id_C = C.Id
RIGHT JOIN @A AS A ON B.Id_A = A.Id;
Вы бы предпочли многословные подвыборки или RIGHT JOIN, предполагающие достойные комментарии в каждом случае?
Все статьи, которые я когда-либо читал, в значительной степени говорили о том, что я думаю о ПРАВИЛЬНЫХ СОЕДИНЕНИЯХ, что они не нужны и сбивают с толку. Достаточно ли силен этот случай, чтобы сломить культурное неприятие?
Тогда взломать порядок соединения? Отличный вопрос, мне очень интересно увидеть ответы. Несмотря на то, что это необычно, я бы проголосовал за ваш правильный синтаксис соединения. Если что-то окажется полезным, мы могли бы изучить это.
@jarlh Согласен, но в таком случае LEFT JOIN можно выполнить ту же работу?
t1 LEFT JOIN t2 дает тот же результат, что и t2 RIGHT JOIN t1 - всегда!
@jarlh здесь нет аргументов; как я уже сказал, я всегда избегал RIGHT JOIN, потому что я согласен с вами - так вы говорите, что предпочитаете синтаксис подзапроса в этом случае, или я упускаю возможное альтернативное простое решение, использующее только соединения LEFT и INNER?
Пожалуйста, задайте 1 (конкретный исследовательский, не повторяющийся) вопрос.
Ответы 2
Как писал @jarlh, большинство людей думают, что СЛЕВА НАПРАВО гораздо более интуитивно понятны, поэтому очень сложно увидеть ПРАВОЕ соединение в коде. Иногда я обнаруживал, что SQL Server создает лучшие планы запросов, когда я использую OUTER APPLY в сочетании с предложениями WHERE EXISTS вместо ваших LEFT JOIN и внутреннего INNER JOIN с WHERE EXISTS. Результат не сильно отличается от того, что у вас есть в первом примере:
SELECT A.Id, T.Id
FROM
#A AS A
OUTER APPLY (
SELECT C.Id FROM #C AS C
WHERE EXISTS (SELECT 1 FROM #B AS B WHERE A.Id = B.Id_a AND B.Id_C = C.Id) )T;
Это не то же самое. Exists является полусоединением; если бы в таблице B было 10 строк с одинаковыми id_a и id_c, ваш запрос вернул бы 1 строку, OP вернул бы 10.
Хотя это не совсем то же самое, что и оригинал, как указывает @GeorgeMenoutis, это хорошее предложение, которое сработает во многих случаях — я пробовал его в одном случае, и это действительно немного улучшило производительность, так что определенно полезный пример если не точный ответ.
Это настолько сильно отличается, что ожидается увеличение производительности. Оператор semijoin завершится на первой найденной строке, а не просканирует дополнительные строки и, возможно, также вернет их.
Как я уже писал, это зависит от случая, в некоторых случаях я видел, что он действительно использует полусоединение с таким количеством выполнений, сколько строк в #a (природа OUTER APPLY). В других случаях я получил тот же план выполнения, что и (LEFT JOIN вместе с INNER JOIN),
Я нашел ответ на этот вопрос в старых скриптах, которые я просматривал - я наткнулся на этот синтаксис, который выполняет ту же функцию, что и пример RIGHT JOIN, используя LEFT JOINs (или, по крайней мере, я думаю, что он делает - он определенно дает правильный результаты в примере):
DECLARE @A TABLE (Id INT)
DECLARE @B TABLE (Id_A INT, Id_C INT)
DECLARE @C TABLE (Id INT)
INSERT @A VALUES (1),(2)
INSERT @B VALUES (1,10),(2,20),(1,20)
INSERT @C VALUES (10),(30)
SELECT
A.Id, C.Id
FROM
@A AS A
LEFT JOIN @B AS B
INNER JOIN @C AS C
ON C.Id = B.Id_C
ON B.Id_A = A.Id
Я не знаю, есть ли название для этого шаблона, которого я раньше не видел в других местах работы, но, похоже, он работает как «вложенное» соединение, позволяя ЛЕВОМУ СОЕДИНЕНИЮ сохранять строки из более позднего ВНУТРЕННЕГО СОЕДИНЕНИЯ. .
Обновлено: я провел еще несколько исследований и, по-видимому, это синтаксис ANSI SQL для вложенных соединений, но... он не очень популярен!
Описательная статья
Соответствующий вопрос и ответ по обмену стеками
High Plains Grifter — вам нужны круглые скобки вокруг вложенных соединений, чтобы правильно сгруппировать их: FROM @A AS A LEFT JOIN ( @B AS B INNER JOIN @C AS C ON C.Id = B.Id_C ) ON B.Id_A = A.Id. В остальном я согласен с этой рекомендацией. Я довольно часто видел этот шаблон с соединениями "многие ко многим" (A-C), определенными с использованием промежуточной соединительной таблицы (B), особенно если к удаленной таблице применяются дополнительные условия.
Правильные соединения сбивают с толку, большинство людей считают, что main table LEFT JOIN optional data получить гораздо проще, чем optional data RIGHT JOIN main table. (Время от времени я ПРАВИЛЬНО СОЕДИНЯЛ таблицу календаря, чтобы получить строки также для дат без данных.)