В SQL / MySQL, в чем разница между "ON" и "WHERE" в операторе соединения?

Question

Следующие операторы дают тот же результат (один использует 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? Благодарю.

Answer 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Таким образом, возвращаются только те подарки, которые никогда не были отправлены.

Answer 2

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

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

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

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

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

Answer 4

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

Answer 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} } для левого присоединения и { 'ALICE': {'Teddy bear', NULL}, 'Flowers': {'Teddy bear', NULL} } для ПРАВИЛЬНОГО СОЕДИНЕНИЯ

Teddy bear Alice
Teddy bear Bob
Flowers    NULL

---

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

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

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

Flowers NULL

и для полного внешнего соединения приведет к{ NULL: {'ALICE', 'Bob'}, 'Flowers': {NULL} } для левого присоединения и { 'ALICE': {NULL, NULL}, 'Flowers': {NULL, NULL} } для правого присоединения

NULL    Alice
NULL    Bob
Flowers NULL

---

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

Answer 6

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