En SQL / MySQL, Quelle est la différence entre "ON" et "WHERE" dans une déclaration de jointure?

les énoncés suivants donnent le même résultat (on utilise on, et l'autre en utilisant 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;

je ne peux voir que dans le cas d'une Jointure Externe Gauche trouver le "inégalée" cas:

(pour trouver les cadeaux qui n'ont jamais été envoyés par tout le monde)

mysql> select name from gifts LEFT OUTER JOIN sentgifts 
           ON gifts.giftID = sentgifts.giftID 
           WHERE sentgifts.giftID IS NULL;

dans ce cas, c'est d'abord on et where. L' on d'abord faire la correspondance, puis where le "secondaire" filtrage? Ou est-il un plus règle générale d'utilisation on contre where? Grâce.

42
demandé sur 太極者無極而生 2010-04-27 19:57:58

6 réponses

WHERE est une partie de l' SELECT requête dans son ensemble, ON fait partie de chaque jointure individuelle.

ON ne peut renvoyer qu'aux champs des tables utilisées précédemment.

Quand il n'y a pas de match contre un enregistrement dans la table de gauche, LEFT JOIN retourne un enregistrement de la table de droite avec tous les champs pour valeur NULLS. WHERE la clause évalue et filtre ensuite ceci.

dans votre requête, seuls les enregistrements de gifts sans correspondance 'sentgifts' sont retournés.

Voici l'exemple

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

comme vous pouvez le voir, aucune correspondance réelle ne peut laisser un NULLsentgifts.id, de sorte que seuls les cadeaux qui n'ont jamais été envoyés sont retournés.

47
répondu Quassnoi 2014-11-03 23:57:02

ON l'article définit la relation entre les tables.

WHERE l'article décrit les lignes qui vous intéressent.

autant de fois que vous pouvez échanger et obtenir le même résultat, mais ce n'est pas toujours le cas avec une jointure externe gauche.

  • Si le ON la clause échoue vous obtenez toujours une ligne avec les colonnes de la table de gauche mais avec les nulls dans les colonnes de la table de droite.
  • Si le WHERE l'article échoue, vous n'obtiendrez pas la ligne à tous.
42
répondu Mark Byers 2010-04-27 16:04:10

en utilisant INNER JOIN, ON et WHERE aura le même résultat. Donc,

select *
from Table1 t1
inner join Table2 t2 on t1.id = t2.id
where t1.Name = 'John'

aura exactement la même sortie que

select *
from Table1 t1
inner join Table2 t2 on t1.id = t2.id
    and t1.Name = 'John'

comme vous l'avez noté, ce n'est pas le cas lorsque vous utilisez OUTER JOIN. Ce que le plan de requête est construit dépend de la plate-forme de base de données ainsi que des spécificités de requête, et est sujet à changement, donc prendre des décisions sur cette seule base ne va pas donner un plan de requête garanti.

en règle générale, vous devez utiliser colonnes qui rejoignent vos tables en ON les clauses et les colonnes qui sont utilisées pour le filtrage dans WHERE les clauses. Ceci offre la meilleure lisibilité.

13
répondu RedFilter 2010-04-27 16:10:13

bien que les résultats soient les mêmes, le 'ON' fait d'abord la jointure puis récupère les données de l'ensemble jointé. La récupération est plus rapide et la charge est moindre. Mais en utilisant 'où' parce que les deux ensembles de résultats d'être récupéré d'abord et puis appliquer la condition. Donc vous savez ce qui est préférable.

2
répondu Kangkan 2010-04-27 16:01:46

si vous utilisez une jointure, vous devez spécifier les conditions sur lesquelles vous adhérez. Cette liste va dans une clause. Une clause WHERE est utilisée pour conditionner les données pour n'importe où dans la requête.

0
répondu brydgesk 2010-04-27 16:00:27
  • ON s'applique à l'ensemble utilisé pour créer les permutations de chaque enregistrement dans le cadre de l'opération de JOINTURE
  • où spécifie le filtre appliqué après l'opération de JOINTURE

en effet, ON remplace chaque champ qui ne remplit pas sa condition par un nul. Étant donné l'exemple de @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

---

les permutations de jointure de gauche auraient été calculé pour les collections suivantes s'il n'y avait pas de condition:

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

g.giftID = sg.giftID À la condition, c'est les collections qui sera utilisé pour la création de permutations:

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

qui en effet est le suivant:

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

et donc les résultats de la JOINTURE GAUCHE:

Teddy bear Alice
Teddy bear Bob
Flowers    NULL

et pour une JOINTURE EXTERNE COMPLÈTE vous aurait:

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL} } pour rejoindre à gauche et{ 'ALICE': {'Teddy bear', NULL}, 'Flowers': {'Teddy bear', NULL} } for RIGHT JOIN:

Teddy bear Alice
Teddy bear Bob
Flowers    NULL

Si vous avez une condition telle que ON g.giftID = 1 il serait

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

qui pour la jointure de gauche résulterait en

Flowers NULL

et pour une jointure extérieure complète résulterait en { NULL: {'ALICE', 'Bob'}, 'Flowers': {NULL} } pour rejoindre à gauche et{ 'ALICE': {NULL, NULL}, 'Flowers': {NULL, NULL} } for RIGHT JOIN

NULL    Alice
NULL    Bob
Flowers NULL

Remarque: MySQL n'a pas une adhésion extérieure complète et vous devez présenter une demande D'adhésion à gauche and RIGHT JOIN

0
répondu Hou 2018-04-23 17:46:25