SQL JOIN: различия между WHERE и ON клаузулами

Основное различие между предложениями WHERE и ON в операциях SQL JOIN заключается во времени их выполнения и цели: предложение ON определяет условие соединения и указывает, как связаны таблицы, выполняясь в процессе соединения, тогда как предложение WHERE фильтрует итоговый набор результатов после завершения соединения. Понимание этого различия имеет решающее значение, особенно для OUTER JOIN, где размещение условий значительно влияет на результаты запросов, хотя для INNER JOIN функциональное различие часто минимально из-за эквивалентности оптимизатора.

Содержание

• Основные различия
• Порядок выполнения и логический поток
• Поведение INNER JOIN vs OUTER JOIN
• Вопросы производительности
• Лучшие практики и рекомендации
• Практические примеры и случаи использования
• Когда использовать каждое предложение

Основные различия

Фундаментальное различие между предложениями ON и WHERE в операциях SQL JOIN заключается в их цели и времени выполнения в процессе выполнения запроса. Предложение ON специально предназначено для определения условия соединения - оно указывает, как связаны таблицы и какие строки должны быть сопоставлены друг с другом. Здесь размещаются критерии, связывающие связанные записи между соединяемыми таблицами.

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

Как объясняется в документации Atlassian, “ON следует использовать для определения условия соединения, а WHERE - для фильтрации данных”. Хотя это не является строгим требованием, которое база данных принудительно выполняет, следование этому принципу делает запросы более читаемыми и логически организованными.

Порядок выполнения и логический поток

SQL-запросы следуют определенному логическому порядку операций, который объясняет, почему размещение условий имеет значение. Последовательность выполнения обычно соответствует следующему шаблону:

1. Предложение FROM - определяет базовые таблицы
2. Предложения JOIN - выполняет соединения с использованием условий ON
3. Предложение WHERE - фильтрует объединенный набор результатов
4. GROUP BY - агрегирует данные
5. HAVING - фильтрует сгруппированные данные
6. SELECT - указывает столбцы для возврата
7. ORDER BY - сортирует итоговый результат

Как отмечает один из участников Reddit, “Когда вы читаете запрос, сначала прочитайте предложение from, затем соединения, затем where (затем group by, having, order by, limit и, наконец, select). Вы создаете плоский промежуточный набор результатов, из которого в конечном итоге выбираете данные.”

Этот порядок выполнения означает, что условия ON оцениваются до условий WHERE. Когда вы соединяете две таблицы, база данных сначала создает декартово произведение на основе условий ON, а затем применяет предложение WHERE для фильтрации полученного соединения источник.

Поведение INNER JOIN vs OUTER JOIN

Различие между предложениями ON и WHERE становится особенно важным при работе с различными типами JOIN.

Поведение INNER JOIN

Для INNER JOIN различие между предложениями ON и WHERE часто минимально, поскольку INNER JOIN возвращают только строки, где есть совпадение в обеих таблицах. В этом контексте условия, размещенные в предложении ON или WHERE, обычно дают одинаковые результаты:

-- Эти запросы эквивалентны для INNER JOIN
SELECT *
FROM users
INNER JOIN houses ON users.id = houses.owner_id
WHERE houses.price > 100000;

SELECT *
FROM users
INNER JOIN houses ON users.id = houses.owner_id AND houses.price > 100000;

Поведение OUTER JOIN

Для OUTER JOIN размещение условий имеет существенное значение. Как объясняется на Stack Overflow, “Для OUTER JOIN они не обязательно взаимозаменяемы, в зависимости от стороны соединения, от которой они зависят.”

• Условия в предложении ON: Они оцениваются до операции соединения. Они влияют на то, какие строки сопоставляются, но не отфильтровывают несопоставленные строки из основной таблицы.
• Условия в предложении WHERE: Они оцениваются после операции соединения. Они могут отфильтровывать несопоставленные строки, эффективно преобразуя OUTER JOIN в INNER JOIN.

Рассмотрим этот пример LEFT JOIN:

-- Условие в предложении ON - сохраняет всех пользователей, фильтрует дома
SELECT u.name, h.address
FROM users u
LEFT JOIN houses h ON u.id = h.owner_id AND h.price > 100000;

-- Условие в предложении WHERE - фильтрует пользователей без соответствующих домов
SELECT u.name, h.address
FROM users u
LEFT JOIN houses h ON u.id = h.owner_id
WHERE h.price > 100000 OR h.price IS NULL;

