В SQL/MySQL какая разница между "ON" и "WHERE" в заявлении о соединении?

Следующие утверждения дают один и тот же результат (один использует 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;

Я могу видеть только в случае, когда Left Outer Join обнаруживает "непревзойденные" случаи:
(чтобы узнать подарки, которые никогда не посылали никому)

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? Спасибо.

Ответы

Ответ 1

WHERE является частью запроса SELECT в целом, ON является частью каждого отдельного соединения.

ON может ссылаться только на поля ранее использованных таблиц.

Если нет фактического совпадения с записью в левой таблице, LEFT JOIN возвращает одну запись из правой таблицы со всеми полями, установленными на NULLS. WHERE затем оценивает и фильтрует это.

В вашем запросе возвращаются только записи из gifts без соответствия в '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, поэтому возвращаются только те подарки, которые не были отправлены.

Ответ 2

Предложение ON определяет отношения между таблицами.

Предложение WHERE описывает, какие строки вас интересуют.

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

  • Если предложение ON не выполняется, вы все равно получаете строку со столбцами из левой таблицы, но с пустыми значениями в столбцах из правой таблицы.
  • Если WHERE не выполнено, вы вообще не получите эту строку.

Ответ 3

При использовании 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. Это обеспечивает наилучшую читаемость.

Ответ 4

Хотя результаты одинаковы, 'ON' сначала выполните соединение, а затем извлекут данные объединенного набора. Поиск быстрее, а загрузка меньше. Но, используя "WHERE", сначала нужно получить два набора результатов, а затем применить условие. Значит, вы знаете, что предпочтительнее.

Ответ 5

  • ON применяется к набору, используемому для создания перестановок каждой записи как части операции JOIN
  • WHERE указывает фильтр, примененный после операции JOIN

По сути, ON заменяет каждое поле, которое не удовлетворяет его условию, значением NULL. Приведенный пример @Quassnoi

gifts

1   Teddy bear
2   Flowers

sentgifts

1   Alice
1   Bob

---
SELECT  *
FROM    gifts g
LEFT JOIN
        sentgifts sg
ON      g.giftID = sg.giftID

---

Перестановки LEFT JOIN были бы рассчитаны для следующих коллекций, если бы не было условия ON:

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {'ALICE', 'Bob'} }

с g.giftID = sg.giftID ON, это коллекции, которые будут использоваться для создания перестановок:

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL, NULL} }

который в действительности является:

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL} }

и так приводит к левому соединению:

Teddy bear Alice
Teddy bear Bob
Flowers    NULL

и для ПОЛНОГО НАРУЖНОГО СОЕДИНЕНИЯ у вас будет:

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL} } для LEFT JOIN и { 'ALICE': {'Teddy bear', NULL}, 'Flowers': {'Teddy bear', NULL} } для RIGHT JOIN:

Teddy bear Alice
Teddy bear Bob
Flowers    NULL

Если бы у вас также было такое условие, как ON g.giftID = 1 это было бы

{ NULL: {'ALICE', 'Bob'}, 'Flowers': {NULL} }

который для LEFT JOIN приведет к

Flowers NULL

и для FULL OUTER JOIN будет получено { NULL: {'ALICE', 'Bob'}, 'Flowers': {NULL} } для LEFT JOIN и { 'ALICE': {NULL, NULL}, 'Flowers': {NULL, NULL} } для RIGHT JOIN

NULL    Alice
NULL    Bob
Flowers NULL

Примечание. В MySQL нет FULL OUTER JOIN, и вам необходимо применить UNION к LEFT JOIN и RIGHT JOIN.

Ответ 6

Если вы используете JOIN, вам нужно указать условия, в которых вы присоединяетесь. Этот список находится в предложении ON. Предложение WHERE используется для условий для данных в любом месте запроса.