mysql的on和in用法_在SQL / MySQL中，連接語句中的“ON”和“WHERE”有什么區別?

0

ON is applied to the set used for creating the permutations of each record as a part of the JOIN operation

ON應用於作為連接操作的一部分創建每個記錄的排列的集合

WHERE specifies the filter applied after the JOIN operation

在哪里指定在聯接操作之后應用的篩選器

In effect, ON replaces each field that does not satisfy its condition with a NULL. Given the example by @Quassnoi

實際上，用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

---

The LEFT JOIN permutations would have been calculated for the following collections if there was no ON condition:

如果沒有條件，則為以下集合計算左連接排列:

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

{“泰迪熊”:{“愛麗絲”,“鮑勃”},“花”:{“愛麗絲”,“鮑勃”} }

with the g.giftID = sg.giftID ON condition, this is the collections that will be used for creating the permutations:

與g。giftID = sg。giftID條件下，這是用於創建排列的集合:

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

{“泰迪熊”:{“愛麗絲”,“鮑勃”},“花”:{零,零} }

which in effect is:

這實際上是:

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

{“泰迪熊”:{“愛麗絲”,“鮑勃”},“花”:{零} }

and so results in the LEFT JOIN of:

因此，左連接為:

Teddy bear Alice

Teddy bear Bob

Flowers NULL

and for a FULL OUTER JOIN you would have:

對於一個完整的外部連接你會有:

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL} } for LEFT JOIN and { 'ALICE': {'Teddy bear', NULL}, 'Flowers': {'Teddy bear', NULL} } for RIGHT JOIN:

{'Teddy bear': {'ALICE'， 'Bob'}， 'Flowers': {NULL}表示左連接，{'ALICE': {'Teddy bear'， NULL}， 'Flowers': {'Teddy bear'， NULL}表示右連接:

Teddy bear Alice

Teddy bear Bob

Flowers NULL

If you also had a condition such as ON g.giftID = 1 it would be

如果你也有一個條件，比如g。giftID = 1

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

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

which for LEFT JOIN would result in

左連接的結果是什么

Flowers NULL

花空

and for a FULL OUTER JOIN would result in { NULL: {'ALICE', 'Bob'}, 'Flowers': {NULL} } for LEFT JOIN and { 'ALICE': {NULL, NULL}, 'Flowers': {NULL, NULL} } for RIGHT JOIN

對於完整的外部連接，將會產生{NULL: {'ALICE'， 'Bob'}， 'Flowers': {NULL}表示左連接，{'ALICE': {NULL, NULL}， 'Flowers': {NULL, NULL}表示右連接

NULL Alice

NULL Bob

Flowers NULL

Note MySQL does not have a FULL OUTER JOIN and you need to apply UNION to LEFT JOIN and RIGHT JOIN

注意，MySQL沒有完整的外部連接，您需要將UNION應用於左連接和右連接。