Разница между фильтрацией запросов в JOIN и WHERE?

В SQL я пытаюсь фильтровать результаты на основе идентификатора и задаюсь вопросом, существует ли какая-либо логическая разница между

SELECT value 
FROM table1 
JOIN table2 ON table1.id = table2.id 
WHERE table1.id = 1

SELECT value 
FROM table1 
JOIN table2 ON table1.id = table2.id AND table1.id = 1

Мне кажется, что логика различна, хотя вы всегда будете получать одинаковый набор результатов, но я задавался вопросом, есть ли какие-либо условия, при которых вы получите два разных набора результатов (или они всегда будут возвращать то же самое два набора результатов)

Ответ 1

Ответ: НЕТ, но:

Я всегда предпочитаю делать следующее.

Всегда сохраняйте условия присоединения в разделе ON
Всегда помещайте фильтр в предложение where

Это делает запрос более удобочитаемым.

Итак, я буду использовать этот запрос:

SELECT value
FROM table1
INNER JOIN table2
        ON table1.id = table2.id
WHERE table1.id = 1

Однако, когда вы используете OUTER JOIN'S, существует большая разница в сохранении фильтра в состоянии ON и where.

Обработка логических запросов

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

(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
| (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias>
| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>;

Обработка логических запросов блок-схемы

(1) FROM: Фаза FROM идентифицирует исходные таблицы запросов и обрабатывает операторы таблиц. Каждый оператор таблицы применяет ряд подфаз. Например, фазы, участвующие в соединении, являются (1-J1) Декартово произведение, (1-J2) ON Filter, (1-J3) Добавить внешние строки. ОТ фаза генерирует виртуальную таблицу VT1.
(1-J1) Декартово произведение: эта фаза выполняет декартово произведение (перекрестное соединение) между двумя таблицами, участвующими в операторе таблицы, генерируя VT1-J1.
(1-J2) Фильтр ON: эта фаза фильтрует строки из VT1-J1 на основе предикат, который появляется в предложении ON (< on_predicate > ). Только строки, для которых предикат имеет значение TRUE, вставляются в VT1-J2.
(1-J3) Добавить внешние строки. Если указано OUTER JOIN (в отличие от CROSS JOIN или INNER JOIN), строки из сохраненного стола или таблиц для которых совпадение не было найдено, добавляются к строкам из VT1-J2 как внешние строки, генерирующие VT1-J3.
(2) WHERE: эта фаза фильтрует строки из VT1 на основе предикат, который появляется в предложении WHERE(). Только строки, для которых предикат имеет значение TRUE, вставляются в VT2.
(3) GROUP BY: Эта фаза упорядочивает строки из VT2 в группах на основе в списке столбцов, указанном в предложении GROUP BY, генерируя VT3. В конечном итоге, будет одна строка результатов для каждой группы.
(4) HAVING: Эта фаза фильтрует группы из VT3 на основе предикат, который появляется в предложении HAVING (< having_predicate > ). Вставляются только группы, для которых предикат имеет значение TRUE. в VT4.
(5) SELECT: Эта фаза обрабатывает элементы в предложении SELECT, генерируя VT5.
(5-1) Оценка выражений: Эта фаза оценивает выражения в список SELECT, генерирующий VT5-1.
(5-2) DISTINCT: Эта фаза удаляет повторяющиеся строки из VT5-1, генерируя VT5-2.
(5-3) TOP: Эта фаза фильтрует указанное верхнее число или процент строк из VT5-2 на основе логического упорядочения, определенного ORDER BY, генерируя таблицу VT5-3.
(6) ORDER BY: Эта фаза сортирует строки из VT5-3 в соответствии с список столбцов, указанный в предложении ORDER BY, генерирующий курсор VC6.

Отсылается от эта отличная ссылка.

Ответ 2

Пока нет разницы при использовании INNER JOINS, как указывал VR46, при использовании OUTER JOINS существует значительная разница и оценка значения во второй таблице (< для левых объединений - первая таблица для правых объединений). Рассмотрим следующую настройку:

DECLARE @Table1 TABLE ([ID] int)
DECLARE @Table2 TABLE ([Table1ID] int, [Value] varchar(50))

INSERT INTO @Table1
VALUES
(1),
(2),
(3)

INSERT INTO @Table2
VALUES
(1, 'test'),
(1, 'hello'),
(2, 'goodbye')

Если мы выберем его с помощью левого внешнего соединения и поместим условие в предложение where:

SELECT * FROM @Table1 T1
LEFT OUTER JOIN @Table2 T2
    ON T1.ID = T2.Table1ID
WHERE T2.Table1ID = 1

Получаем следующие результаты:

ID          Table1ID    Value
----------- ----------- --------------------------------------------------
1           1           test
1           1           hello

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

SELECT * FROM @Table1 T1
LEFT OUTER JOIN @Table2 T2
    ON T1.ID = T2.Table1ID
    AND T2.Table1ID = 1

Получаем следующие результаты:

ID          Table1ID    Value
----------- ----------- --------------------------------------------------
1           1           test
1           1           hello
2           NULL        NULL
3           NULL        NULL

Это потому, что мы больше не фильтруем результирующий набор с помощью идентификатора table1 из 1 - скорее мы фильтруем JOIN. Таким образом, даже несмотря на то, что идентификатор table1 из 2 DOES имеет совпадение во второй таблице, он исключается из объединения - но НЕ - результат (следовательно, нулевые значения).

Итак, для внутренних объединений это не имеет значения, но вы должны хранить его в предложении where для удобочитаемости и согласованности. Тем не менее, для внешних соединений вам нужно знать, что это имеет значение, когда вы ставите условие, так как оно повлияет на ваш результат.

Ответ 3

Я думаю, что ответ, обозначенный как "правильный", неверен. Зачем? Я пытаюсь объяснить:

У нас есть мнение

"Всегда сохраняйте условия соединения в ON. Всегда помещайте фильтр в разделе где"

И это неправильно. Если вы находитесь во внутреннем соединении, каждый раз ставьте параметры фильтра в предложение ON, а не где. Вы спрашиваете, почему? Попробуйте представить сложный запрос с общей суммой 10 таблиц (например, каждая таблица имеет 10 тыс. Рекурсов), со сложным предложением WHERE (например, используемыми функциями или расчетами). Если вы ставите критерии фильтрации в предложение ON, JOINS между этими 10 таблицами не возникает, предложение WHERE не будет выполняться вообще. В этом случае вы не выполняете вычисления 10000 ^ 10 в предложении WHERE. Это имеет смысл, не ставя фильтрацию параметров только в предложение WHERE.