Условия в левое соединение (внешнее соединение) против внутреннего соединения

Question

SELECT A.COL1, B.COL1,C.COL1
FROM TABLEA A
LEFT JOIN TABLEB B ON A.COL1 = B.COL1
LEFT JOIN TABLEC C ON (
        C.COL3 IS NOT NULL
        AND (
              C.COL2 = 664
              AND A.COL1 = C.COL1
        )
)

Что касается технических особенностей SQL, что означает условие, записанное в скобках после LEFT JOIN TABLE C ON имею в виду? Зачем это нужно?

Answer 1

Внутреннее соединение (JOIN или INNER JOIN, CROSS JOIN или запятая) сначала выполняет CROSS JOIN. (Т.е. возвращает все строки, которые могут быть созданы путем добавления строки из ее левой таблицы и строки из ее правой таблицы.) Затем любое ON удаляет строки, которые не соответствуют его условию. OUTER JOIN возвращает строки соответствующего INNER JOIN plus для левой таблицы (LEFT) или правой таблицы (RIGHT) или обеих таблиц (FULL), любых несопоставленных строк, расширенных значениями NULL. После ОТ ГДЕ удаляются строки, которые не соответствуют его условию.

Если условие включено, тогда соответствующие строки удаляются в FROM. Но если это условие вместо этого в WHERE, то соответствующие строки и любые строки, включающие их через более поздние объединения, все еще удаляются. Таким образом, если FROM имеет только внутренние соединения, тогда не имеет значения, находится ли условие в ON или WHERE.

Но если FROM имеет условие OUTER JOIN ON, то строки перекрестного соединения, не удовлетворяющие условию, удаляются, и добавляются определенные строки, расширенные NULL, тогда как перемещение этого условия в WHERE выполняет удаление, но не добавление.

Для языка не обязательно иметь ON для INNER JOIN, так как вместо t1 INNER JOIN t2 ON condition можно привлечь (SELECT * FROM t1 INNER JOIN t2 WHERE condition) вместо.

Исходя из вышеизложенного, вы можете выработать следующее: Для последовательности ВНУТРЕННИХ СОЕДИНЕНИЙ после любого последнего ВНЕШНЕГО СОЕДИНЕНИЯ (в том числе когда нет ВНЕШНИХ СОЕДИНЕНИЙ) можно свободно перемещать условия между их ON и WHERE. Но не для ON или перед любым последним OUTER JOIN, потому что они могут влиять на его входы и, таким образом, влиять на то, какие NULL-строки выводятся. Нет никакой причины ожидать того же результата, если такое условие было перенесено из ON в WHERE.

Для вашего кода: Вероятно, запрос предназначен для возврата A.COL1 в качестве идентификатора со связанной информацией A, B и C, с теми, которые, тем не менее, не содержат информации B (с информацией B & C NULLed), и с теми, которые делают, но не имеют информации C или делают, но не имеют ненулевой C.COL3 или имеют, но, тем не менее, не содержат C.COL2=664 (с информацией C равной NULL).

Answer 2

С OUTER JOIN ваш C может быть NULL потому что нет совпадения для A.COL1.

Если это так, то никогда не может быть C.COL2 = 664 имеется в виду любой WHERE заявление выбросило бы ряд.

Поместив условие в ON пункт, который вы просите, чтобы быть применены к JOIN условие, а не как фильтр на результат.

Для внешних соединений это не имеет значения.

Answer 3

Более ранние версии ANSI SQL не содержали предложение ON для условий соединения - оно использовало предложение where для всего. Это было хорошо для внутренних объединений, но поскольку приложения баз данных начали использовать внешние объединения, возникли проблемы, которые возникли с этим подходом. Некоторые из вас могут помнить оригинальный синтаксис ANSI-89-эры для *= а также =*, Они использовались в предикатах для определения поведения внешнего соединения. В этом случае мы сохраним несовпадающие строки из A в дополнение к обычным строкам, возвращаемым из соединения:

SELECT A.COL1, B.COL1,C.COL1
FROM TABLEA A
LEFT JOIN TABLEB B ON A.COL1 = B.COL1
LEFT JOIN TABLEC C ON (
        C.COL3 IS NOT NULL
        AND (
              C.COL2 = 664
              AND A.COL1 = C.COL1
        )
)

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

Предложение ON: этот синтаксис позволяет указывать имена столбцов для ключей объединения в обеих таблицах. Предложение ON используется для объединения таблиц, в которых имена столбцов не совпадают в обеих таблицах. Условия соединения удаляются из условий фильтра в предложении WHERE.