Предложение WHERE против ON при использовании JOIN

Предполагая, что у меня есть следующий код T-SQL:

SELECT * FROM Foo f
INNER JOIN Bar b ON b.BarId = f.BarId;
WHERE b.IsApproved = 1;

Следующий также возвращает тот же набор строк:

SELECT * FROM Foo f
INNER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId);

Это может быть не лучший образец примера, но есть ли разница в производительности между этими двумя?

Ответ 1

Нет, оптимизатор запросов достаточно умен, чтобы выбрать один и тот же план выполнения для обоих примеров.

Вы можете использовать SHOWPLAN для проверки плана выполнения.

Тем не менее, вы должны поместить все соединения соединения в предложение ON и все ограничения в предложении WHERE.

Ответ 2

Просто будьте осторожны с разницей с внешними соединениями. Запрос, в котором фильтр b.IsApproved (в правой таблице, строке) добавлен в ON условие JOIN:

SELECT * 
FROM Foo f 
LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId);

Это не то же самое, что размещение фильтра в предложении WHERE:

SELECT * 
FROM Foo f 
LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId)
WHERE (b.IsApproved = 1);

Так как для "неудачных" внешних подключений к Bar (т.е. там, где нет b.BarId для f.BarId), это оставит b.IsApproved как NULL для всех таких неудачных строк соединения, а эти строки затем будет отфильтровываться.

Другим способом рассмотрения этого является то, что для первого запроса LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId) всегда будет возвращать строки таблицы LEFT, так как LEFT OUTER JOIN гарантирует, что строки LEFT будут возвращены, даже если соединение завершится с ошибкой. Однако эффект добавления (b.IsApproved = 1) в условие LEFT OUTER JOIN заключается в том, чтобы NULL удалять любые столбцы правой таблицы, если (b.IsApproved = 1) является ложным, то есть в соответствии с теми же правилами, которые обычно применяются к условию LEFT JOIN на (b.BarId = f.BarId).

Обновление: Чтобы завершить вопрос, заданный Конрадом, эквивалентным LOJ для фильтра OPTIONAL будет:

SELECT * 
FROM Foo f 
LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId)
WHERE (b.IsApproved IS NULL OR b.IsApproved = 1);

то есть. В предложении WHERE должно учитываться как условие, происходит ли сбой соединения (NULL), и фильтр должен игнорироваться, и когда соединение завершается успешно, и фильтр должен применяться. (b.IsApproved или b.BarId можно проверить на NULL)

Я добавил здесь SqlFiddle здесь, в котором демонстрируются различия между различными местами размещения фильтра b.IsApproved относительно JOIN.

Ответ 3

SELECT * FROM Foo f
INNER JOIN Bar b ON b.BarId = f.BarId
WHERE b.IsApproved = 1;

Это лучшая форма. Легко читается и легко модифицируется. В деловом мире это то, с чем вы хотели бы пойти. Что касается производительности, они все те же.

Ответ 4

Мне кажется, что некоторые случаи, когда оптимизатор недостаточно умен, даже в последних версиях MSSQL, и разница в производительности была монстром.

Но это исключение, большинство разработчиков SQL Server оптимизируют проблему и получают правильный план.

Таким образом, используйте политику использования фильтров в предложении WHERE и оптимизируйте, когда это необходимо.

Ответ 5

Я только что проверил тест на четыре таблицы - одну основную таблицу с тремя INNER JOINs и в общей сложности четыре параметра и сравнил планы выполнения обоих подходов (используя критерии фильтра в ON JOIN и затем также в предложении WHERE).

Планы выполнения точно такие же. Я запускал это на SQL Server 2008 R2.