INNER JOIN ON vs WHERE: что выбрать?

INNER JOIN — это синтаксис ANSI, который вам следует использовать.

Этот подход обычно считается более читабельным, особенно когда вы соединяете много таблиц.

Кроме того, его легко заменить на OUTER JOIN, если возникнет такая необходимость.

Синтаксис WHERE более ориентирован на реляционную модель.

Результатом объединения двух таблиц с помощью JOIN является декартово произведение этих таблиц, к которому применяется фильтр, выбирающий только те строки, в которых совпадают соединяемые столбцы.

С этим проще разобраться с синтаксисом WHERE.

Что касается вашего примера, в MySQL (и в SQL в целом) эти два запроса являются синонимами.

Также обратите внимание, что в MySQL есть оператор STRAIGHT_JOIN.

С помощью этого оператора вы можете контролировать порядок соединения JOIN: какая таблица будет сканироваться в внешнем цикле, а какая в внутреннем.

Вы не можете контролировать это в MySQL с помощью синтаксиса WHERE.

Другие участники обсуждения уже отметили, что использование INNER JOIN повышает читаемость кода, и я с этим согласен. Позвольте мне объяснить, почему синтаксис JOIN более читабелен.

Основной запрос SELECT выглядит так:

SELECT stuff
FROM tables
WHERE conditions

Клаузула SELECT говорит нам что мы получаем, клаузула FROM указывает откуда мы это получаем, а клаузула WHERE говорит какие записи мы выбираем.

JOIN является утверждением о таблицах, о том, как они связаны друг с другом (концептуально объединяясь в одну таблицу).

Любые элементы запроса, которые управляют таблицами — откуда мы получаем данные — семантически принадлежат клаузе FROM (и, конечно, именно туда помещаются элементы JOIN). Помещение элементов соединения в клаузу WHERE смешивает какие записи мы выбираем и откуда мы их берем, поэтому предпочтительнее использовать синтаксис JOIN.

Имплицитный синтаксис соединений ANSI старый, менее очевидный и не рекомендуется.

Кроме того, реляционная алгебра позволяет менять местами предикаты в операторе WHERE и INNER JOIN, поэтому даже запросы с INNER JOIN и условиями в WHERE могут быть реорганизованы оптимизатором.

Я рекомендую писать запросы наиболее читаемым способом.

Иногда это означает, что INNER JOIN может быть относительно "неполным", и часть критериев помещается в WHERE, чтобы списки условий фильтрации были более удобными для поддержки.

Например, вместо:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Можно написать:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

Но, конечно, это зависит от конкретной ситуации.

Неявные соединения (так называемые в вашем первом запросе) становятся значительно более запутанными, трудно читаемыми и трудными в поддержке, когда вам нужно начать добавлять больше таблиц в ваш запрос. Представьте себе, что вы выполняете тот же запрос и вид соединения на четырех или пяти разных таблицах... это настоящая головная боль.

Использование явного соединения (ваш второй пример) намного более читаемо и легко в обслуживании.

Стандарт SQL:2003 изменил некоторые правила приоритетов, в результате чего оператор JOIN стал иметь приоритет над "запятой" для объединения. Это действительно может изменить результаты вашего запроса в зависимости от его структуры. Это вызвало некоторые проблемы у пользователей, когда MySQL версии 5.0.12 начал следовать этому стандарту.

В вашем примере ваши запросы будут работать одинаково. Но если вы добавите третью таблицу:

SELECT ... FROM table1, table2 JOIN table3 ON ... WHERE ...

То до версии MySQL 5.0.12 сначала объединялись table1 и table2, а затем table3. Теперь (начиная с версии 5.0.12) сначала объединяются table2 и table3, а затем уже table1. Это не всегда изменяет результаты, но может, и вы даже не заметите этого.

Я больше не использую синтаксис с "запятой", предпочитая второй пример. Во-первых, это гораздо более читаемо, поскольку условия JOIN располагаются рядом с самими JOIN, а не разделены в другую секцию запроса.