Первый запрос возвращает всех пользователей, но показывает только дома стоимостью более 100 000 долларов. Второй запрос возвращает только пользователей, у которых либо дома стоимостью более 100 000 долларов, либо вообще нет домов.

Вопросы производительности

Производительность INNER JOIN

Для INNER JOIN оптимизаторы баз данных обычно достаточно умны, чтобы распознать эквивалентные условия в предложениях ON и WHERE и сгенерировать одинаковые планы выполнения. Как отмечается в блоге Percona, “Есть ли разница в производительности между размещением условий JOIN в предложении ON или WHERE в MySQL? Нет, разницы нет. Следующие запросы алгебраически эквивалентны внутри MySQL и будут иметь одинаковый план выполнения.”

Однако есть исключения. Как отмечает один из администраторов баз данных, “НЕ ВСЕГДА ВЕРНО, что разницы между предложением join и where нет. Я оптимизирую долго выполняющиеся запросы постоянно, и иногда запросы с использованием where clause работают лучше, чем с join, вплоть до 70 раз.”

Производительность OUTER JOIN

Для OUTER JOIN размещение может влиять на производительность, поскольку оно меняет стратегию выполнения. Документация Microsoft объясняет: “Если у вас есть только внутренние соединения, используйте предложение INNER JOIN для указания, какие строки должны быть сопоставлены, и предложение WHERE для указания, какие из этих строк должны быть выбраны. Обратите внимание, что это не относится, если у вас есть OUTER JOIN.”

Поведение оптимизатора

Современные оптимизаторы достаточно сложны, чтобы умно обрабатывать эквивалентные условия. Как объясняют участники Stack Overflow, “Вы не заставляете планировщик запросов, помещая условия в предложение ON или WHERE. Приличный оптимизатор/планировщик запросов должен уметь распознавать обе версии как эквивалентные (и они действительно эквивалентны) и выбирать из различных планов выполнения.”

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

Лучшие практики и рекомендации

Общие рекомендации

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

1. Используйте ON для условий соединения: Размещайте критерии, определяющие, как связаны таблицы, в предложении ON. Это делает вашу цель ясной и поддерживает логическое разделение.

2. Используйте WHERE для фильтрации: Применяйте все критерии пост-соединения в предложении WHERE. Это включает условия для отдельных таблиц или комбинации соединенных таблиц.

3. Будьте осторожны с OUTER JOIN: Помните, что размещение влияет как на логику, так и на производительность в сценариях OUTER JOIN. Условия в предложении ON влияют на сопоставление, тогда как условия в предложении WHERE влияют на фильтрацию итогового результата.

4. Учитывайте читаемость: Даже когда размещение не влияет на результаты, использование предложений в соответствии с их предполагаемой целью делает запросы легче для понимания и поддержки.

Расширенные соображения

Некоторые разработчики рекомендуют размещать фильтры в предложении ON даже для INNER JOIN. Как отмечает один из практиков, “Я лично размещаю любые фильтры по соединяемым таблицам в ON вместо WHERE, чтобы я мог изменять INNER на OUTER JOIN без необходимости корректировки остального кода.”

Этот подход имеет смысл, когда вы ожидаете, что типы соединений могут измениться со временем. Однако его чрезмерное использование может сделать запросы сложнее для чтения, поскольку предложение ON загромождается логикой фильтрации, которая не напрямую связана с операцией соединения.

Практические примеры и случаи использования

Пример 1: Базовый INNER JOIN

-- Четкое разделение: ON для соединения, WHERE для фильтрации
SELECT u.name, h.address, h.price
FROM users u
INNER JOIN houses h ON u.id = h.owner_id
WHERE h.price > 200000 AND u.status = 'active';

Этот запрос четко разделяет условие соединения (сопоставление пользователей с их домами) от критериев фильтрации (дорогие дома для активных пользователей).

Пример 2: LEFT JOIN с разным размещением условий

-- Условие в предложении ON - влияет на отображение домов
SELECT u.name, h.address, h.price
FROM users u
LEFT JOIN houses h ON u.id = h.owner_id AND h.price > 200000;

-- Условие в предложении WHERE - влияет на возвращаемых пользователей
SELECT u.name, h.address, h.price
FROM users u
LEFT JOIN houses h ON u.id = h.owner_id
WHERE h.price > 200000 OR h.price IS NULL;

