В SQL / MySQL, каково различие между “НА” и “ГДЕ” в операторе соединения?
Следующие утверждения дают тот же результат (каждый использует on, и другое использование where):
mysql> select * from gifts INNER JOIN sentGifts ON gifts.giftID = sentGifts.giftID;
mysql> select * from gifts INNER JOIN sentGifts WHERE gifts.giftID = sentGifts.giftID;
Я могу только видеть в случае Левого Внешнего объединения, находящего "несопоставленные" случаи:
(для обнаружения подарков, которые никогда не отправлялись никем),
mysql> select name from gifts LEFT OUTER JOIN sentgifts
ON gifts.giftID = sentgifts.giftID
WHERE sentgifts.giftID IS NULL;
В этом случае это сначала использует on, и затем where. Делает on сначала сделайте соответствие, и затем where "вторичная" фильтрация? Или есть ли более общее правило использования on по сравнению с where?Спасибо.
5 ответов
ГДЕ является частью запроса SELECT в целом, ON является частью каждое отдельное присоединение.
ВКЛ может ссылаться только на поля ранее использованных таблиц.
Если нет фактического совпадения с записью в левой таблице, LEFT JOIN возвращает одну запись из правой таблицы со всеми полями, установленными в NULLS . Затем предложение WHERE оценивает и фильтрует это.
В вашем запросе возвращаются только записи из подарков без совпадений в 'sentgifts'.
Вот пример
gifts
1 Teddy bear
2 Flowers
sentgifts
1 Alice
1 Bob
---
SELECT *
FROM gifts g
LEFT JOIN
sentgifts sg
ON g.giftID = sg.giftID
---
1 Teddy bear 1 Alice
1 Teddy bear 1 Bob
2 Flowers NULL NULL -- no match in sentgifts
---
SELECT *
FROM gifts g
LEFT JOIN
sentgifts sg
ON g.giftID = sg.giftID
WHERE sg.giftID IS NULL
---
2 Flowers NULL NULL -- no match in sentgifts
Как видите, фактическое совпадение не может оставить NULL в sentgifts.id , поэтому возвращаются только подарки, которые никогда не были отправлены. .
При использовании INNER JOIN , ON и WHERE будут иметь одинаковый результат. Таким образом,
select *
from Table1 t1
inner join Table2 t2 on t1.id = t2.id
where t1.Name = 'John'
будет иметь тот же результат, что и
select *
from Table1 t1
inner join Table2 t2 on t1.id = t2.id
and t1.Name = 'John'
. Как вы заметили, это не тот случай, когда используется OUTER JOIN . Построенный план запроса зависит от платформы базы данных, а также от специфики запроса и может быть изменен, поэтому принятие решений только на этой основе не даст гарантированного плана запроса.
Как правило, вы должны использовать столбцы, которые объединяют ваши таблицы в предложениях ON , и столбцы, которые используются для фильтрации в предложениях WHERE . Это обеспечивает лучшую читаемость.
ВКЛ. Пункт определяет отношения между таблицами.
Предложение WHERE описывает, какие строки вас интересуют.
Часто вы можете поменять местами их и все равно получить тот же результат, однако это не всегда так с левым внешним соединением.
- Если предложение
ONне выполняется, вы все равно получаете строку со столбцами из левой таблицы , но с нулями в столбцах из правой таблицы.- Если предложение
WHEREне выполняется, вы вообще не получите эту строку.
Хотя результаты одинаковы, «ON» сначала выполняет соединение, а затем извлекает данные объединенного набора. Поиск происходит быстрее, а нагрузка меньше. Но использование WHERE приводит к тому, что сначала выбираются два набора результатов, а затем применяется условие. Итак, вы знаете, что предпочтительнее.
Если вы используете JOIN, вам необходимо указать условия, на которых вы присоединяетесь. Этот список входит в предложение ON. Предложение WHERE используется для кондиционирования данных в любом месте запроса.