inner join vs outer join
Jointure interne et jointure externe: préparez-vous à explorer les différences exactes entre jointure interne et externe
Avant d'explorer les différences entre la jointure interne et la jointure externe, voyons d'abord ce qu'est une jointure SQL?
Une clause de jointure est utilisée pour combiner des enregistrements ou pour manipuler les enregistrements de deux ou plusieurs tables via une condition de jointure. La condition de jointure indique comment les colonnes de chaque table sont mises en correspondance les unes avec les autres.
La jointure est basée sur une colonne liée entre ces tables. Un exemple le plus courant est la jointure entre deux tables via la colonne de clé primaire et la colonne de clé étrangère.
Supposons que nous ayons une table contenant les salaires des employés et une autre table contenant les détails des employés.
Dans ce cas, il y aura une colonne commune comme l'identifiant d'employé qui joindra ces deux tables. Cette colonne ID d'employé serait la clé primaire des tables de détails des employés et la clé étrangère dans la table des salaires des employés.
Il est très important d'avoir une clé commune entre les deux entités. Vous pouvez considérer une table comme une entité et la clé comme un lien commun entre les deux tables qui est utilisé pour l'opération de jointure.
lecteur de fichiers swf aucun objet flash Shockwave installé
Fondamentalement, il existe deux types de jointure dans SQL, c'est-à-dire Jointure interne et jointure externe . La jointure externe est subdivisée en trois types, c'est-à-dire Jointure externe gauche, jointure externe droite et jointure externe complète.
Dans cet article, nous verrons la différence entre Jointure interne et jointure externe en détail. Nous garderons les jointures croisées et les jointures inégales hors de la portée de cet article.
Ce que vous apprendrez:
- Qu'est-ce que Inner Join?
- Qu'est-ce que la jointure externe?
- Différence entre la jointure interne et externe
- Performance
- Jointure interne et externe MS Access
- Jointure gauche vs jointure externe gauche
- Jointure externe gauche vs jointure externe droite
- Différence entre la jointure interne et la jointure externe au format tabulaire
- Jointure interne et externe vs union
- Conclusion
- lecture recommandée
Qu'est-ce que Inner Join?
Une jointure interne ne renvoie que les lignes qui ont des valeurs correspondantes dans les deux tables (nous considérons ici que la jointure se fait entre les deux tables).
Qu'est-ce que la jointure externe?
La jointure externe comprend les lignes correspondantes ainsi que certaines des lignes non correspondantes entre les deux tables. Une jointure externe diffère fondamentalement de la jointure interne dans la façon dont elle gère la condition de correspondance fausse.
Il existe 3 types de jointure externe:
- Jointure externe gauche : Renvoie toutes les lignes de la table LEFT et les enregistrements correspondants entre les deux tables.
- Jointure externe droite : Renvoie toutes les lignes de la table RIGHT et les enregistrements correspondants entre les deux tables.
- Jointure externe complète : Il combine le résultat de la jointure externe gauche et de la jointure externe droite.
Différence entre la jointure interne et externe
(image la source )
Comme le montre le diagramme ci-dessus, il existe deux entités, à savoir le tableau 1 et le tableau 2, et les deux tableaux partagent des données communes.
Une jointure interne renverra la zone commune entre ces tables (la zone grisée verte dans le diagramme ci-dessus), c'est-à-dire tous les enregistrements communs entre le tableau 1 et le tableau 2.
Une jointure externe gauche renverra toutes les lignes de la table 1 et uniquement les lignes de la table 2 qui sont également communes à la table 1. Une jointure externe droite fera exactement le contraire. Il donnera tous les enregistrements de la table 2 et uniquement les enregistrements correspondants correspondants de la table 1.
De plus, une jointure externe complète nous donnera tous les enregistrements des tableaux 1 et 2.
Commençons par un exemple pour rendre cela plus clair.
Supposons que nous ayons deux les tables: EmpDetails et EmpSalary .
Tableau EmpDetails:
| EmployeeID | Nom de l'employé |
| 7 | Lis |
| 1 | John |
| deux | Samantha |
| 3 | Aucun |
| 4 | Soyeux |
| 5 | RAM |
| 6 | Arpit |
| 8 | Sita |
| 9 | Farah |
| dix | Jerry |
Tableau EmpSalary:
| EmployeeID | Nom de l'employé | EmployéSalaire |
|---|---|---|
| Onze | Rose | 90000 |
| 1 | John | 50 000 |
| deux | Samantha | 120000 |
| 3 | Aucun | 75 000 |
| 4 | Soyeux | 25 000 |
| 5 | RAM | 150000 |
| 6 | Arpit | 80000 |
| 12 | Sakshi | 45 000 |
| 13 | Jack | 250 000 |
Faisons une jointure intérieure sur ces deux tables et observons le résultat:
Mettre en doute:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails INNER JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID; Résultat:
| EmployeeID | Nom de l'employé | EmployéSalaire |
|---|---|---|
| 7 | Lis | NUL |
| 1 | John | 50 000 |
| deux | Samantha | 120000 |
| 3 | Aucun | 75 000 |
| 4 | Soyeux | 25 000 |
| 5 | RAM | 150000 |
| 6 | Arpit | 80000 |
Dans l'ensemble de résultats ci-dessus, vous pouvez voir que Inner Join a renvoyé les 6 premiers enregistrements qui étaient présents à la fois dans EmpDetails et EmpSalary ayant une clé correspondante, c'est-à-dire EmployeeID. Par conséquent, si A et B sont deux entités, la jointure intérieure renverra le jeu de résultats qui sera égal à «Enregistrements en A et B», en fonction de la clé correspondante.
Voyons maintenant ce que fera une jointure externe gauche.
Mettre en doute:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails LEFT JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID; Résultat:
| EmployeeID | Nom de l'employé | EmployéSalaire |
|---|---|---|
| 1 | John | 50 000 |
| deux | Samantha | 120000 |
| 3 | Aucun | 75 000 |
| 4 | Soyeux | 25 000 |
| 5 | RAM | 150000 |
| 6 | Arpit | 80000 |
| 8 | Sita | NUL |
| 9 | Farah | NUL |
| dix | Jerry | NUL |
Dans le jeu de résultats ci-dessus, vous pouvez voir que la jointure externe gauche a renvoyé tous les 10 enregistrements de la table LEFT, c'est-à-dire la table EmpDetails et que les 6 premiers enregistrements correspondent, elle a renvoyé le salaire de l'employé pour ces enregistrements correspondants.
Comme le reste des enregistrements n'ont pas de clé correspondante dans la table RIGHT, c'est-à-dire la table EmpSalary, il a renvoyé NULL correspondant à ceux-ci. Étant donné que Lily, Sita, Farah et Jerry n'ont pas d'ID d'employé correspondant dans la table EmpSalary, leur salaire apparaît comme NULL dans l'ensemble de résultats.
Ainsi, si A et B sont deux entités, la jointure externe gauche renverra l'ensemble de résultats qui sera égal à «Enregistrements dans A NOT B», en fonction de la clé correspondante.
Voyons maintenant ce que fait la jointure externe droite.
Mettre en doute:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails RIGHT join EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID; Résultat:
| EmployeeID | Nom de l'employé | EmployéSalaire |
|---|---|---|
| NUL | NUL | 90000 |
| 1 | John | 50 000 |
| deux | Samantha | 120000 |
| 3 | Aucun | 75 000 |
| 4 | Soyeux | 25 000 |
| 5 | RAM | 150000 |
| 6 | Arpit | 80000 |
| NUL | NUL | 250 000 |
| NUL | NUL | 250 000 |
Dans l'ensemble de résultats ci-dessus, vous pouvez voir que la jointure externe droite a fait exactement le contraire de la jointure gauche. Il a renvoyé tous les salaires de la table de droite, c'est-à-dire la table EmpSalary.
Mais, comme Rose, Sakshi et Jack n'ont pas d'ID d'employé correspondant dans la table de gauche, c'est-à-dire la table EmpDetails, nous avons obtenu leur ID d'employé et EmployeeName comme NULL dans la table de gauche.
Ainsi, si A et B sont deux entités, alors la jointure externe droite renverra le jeu de résultats qui sera égal à «Enregistrements dans B NOT A», en fonction de la clé correspondante.
Voyons également quel sera le jeu de résultats si nous faisons une opération de sélection sur toutes les colonnes des deux tables.
Mettre en doute:
SELECT * FROM EmpDetails RIGHT JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;Résultat:
| EmployeeID | Nom de l'employé | EmployeeID | Nom de l'employé | EmployéSalaire |
|---|---|---|---|---|
| NUL | NUL | Onze | Rose | 90000 |
| 1 | John | 1 | John | 50 000 |
| deux | Samantha | deux | Samantha | 120000 |
| 3 | Aucun | 3 | Aucun | 75 000 |
| 4 | Soyeux | 4 | Soyeux | 25 000 |
| 5 | RAM | 5 | RAM | 150000 |
| 6 | Arpit | 6 | Arpit | 80000 |
| NUL | NUL | 12 | Sakshi | 250 000 |
| NUL | NUL | 13 | Jack | 250 000 |
Maintenant, passons à la jointure complète.
Une jointure externe complète est effectuée lorsque nous voulons toutes les données des deux tables, qu'il y ait une correspondance ou non. Par conséquent, si je veux tous les employés même si je ne trouve pas de clé correspondante, je vais exécuter une requête comme indiqué ci-dessous.
Mettre en doute:
SELECT * FROM EmpDetails FULL JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;Résultat:
| EmployeeID | Nom de l'employé | EmployeeID | Nom de l'employé | EmployéSalaire |
|---|---|---|---|---|
| 7 | Lis | NUL | NUL | NUL |
| 1 | John | 1 | John | 50 000 |
| deux | Samantha | deux | Samantha | 120000 |
| 3 | Aucun | 3 | Aucun | 75 000 |
| 4 | Soyeux | 4 | Soyeux | 25 000 |
| 5 | RAM | 5 | RAM | 150000 |
| 6 | Arpit | 6 | Arpit | 80000 |
| 8 | Sita | NUL | NUL | NUL |
| 9 | Farah | NUL | NUL | NUL |
| dix | Jerry | NUL | NUL | NUL |
| NUL | NUL | Onze | Rose | 90000 |
| NUL | NUL | 12 | Sakshi | 250 000 |
| NUL | NUL | 13 | Jack | 250 000 |
Vous pouvez voir dans le jeu de résultats ci-dessus que, comme les six premiers enregistrements correspondent dans les deux tables, nous avons toutes les données sans NULL. Les quatre enregistrements suivants existent dans la table de gauche mais pas dans la table de droite, donc les données correspondantes dans la table de droite sont NULL.
Les trois derniers enregistrements existent dans la table de droite et non dans la table de gauche, nous avons donc NULL dans les données correspondantes de la table de gauche. Ainsi, si A et B sont deux entités, la jointure externe complète renverra le jeu de résultats qui sera égal à «Enregistrements dans A ET B», quelle que soit la clé correspondante.
Théoriquement, il s'agit d'une combinaison de jointure gauche et de jointure droite.
Performance
Comparons une jointure interne à une jointure externe gauche dans le serveur SQL. Parlant de la vitesse de fonctionnement, une jointure externe gauche n'est évidemment pas plus rapide qu'une jointure interne.
Selon la définition, une jointure externe, que ce soit la gauche ou la droite, elle doit effectuer tout le travail d'une jointure interne avec le travail supplémentaire nul - étendant les résultats. Une jointure externe devrait renvoyer un plus grand nombre d'enregistrements, ce qui augmente encore sa durée totale d'exécution simplement en raison du jeu de résultats plus grand.
Ainsi, une jointure externe est plus lente qu'une jointure interne.
De plus, il peut y avoir des situations spécifiques où la jointure gauche sera plus rapide qu'une jointure interne, mais nous ne pouvons pas continuer à les remplacer l'une par l'autre car une jointure externe gauche n'est pas fonctionnellement équivalente à une jointure interne.
Laissez-nous discuter d'une instance où la jointure gauche pourrait être plus rapide que la jointure intérieure. Si les tables impliquées dans l'opération de jointure sont trop petites, disons qu'elles ont moins de 10 enregistrements et que les tables ne possèdent pas suffisamment d'index pour couvrir la requête, dans ce cas, la jointure gauche est généralement plus rapide que la jointure interne.
Créons les deux tableaux ci-dessous et faisons un INNER JOIN et un LEFT OUTER JOIN entre eux à titre d'exemple:
CREATE TABLE #Table1 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Table1 (ID, Name) VALUES (1, 'A') INSERT #Table1 (ID, Name) VALUES (2, 'B') INSERT #Table1 (ID, Name) VALUES (3, 'C') INSERT #Table1 (ID, Name) VALUES (4, 'D') INSERT #Table1 (ID, Name) VALUES (5, 'E') CREATE TABLE #Table2 ( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL ) INSERT #Table2 (ID, Name) VALUES (1, 'A') INSERT #Table2 (ID, Name) VALUES (2, 'B') INSERT #Table2 (ID, Name) VALUES (3, 'C') INSERT #Table2 (ID, Name) VALUES (4, 'D') INSERT #Table2 (ID, Name) VALUES (5, 'E') SELECT * FROM #Table1 t1 INNER JOIN #Table2 t2 ON t2.Name = t1.Name | identifiant | Nom | identifiant | Nom | |
|---|---|---|---|---|
Vous trouverez ci-dessous la visualisation d'une jointure interne:  | Ci-dessous, la visualisation d'une jointure externe  | |||
| 1 | 1 | À | 1 | À |
| deux | deux | B | deux | B |
| 3 | 3 | C | 3 | C |
| 4 | 4 | ré | 4 | ré |
| 5 | 5 | EST | 5 | EST |
SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS bar ON (55=55); | identifiant | Nom | identifiant | Nom | |
|---|---|---|---|---|
| 1 | 1 | À | 1 | À |
| deux | deux | B | deux | B |
| 3 | 3 | C | 3 | C |
| 4 | 4 | ré | 4 | ré |
| 5 | 5 | EST | 5 | EST |
Comme vous pouvez le voir ci-dessus, les deux requêtes ont renvoyé le même jeu de résultats. Dans ce cas, si vous affichez le plan d'exécution des deux requêtes, vous constaterez que la jointure interne a coûté plus cher que la jointure externe. En effet, pour une jointure interne, le serveur SQL effectue une correspondance de hachage alors qu'il effectue des boucles imbriquées pour la jointure gauche.
Une correspondance de hachage est normalement plus rapide que les boucles imbriquées. Mais, dans ce cas, comme le nombre de lignes est si petit et qu'il n'y a pas d'index à utiliser (comme nous faisons une jointure sur la colonne de nom), l'opération de hachage s'est avérée une requête de jointure interne la plus coûteuse.
Cependant, si vous modifiez la clé correspondante dans la requête de jointure de Nom à ID et s'il y a un grand nombre de lignes dans la table, vous constaterez que la jointure interne sera plus rapide que la jointure externe gauche.
Jointure interne et externe MS Access
Lorsque vous utilisez plusieurs sources de données dans une requête MS Access, vous appliquez des JOINs pour contrôler les enregistrements que vous souhaitez voir, en fonction de la manière dont les sources de données sont liées les unes aux autres.
Dans une jointure interne, seuls les éléments associés des deux tables sont combinés dans un seul jeu de résultats. Il s'agit d'une jointure par défaut dans Access et la plus fréquemment utilisée également. Si vous appliquez une jointure mais que vous ne spécifiez pas explicitement de quel type de jointure il s'agit, Access suppose qu'il s'agit d'une jointure interne.
Dans les jointures externes, toutes les données associées des deux tables sont correctement combinées, ainsi que toutes les lignes restantes d'une table. Dans les jointures externes complètes, toutes les données sont combinées dans la mesure du possible.
Jointure gauche vs jointure externe gauche
Dans SQL Server, le mot clé externe est facultatif lorsque vous appliquez une jointure externe gauche. Ainsi, cela ne fait aucune différence si vous écrivez «LEFT OUTER JOIN» ou «LEFT JOIN» car les deux vont vous donner le même résultat.
A LEFT JOIN B est une syntaxe équivalente à A LEFT OUTER JOIN B.
Voici la liste des syntaxes équivalentes dans le serveur SQL:
(image la source )
Jointure externe gauche vs jointure externe droite
Nous avons déjà vu cette différence dans cet article. Vous pouvez vous référer aux requêtes de jointure externe gauche et jointure externe droite et à l'ensemble de résultats pour voir la différence.
La principale différence entre la jointure gauche et la jointure droite réside dans l'inclusion de lignes sans correspondance. La jointure externe gauche inclut les lignes sans correspondance de la table qui se trouve à gauche de la clause de jointure, tandis qu'une jointure externe droite inclut les lignes sans correspondance de la table qui se trouve à droite de la clause de jointure.
Les gens demandent ce qui est préférable d'utiliser, c'est-à-dire jointure gauche ou jointure droite? Fondamentalement, il s'agit du même type d'opérations sauf avec leurs arguments inversés. Par conséquent, lorsque vous demandez quelle jointure utiliser, vous demandez en fait s'il faut écrire un une. C'est juste une question de préférence.
Généralement, les utilisateurs préfèrent utiliser la jointure gauche dans leur requête SQL. Je vous suggère de rester cohérent dans la manière dont vous écrivez la requête afin d'éviter toute confusion dans l'interprétation de la requête.
Nous avons tout vu sur les jointures internes et tous les types de jointures externes jusqu'à présent. Résumons rapidement la différence entre Inner Join et Outer Join.
Différence entre la jointure interne et la jointure externe au format tabulaire
| Jointure interne | Jointure externe |
|---|---|
| Renvoie uniquement les lignes qui ont des valeurs correspondantes dans les deux tables. | Inclut les lignes correspondantes ainsi que certaines des lignes non correspondantes entre les deux tables. |
| Dans le cas où il y a un grand nombre de lignes dans les tables et qu'il y a un index à utiliser, INNER JOIN est généralement plus rapide que OUTER JOIN. | Généralement, un OUTER JOIN est plus lent qu'un INNER JOIN car il doit retourner plus d'enregistrements par rapport à INNER JOIN. Cependant, il peut y avoir des scénarios spécifiques où OUTER JOIN est plus rapide. |
| Lorsqu'une correspondance n'est pas trouvée, elle ne renvoie rien. | Lorsqu'une correspondance n'est pas trouvée, un NULL est placé dans la valeur de colonne renvoyée. |
| Utilisez INNER JOIN lorsque vous souhaitez rechercher des informations détaillées sur une colonne spécifique. | Utilisez OUTER JOIN lorsque vous souhaitez afficher la liste de toutes les informations des deux tableaux. |
| INNER JOIN agit comme un filtre. Il doit y avoir une correspondance sur les deux tables pour qu'une jointure interne renvoie des données. | Ils agissent comme des ajouts de données. |
| La notation de jointure implicite existe pour la jointure interne qui inscrit les tables à joindre dans la manière séparée par des virgules dans la clause FROM. Exemple: SELECT * FROM produit, catégorie WHERE product.CategoryID = category.CategoryID; | Aucune notation de jointure implicite n'est là pour la jointure externe. |
Jointure interne et externe vs union
Parfois, nous confondons Rejoindre et Union et c'est aussi l'une des questions les plus fréquemment posées dans Entretiens SQL . Nous avons déjà vu la différence entre la jointure interne et la jointure externe. Voyons maintenant en quoi un JOIN est différent d'un UNION.
UNION place une ligne de requêtes les unes après les autres, tandis que join crée un produit cartésien et le sous-ensemble. Ainsi, UNION et JOIN sont des opérations complètement différentes.
Exécutons les deux requêtes ci-dessous dans MySQL et voyons leur résultat.
Requête UNION:
SELECT 28 AS bah UNION SELECT 35 AS bah; Résultat:
| Bah | |
|---|---|
| 1 | 28 |
| deux | 35 |
REJOINDRE la requête:
SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS bar ON (55=55); Résultat:
| toto | Bar | |
|---|---|---|
| 1 | 38 | 35 |
Une opération UNION place le résultat de deux requêtes ou plus dans un seul jeu de résultats. Ce jeu de résultats contient tous les enregistrements renvoyés via toutes les requêtes impliquées dans UNION. Ainsi, fondamentalement, une UNION combine les deux ensembles de résultats ensemble.
Une opération de jointure récupère les données de deux tables ou plus en fonction des relations logiques entre ces tables, c'est-à-dire en fonction de la condition de jointure. Dans la requête de jointure, les données d'une table sont utilisées pour sélectionner des enregistrements d'une autre table. Il vous permet de lier des données similaires présentes sur différentes tables.
Pour le comprendre très simplement, vous pouvez dire qu'une UNION combine des lignes de deux tables alors qu'une jointure combine des colonnes de deux ou plusieurs tables. Ainsi, les deux sont utilisés pour combiner les données de n tables, mais la différence réside dans la façon dont les données sont combinées.
Vous trouverez ci-dessous les représentations illustrées d'UNION et de JOIN.
questions d'entretien unix pour l'assistance à la production
Ce qui précède est une représentation illustrée d'une opération de jointure illustrant que chaque enregistrement de l'ensemble de résultats contient des colonnes des deux tables, c'est-à-dire le tableau A et le tableau B. Ce résultat est renvoyé en fonction de la condition de jointure appliquée dans la requête.
Une jointure est généralement le résultat d'une dénormalisation (à l'opposé de la normalisation) et elle utilise la clé étrangère d'une table pour rechercher les valeurs de colonne en utilisant la clé primaire dans une autre table.
Ce qui précède est une représentation graphique d'une opération UNION illustrant que chaque enregistrement dans l'ensemble de résultats est une ligne de l'une des deux tables. Ainsi, le résultat de l'UNION a combiné les lignes du tableau A et du tableau B.
Lectures complémentaires = >> MySQL UNION expliqué avec des exemples
Conclusion
Dans cet article, nous avons vu les principales différences entre les Jointure interne et jointure externe dans SQL . Nous avons également vu la classification d'une jointure externe, c'est-à-dire jointure gauche, jointure droite et jointure complète. Nous avons vu comment chacun de ces types de jointure fonctionne et comment ils diffèrent les uns des autres.
Nous avons également effectué une comparaison des performances entre ces types de jointure. Nous avons également discuté de la différence entre une adhésion et un syndicat.
Lire aussi = >> Types de jointure MySQL
J'espère que cet article vous aurait aidé à dissiper vos doutes concernant les différences entre les différents types de jointures. Nous sommes sûrs que cela vous permettra de décider du type de jointure à choisir en fonction de l'ensemble de résultats souhaité.
lecture recommandée
- Différence exacte entre la vérification et la validation avec des exemples
- Modem vs routeur: connaître la différence exacte
- Différence entre SQL et MySQL et SQL Server (avec exemples)
- Tutoriel Python DateTime avec des exemples
- LAN Vs WAN Vs MAN: différence exacte entre les types de réseau
- Commande Cut sous Unix avec des exemples
- Syntaxe des commandes Unix Cat, options avec exemples
- Utilisation du curseur dans MongoDB avec des exemples