Фильтрация с использованием JOIN вместо WHERE

В SQL (MSSQL, Oracle и т.д., что угодно) при объединении таблиц, каков прирост от добавления фильтра в оператор JOIN вместо того, чтобы иметь его в предложении WHERE?

то есть.

SELECT * FROM X INNER JOIN Y ON X.A = Y.A WHERE X.B = 'SOMETHING'

против

SELECT * FROM X INNER JOIN Y ON X.A = Y.A AND X.B = 'SOMETHING'

Я понимаю, что это не работает во всех случаях, но я заметил, что в некоторых случаях, по-видимому, наблюдается усиление производительности, поместив критерии фильтра в оператор JOIN. Однако, поскольку это часть оператора JOIN, это также может привести к тому, что он будет вести себя немного странно.

Мысли?

Ответ 1

Для запросов INNER JOIN характеристики производительности этих фильтров будут зависеть от многих факторов - размера таблиц, индексации, избирательности запроса и других факторов, специфичных для СУБД, на которых выполняется запрос.

В LEFT и RIGHT OUTER JOIN положение фильтра имеет значение гораздо больше, чем INNER JOIN, так как влияет на то, будет ли оно применяться раньше (предложение JOIN) или после (WHERE), соединение.

Ответ 2

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

Ответ 3

Для INNER JOIN я бы не ожидал разницы в производительности, а скорее, что тот же план будет использоваться, если бы фильтр находился в предложении JOIN... ON или WHERE. Я лично предпочитаю использовать критерии соединения в предложении JOIN и фильтрацию в предложении WHERE - способ привязать все "параметры" к оператору SQL в том же месте - это не обязательно разумно или хорошо, продуманный. Напротив, некоторым людям нравится иметь все в разделе JOIN, чтобы все было вместе.

Ситуация с внешними соединениями различна: между "LEFT OUTER JOIN b ON a.a_id = b.a_id AND b.type = 1" и "LEFT OUTER JOIN b" a aaaid = b.a_id WHERE b.type = 1 "- на самом деле последнее неявно принуждает внутреннее соединение. Это было бы еще одной причиной поместить все такие условия в предложение JOIN для согласованности.

Ответ 4

Эти синтаксисы являются синонимами и оптимизированы для одной и той же вещи большинством RDBMS.

Я обычно предпочитаю этот синтаксис:

SELECT  *
FROM    X
INNER JOIN
        Y
ON      X.A = Y.A
WHERE   X.B = 'SOMETHING'

когда B не является частью логической связи между A и B, а этот:

SELECT  *
FROM    X
INNER JOIN
        Y
ON      X.A = Y.A
        AND X.B = 'SOMETHING'

когда он есть.

Ответ 5

Ничего, кроме ясности и смысла. Если у вас нет внешних соединений.

Ответ 6

Как человек (а не оптимизатор) сам, при сохранении запроса, я искал бы условие соединения в предложении JOIN и условие поиска в предложении WHERE.

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