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.
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 NULL
sentgifts.id
, de sorte que seuls les cadeaux qui n'ont jamais été envoyés sont retournés.
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.
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é.
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.
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.
- 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