Первый запрос возвращает всех пользователей, но показывает только их дорогие дома. Второй запрос возвращает только пользователей, у которых есть дорогие дома ИЛИ вообще нет домов.

Пример 3: Соединение нескольких таблиц

-- Сложный запрос с правильным использованием предложений
SELECT o.order_id, c.customer_name, p.product_name, o.quantity, o.total_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2024-01-01' 
  AND c.status = 'premium'
  AND o.total_price > 1000;

Этот пример демонстрирует правильное использование предложений: ON определяют отношения между таблицами, тогда как предложение WHERE применяет бизнес-правила для фильтрации итогового результата.

Когда использовать каждое предложение

Используйте предложение ON, когда:

1. Определяете отношения между таблицами: Основная цель - указать, как связаны таблицы
2. Устанавливаете условия соединения: Вам нужно сопоставлять записи между таблицами на основе конкретных критериев
3. Работаете с сопоставлением OUTER JOIN: Для LEFT/RIGHT соединений, когда вы хотите контролировать, какие строки сопоставляются, не исключая несопоставленные строки
4. Поддерживаете логику соединения: Когда вы хотите организовать условия соединения отдельно от логики фильтрации
5. Ожидаете изменения типов соединения: Когда вы предполагаете переключение между INNER и OUTER JOIN и хотите минимизировать изменения в коде

Используйте предложение WHERE, когда:

1. Фильтруете после соединения: Применяете бизнес-правила к итоговому набору результатов
2. Фильтруете отдельные таблицы: Фильтрация на основе условий, не связанных с отношениями соединения
3. Фильтруете результаты OUTER JOIN: Когда вы хотите исключить несопоставленные строки из OUTER JOIN
4. Используете сложную логику фильтрации: Когда ваша фильтрация включает несколько условий, охватывающих разные аспекты запроса
5. Оптимизируете производительность: В конкретных случаях, когда размещение влияет на планы выполнения (хотя это редкость с современными оптимизаторами)

Как подчеркивает The Data School, “ON следует использовать для определения условия соединения, а WHERE - для фильтрации данных”. Следование этому принципу приведет к более поддерживаемому и понятному SQL-коду.

Заключение

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

• Предложения ON определяют условия соединения и выполняются в процессе соединения, контролируя, как связаны таблицы
• Предложения WHERE фильтруют итоговый набор результатов после завершения всех соединений
• Для INNER JOIN размещение часто не влияет на результаты из-за эквивалентности оптимизатора
• Для OUTER JOIN размещение значительно влияет как на логику, так и на производительность
• Современные оптимизаторы обычно достаточно сложны, чтобы умно обрабатывать эквивалентные условия

Лучшие практики рекомендуют использовать предложения ON для условий соединения и WHERE для фильтрации, поддерживая четкое разделение ответственности в ваших SQL-запросах. Хотя могут быть крайние случаи, когда отклонение от этого подхода дает преимущества, следование этим рекомендациям обычно приводит к более читаемому, поддерживаемому и логически корректному SQL-коду.

При работе со сложными запросами, включающими несколько соединений или внешние соединения, всегда учитывайте, как размещение условий влияет на итоговый результат. Тщательно тестируйте свои запросы, особенно при переключении между INNER и OUTER JOIN, чтобы убедиться, что вы получаете ожидаемое поведение.

Источники

1. Stack Overflow - SQL JOIN: в чем разница между предложением WHERE и ON?
2. Atlassian - Расшифровка SQL: WHERE vs. Объяснение ON
3. ThoughtSpot - SQL Joins с использованием WHERE или ON
4. Pluralsight - Использование ON против WHERE для объединения и фильтрации данных в PostgreSQL Joins
5. The Data School - Разница между WHERE и ON в SQL
6. LearnSQL.com - В чем разница между WHERE и ON в SQL JOIN?
7. Bennadel - Оптимизация SQL и сравнение ON и WHERE
8. Блог jOOq - Разница между предложением JOIN…ON SQL и предложением WHERE
9. Microsoft Learn - Соединения (SQL Server)
10. Блог Percona - Есть ли разница в производительности между JOIN и WHERE?
11. Baeldung on SQL - Разница между предложением WHERE и предложением ON в JOIN
12. SQLpey - SQL JOIN ON vs WHERE: когда что использовать?