Quelle est la différence entre les jointures gauche, droite, externe et interne ?

Question

Je me demande comment différencier toutes ces jonctions différentes ...

Answer 1

Exemple simple : Disons que vous avez un Students et une Lockers table. En SQL, la première table que vous spécifiez dans une jointure, Students est le GAUCHE et la seconde, Lockers est le DROIT table.

Chaque étudiant peut être assigné à un casier, donc il y a un LockerNumber dans la colonne Student table. Plus d'un élève peut potentiellement se trouver dans un seul casier, mais surtout au début de l'année scolaire, il se peut que certains élèves entrants n'aient pas de casier et que certains casiers n'aient pas d'élèves assignés.

Pour les besoins de cet exemple, disons que vous avez 100 étudiants dont 70 ont des casiers. Vous disposez d'un total de 50 casiers 40 d'entre eux ont au moins un étudiant et 10 casiers n'ont aucun étudiant.

INNER JOIN est équivalent à " montrez-moi tous les étudiants avec des casiers ".
Tout élève sans casier, ou tout casier sans élève est manquant.
Retourne 70 lignes

LEFT OUTER JOIN serait " Montrez-moi tous les élèves, avec leur casier correspondant s'ils en ont un. ".
Il peut s'agir d'une liste générale d'étudiants ou d'une liste d'étudiants sans casier.
Retourne 100 lignes

RIGHT OUTER JOIN serait " Montre-moi tous les casiers, et les étudiants qui y sont assignés s'il y en a. ".
Cela pourrait être utilisé pour identifier les casiers qui n'ont pas d'étudiants assignés, ou les casiers qui ont trop d'étudiants.
Retourne 80 lignes (liste de 70 étudiants dans les 40 casiers, plus les 10 casiers sans étudiant)

FULL OUTER JOIN serait stupide et probablement pas très utile.
Quelque chose comme " Montre-moi tous les étudiants et tous les casiers, et fais-les correspondre où tu peux. "
Retourne 110 lignes (les 100 élèves, y compris ceux qui n'ont pas de casier. Plus les 10 casiers sans étudiant)

CROSS JOIN est également assez stupide dans ce scénario.
Il n'utilise pas le lien lockernumber dans la table des étudiants, ce qui fait que l'on se retrouve avec une liste géante de tous les appariements possibles entre étudiants et casiers, qu'ils existent ou non.
Retourne 5000 lignes (100 étudiants x 50 casiers). Cela pourrait être utile (avec un filtrage) comme point de départ pour faire correspondre les nouveaux élèves avec les casiers vides.

Answer 2

Voici une vue graphique de toutes les jointures qui donne des explications visuelles claires.

et ici Représentation visuelle des jointures SQL expliqué en détail par C.L. Moffatt

Answer 3

Il existe trois types de jointures de base :

• INNER La jointure compare deux tables et ne renvoie que les résultats où une correspondance existe. Les enregistrements de la première table sont dupliqués lorsqu'ils correspondent à plusieurs résultats dans la deuxième. Les jointures INNER ont tendance à réduire la taille des ensembles de résultats, mais cela n'est pas garanti car les enregistrements peuvent être dupliqués.
• CROSS join compare deux tables et renvoie toutes les combinaisons possibles de lignes des deux tables. Vous pouvez obtenir beaucoup de résultats avec ce type de jointure, qui peuvent même ne pas être significatifs, donc à utiliser avec prudence.
• OUTER join compare deux tables et renvoie des données lorsqu'une correspondance est disponible ou des valeurs NULL sinon. Tout comme la jointure INNER, elle dupliquera les lignes d'une table lorsqu'elle correspondra à plusieurs enregistrements dans l'autre table. Les jointures OUTER ont tendance à élargir les ensembles de résultats, car elles ne suppriment pas d'enregistrements de l'ensemble. Vous devez également qualifier une jointure OUTER pour déterminer quand et où ajouter les valeurs NULL :
  • LEFT signifie conserver tous les enregistrements de la première table quoi qu'il arrive et insérer des valeurs NULL lorsque la deuxième table ne correspond pas.
  • RIGHT signifie le contraire : garder tous les enregistrements de la deuxième table quoi qu'il arrive et insérer des valeurs NULL lorsque la première table ne correspond pas.
  • FULL signifie conserver tous les enregistrements des deux tables, et insérer une valeur NULL dans l'une ou l'autre des tables s'il n'y a pas de correspondance.

Souvent, vous voyez la volonté OUTER mot-clé omis dans la syntaxe. Au lieu de cela, ce sera simplement "LEFT JOIN", "RIGHT JOIN", ou "FULL JOIN". Cela est dû au fait que les jointures INNER et CROSS n'ont aucune signification par rapport à LEFT, RIGHT ou FULL, et qu'elles suffisent donc à elles seules à indiquer sans ambiguïté une jointure OUTER.

Voici un exemple de cas où vous pouvez utiliser chaque type :

• INNER : Vous voulez retourner tous les enregistrements de la table "Invoice", ainsi que leurs "InvoiceLines" correspondantes. Cela suppose que chaque facture valide aura au moins une ligne.
• OUTER : Vous voulez retourner tous les enregistrements "InvoiceLines" pour une facture particulière, ainsi que leurs enregistrements "InventoryItem" correspondants. Il s'agit d'une entreprise qui vend également des services, de sorte que toutes les InvoiceLines n'auront pas un IventoryItem.
• CROSS : Vous avez une table de chiffres avec 10 lignes, chacune contenant les valeurs '0' à '9'. Vous souhaitez créer une table de plage de dates à joindre, de manière à obtenir un enregistrement pour chaque jour de la plage. En croisant cette table avec elle-même à plusieurs reprises, vous pouvez créer autant d'entiers consécutifs que vous le souhaitez (étant donné que vous commencez à 10 puissance 1, chaque jointure ajoute 1 à l'exposant). Utilisez ensuite la fonction DATEADD() pour ajouter ces valeurs à votre date de base pour la plage.

Answer 4

Il n'y a que 4 sortes :

1. Jointure interne : Le type le plus courant. Une ligne de sortie est produite pour chaque paire de lignes d'entrée qui correspondent aux conditions de jointure.
2. Jointure externe gauche : La même chose qu'une jointure interne, sauf que s'il y a une ligne pour laquelle aucune ligne correspondante dans la table de droite ne peut être trouvée, une ligne est sortie contenant les valeurs de la table de gauche, avec NULL pour chaque valeur du tableau de droite. Cela signifie que chaque ligne du tableau de gauche apparaîtra au moins une fois dans le résultat.
3. Jointure extérieure droite : La même chose qu'une jointure externe gauche, sauf que les rôles des tables sont inversés.
4. Jointure extérieure complète : Une combinaison de jointures externes gauche et droite. Chaque ligne des deux tables apparaîtra dans la sortie au moins une fois.

Une "jointure croisée" ou "jointure cartésienne" est simplement une jointure interne pour laquelle aucune condition de jointure n'a été spécifiée, ce qui fait que toutes les paires de lignes sont sorties.

Merci à RusselH d'avoir signalé les jointures FULL, que j'avais omises.

Answer 5

LEFT JOIN y RIGHT JOIN sont des types de OUTER JOIN s.

INNER JOIN est la valeur par défaut -- les lignes des deux tables doivent correspondre à la condition de jointure.