Question Quelle est la différence entre "INNER JOIN" et "OUTER JOIN"?


Aussi comment faire LEFT JOIN, RIGHT JOIN et FULL JOIN s'intégrer?


4014
2017-09-01 22:36


origine


Réponses:


En supposant que vous vous joignez à des colonnes sans doublons, ce qui est un cas très courant:

  • Une jointure interne de A et B donne le résultat de A se croisent B, c'est-à-dire la partie interne d'un Diagramme de Venn intersection.

  • Une jointure externe de A et B donne les résultats de A union B, c'est-à-dire les parties externes d'une union de diagramme de Venn.

Exemples

Supposons que vous avez deux tables, avec une seule colonne chacune, et les données comme suit:

A    B
-    -
1    3
2    4
3    5
4    6

Notez que (1,2) sont uniques à A, (3,4) sont communs et (5,6) sont uniques à B.

Jointure interne

Une jointure interne utilisant l'une des requêtes équivalentes donne l'intersection des deux tables, c'est-à-dire les deux lignes qu'elles ont en commun.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Jointure externe gauche

Une jointure externe gauche donnera toutes les rangées dans A, plus toutes les rangées communes dans B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Jointure externe droite

Une jointure externe droite donnera toutes les lignes dans B, plus toutes les lignes communes dans A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Jointure externe complète

Une jointure externe complète vous donnera l'union de A et B, c'est-à-dire toutes les lignes de A et toutes les lignes de B. Si quelque chose dans A n'a pas de donnée correspondante dans B, alors la partie B est nulle, et vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

5420
2017-09-01 22:59



Vous pouvez également prendre en compte le schéma suivant pour différents types de jointure.

visual explanation of joins

La source: Visual-Representation-of-SQL-Jointures expliqué en détail par C.L. Moffatt


2441
2018-05-16 23:03



je recommande L'article de Jeff. La meilleure description que j'ai jamais vue, plus il y a une visualisation, par exemple:

Jointure interne:

enter image description here

Complète externe:

enter image description here


591
2017-08-30 11:52



Les diagrammes de Venn ne le font pas vraiment pour moi.

Ils ne montrent aucune distinction entre une jointure croisée et une jointure interne, par exemple, ou plus généralement montrent une distinction entre différents types de prédicat de jointure ou fournissent un cadre pour raisonner sur la façon dont ils fonctionneront.

Il n'y a pas de substitut pour comprendre le traitement logique et il est relativement facile à saisir de toute façon.

  1. Imaginez une jointure croisée.
  2. Évaluer le on clause contre toutes les lignes de l'étape 1 en conservant celles où le prédicat évalue à true
  3. (Pour les jointures externes uniquement), ajoutez les lignes externes perdues à l'étape 2.

(NB: En pratique, l'optimiseur de requête peut trouver des moyens plus efficaces d'exécuter la requête que la description purement logique ci-dessus mais le résultat final doit être le même)

Je vais commencer avec une version animée d'un jointure externe complète. D'autres explications suivent.

enter image description here


Explication

Tables sources

enter link description here

Premier départ avec un CROSS JOIN (AKA cartésien produit). Cela n'a pas de ON clause et renvoie simplement chaque permutation de lignes à partir des deux tables.

CHOISISSEZ A.Colour, B.Colour FROM A CROSS JOIN B

enter link description here

Les jointures interne et externe ont un prédicat de clause "ON".

  • Jointure interne. Evaluez la condition dans la clause "ON" pour toutes les lignes du résultat de la jointure croisée. Si vrai renvoie la ligne jointe. Sinon, jetez-le.
  • Jointure externe gauche. Identique à la jointure interne, alors pour les lignes de la table de gauche qui ne correspondent à rien, celles-ci ont des valeurs NULL pour les colonnes de la table de droite.
  • Jointure extérieure droite. Identique à la jointure interne, alors pour les lignes de la table de droite qui ne correspondent à rien, celles-ci ont des valeurs NULL pour les colonnes de la table de gauche.
  • Jointure externe complète. Identique à la jointure interne, puis conserve les lignes non appariées à gauche comme dans la jointure externe gauche et les lignes non correspondantes à droite comme pour la jointure externe droite.

Quelques exemples

CHOISISSEZ A.Couleur, B.Couleur D'UNE PART INTERIEURE B SUR A.Couleur = B.Couleur

Ce qui précède est la jointure equi classique.

Inner Join

Version animée

enter image description here

CHOISISSEZ A.Couleur, B.Couleur d'une jointure intérieure B sur A.Couleur non incluse ('Vert', 'Bleu')

La condition de jointure interne ne doit pas nécessairement être une condition d'égalité et elle n'a pas besoin de référencer les colonnes des deux (ou même des deux) des tables. Évaluer A.Colour NOT IN ('Green','Blue') retourne sur chaque ligne de la croix.

inner 2

SELECT A.Couleur, B.Couleur d'une jointure interne B sur 1 = 1

La condition de jointure a la valeur true pour toutes les lignes du résultat de la jointure croisée, ce qui est identique à une jointure croisée. Je ne vais pas répéter l'image des 16 lignes à nouveau.

CHOISISSEZ A.Couleur, B.Couleur à partir d'un joint extérieur gauche B sur A.Couleur = B.Couleur

Les jointures externes sont logiquement évaluées de la même manière que les jointures internes sauf que si une ligne de la table de gauche (pour une jointure gauche) ne joint aucune ligne de la table de droite, elle est conservée dans le résultat avec NULL valeurs pour les colonnes de droite.

LOJ

SELECT A.Colour, B.Colour FROM A GAUCHE OUTER JOIN B ON A.Couleur = B.Couleur O WH B.Couleur est NULL

Cela restreint simplement le résultat précédent pour ne retourner que les lignes où B.Colour IS NULL. Dans ce cas particulier, ce sont les lignes qui ont été conservées car elles ne correspondaient pas dans la table de droite et la requête renvoie la seule ligne rouge non trouvée dans la table B. Ceci est connu comme une anti-jointure semi.

Il est important de sélectionner une colonne pour le IS NULL test qui n'est pas valide ou pour lequel la condition de jointure garantit que NULL les valeurs seront exclues afin que ce modèle fonctionne correctement et évite de simplement ramener des lignes qui ont un NULL valeur pour cette colonne en plus des lignes non appariées.

loj is null

CHOISISSEZ A.Couleur, B.Couleur D'UN EXTERIEUR DROIT JOIGNEZ-VOUS SUR A.Couleur = B.Couleur

Les jointures externes droites agissent de la même manière que les jointures externes gauches, sauf qu'elles conservent les rangées non correspondantes de la table droite et que null prolongent les colonnes de gauche.

ROJ

CHOISISSEZ A.Couleur, B.Couleur D'UN COMPLÈTE EXTERNE JOINTE B SUR A.Couleur = B.Couleur

Les jointures externes complètes combinent le comportement des jointures gauche et droite et conservent les lignes non correspondantes des tables gauche et droite.

FOJ

SELECT A.Couleur, B.Couleur A PARTIR D'UN JOINDRE COMPLET EXTERNE B ON 1 = 0

Aucune ligne dans la jointure croisée ne correspond à la 1=0 prédicat. Toutes les lignes des deux côtés sont conservées en utilisant les règles de jointure externe normales avec NULL dans les colonnes de la table de l'autre côté.

FOJ 2

SELECT COALESCE (A.Colour, B.Colour) COMME COULEUR A PARTIR D'UN RACCORD EXTERNE COMPLET B ON 1 = 0

Avec un mineur modifier à la question précédente, on pourrait simuler un UNION ALL des deux tables.

UNION ALL

CHOISIR A.Couleur, B.Couleur à partir d'un joint extérieur gauche B sur A.Couleur = B.Couleur O WH B.Couleur = 'Vert'

Notez que le WHERE clause (si présente) s'exécute logiquement après la jointure. Une erreur courante consiste à effectuer une jointure externe gauche, puis à inclure une clause WHERE avec une condition sur la table de droite qui finit par exclure les lignes non correspondantes. Ce qui précède finit par effectuer la jointure externe ...

LOJ

... Et puis la clause "Where" s'exécute. NULL= 'Green' n'évalue pas à true, donc la ligne conservée par la jointure externe finit par être rejetée (avec la bleue), convertissant effectivement la jointure en une jointure interne.

LOJtoInner 

Si l'intention était d'inclure uniquement les lignes de B où Couleur est verte et toutes les lignes de A quelle que soit la syntaxe correcte serait

CHOISIR A.Couleur, B.Couleur à partir d'un joint extérieur gauche B sur A.Couleur = B.Couleur et B.Couleur = 'Vert'

enter image description here

SQL Fiddle

Voir ces exemples courir en direct sur SQLFiddle.com.


526
2017-12-13 11:58



Ce qui suit a été tiré de l'article "MySQL - JOINDRE GAUCHE et RIGHT JOIN, INNER JOIN et OUTER JOIN"par Graham Ellis sur son blog Horse's Mouth.

Dans une base de données telle que MySQL, les données sont divisées en un certain nombre de tables qui sont ensuite connectées (Joined) ensemble par JOIN dans SELECT commandes pour lire les enregistrements de plusieurs tables. Lisez cet exemple pour voir comment cela fonctionne.

D'abord, quelques exemples de données:

people
    mysql> select * from people;
    +------------+--------------+------+
    | name       | phone        | pid  |
    +------------+--------------+------+
    | Mr Brown   | 01225 708225 |    1 |
    | Miss Smith | 01225 899360 |    2 |
    | Mr Pullen  | 01380 724040 |    3 |
    +------------+--------------+------+
    3 rows in set (0.00 sec)

property
    mysql> select * from property;
    +------+------+----------------------+
    | pid  | spid | selling              |
    +------+------+----------------------+
    |    1 |    1 | Old House Farm       |
    |    3 |    2 | The Willows          |
    |    3 |    3 | Tall Trees           |
    |    3 |    4 | The Melksham Florist |
    |    4 |    5 | Dun Roamin           |
    +------+------+----------------------+
    5 rows in set (0.00 sec)

RÉGULIER

Si nous faisons un JOIN régulier (avec aucun des mots-clés INNER, OUTER, LEFT ou RIGHT), alors nous obtenons tous les enregistrements qui correspondent de la manière appropriée dans les deux tables, et les enregistrements dans les deux tables entrantes ne correspondent pas. :

mysql> select name, phone, selling 
from people join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)

JOINT GAUCHE

Si nous faisons un LEFT JOIN, nous obtenons tous les enregistrements qui correspondent de la même manière et EN OUTRE nous obtenons un enregistrement supplémentaire pour chaque enregistrement non apparié dans la table de gauche de la jointure - assurant ainsi (dans cet exemple) que chaque personne obtient une mention :

   mysql> select name, phone, selling 
    from people left join property 
    on people.pid = property.pid; 
    +------------+--------------+----------------------+
    | name       | phone        | selling              |
    +------------+--------------+----------------------+
    | Mr Brown   | 01225 708225 | Old House Farm       |
    | Miss Smith | 01225 899360 | NULL <<-- unmatch    |
    | Mr Pullen  | 01380 724040 | The Willows          |
    | Mr Pullen  | 01380 724040 | Tall Trees           |
    | Mr Pullen  | 01380 724040 | The Melksham Florist |
    +------------+--------------+----------------------+
    5 rows in set (0.00 sec)

DROIT JOIN

Si nous faisons une RIGHT JOIN, nous obtenons tous les enregistrements qui correspondent et en plus un enregistrement supplémentaire pour chaque enregistrement non apparié dans la bonne table de la jointure - dans mon exemple, cela signifie que chaque propriété reçoit une mention même si nous ne le faisons pas avoir les détails du vendeur:

mysql> select name, phone, selling 
from people right join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL      | NULL         | Dun Roamin           |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)

Un INNER JOIN fait une jointure complète, tout comme le premier exemple, et le mot OUTER peut être ajouté après le mot LEFT ou RIGHT dans les deux derniers exemples - il est fourni pour la compatibilité ODBC et n'ajoute pas de fonctionnalités supplémentaires.


291
2018-02-14 05:53



Jointure interne

Récupérer uniquement les lignes correspondantes, c'est-à-dire A intersect B.

Enter image description here

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Jointure externe gauche

Sélectionnez tous les enregistrements de la première table et tous les enregistrements de la seconde table qui correspond aux clés jointes.

Enter image description here

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Jointure externe complète

Sélectionnez tous les enregistrements de la deuxième table et tous les enregistrements du premier table qui correspond aux clés jointes.

Enter image description here

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Les références


114
2018-01-27 12:16



Rejoint sont utilisés pour combiner les données de deux tables, avec pour résultat une nouvelle table temporaire. Les jointures sont effectuées sur la base de quelque chose appelé un prédicat, qui spécifie la condition à utiliser pour effectuer une jointure. La différence entre une jointure interne et une jointure externe est qu'une jointure interne renvoie uniquement les lignes qui correspondent réellement en fonction du prédicat de jointure. Considérons le tableau Employé et Lieu:

enter image description here

Jointure interne:- La jointure interne crée une nouvelle table de résultats en combinant les valeurs de colonne de deux tables (Employé et Emplacement) basé sur le prédicat de jointure. La requête compare chaque ligne de Employé avec chaque rangée de Emplacement pour trouver toutes les paires de lignes qui satisfont le join-predicate. Lorsque le prédicat de jointure est satisfait en faisant correspondre des valeurs non NULL, des valeurs de colonne pour chaque paire de rangées de Employé et Emplacement sont combinés en une ligne de résultat. Voici à quoi ressemblera le SQL pour une jointure interne:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Maintenant, voici le résultat de l'exécution de SQL: enter image description here enter image description here

Jointure externe: - Une jointure externe n'exige pas que chaque enregistrement des deux tables jointes ait un enregistrement correspondant. La table jointe conserve chaque enregistrement, même si aucun autre enregistrement correspondant n'existe. Les jointures externes se subdivisent davantage en jointures externes gauche et en jointures externes droite, en fonction des lignes de la table conservées (gauche ou droite).

Jointure externe gauche:- Le résultat d'une jointure externe gauche (ou simplement d'une jointure gauche) pour les tables Employé et Emplacement contient toujours tous les enregistrements de la table "gauche" (Employé), même si la condition de jointure ne trouve aucun enregistrement correspondant dans la table "droite" (Emplacement). Voici à quoi ressemblerait le SQL pour une jointure externe gauche, en utilisant les tables ci-dessus:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Maintenant, voici ce que le résultat de l'exécution de ce SQL ressemblerait: enter image description here enter image description here

Jointure extérieure droite: - Une jointure externe droite (ou une jointure droite) ressemble étroitement à une jointure externe gauche, sauf si le traitement des tables est inversé. Chaque rangée de la "bonne" table (Emplacement) apparaîtra dans la table jointe au moins une fois. Si aucune ligne correspondante de la table "gauche" (Employé) existe, NULL apparaîtra dans les colonnes de Employé pour les enregistrements qui ne correspondent pas dans Emplacement. Voici à quoi ressemble le SQL:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

En utilisant les tables ci-dessus, nous pouvons montrer à quoi ressemblerait l'ensemble de résultats d'une jointure externe droite:

enter image description hereenter image description here

Joints extérieurs complets: - Full Outer Join ou Full Join est de conserver les informations non concordantes en incluant des lignes non concordantes dans les résultats d'une jointure, utilisez une jointure externe complète. Il inclut toutes les lignes des deux tables, que l'autre table ait ou non une valeur correspondante. enter image description here

Source de l'image

MySQL 8.0 Reference Manual - Syntaxe de jointure


108
2017-12-18 06:54



En termes simples:

Un jointure interne récupérer les lignes correspondantes seulement.

Alors que jointure externe récupérer les lignes appariées d'une table et toutes les lignes de l'autre table .... le résultat dépend de celui que vous utilisez:

  • La gauche: Lignes correspondantes dans la table de droite et toutes les lignes de la table de gauche

  • Droite: Lignes correspondantes dans la table de gauche et toutes les lignes dans la table de droite ou

  • Plein: Toutes les lignes dans toutes les tables. Peu importe s'il y a une correspondance ou non


101
2018-01-12 11:07



Une jointure interne affiche uniquement des lignes s'il existe un enregistrement correspondant sur l'autre côté (droit) de la jointure.

Une jointure externe (à gauche) affiche des lignes pour chaque enregistrement sur le côté gauche, même s'il n'y a pas de ligne correspondante sur l'autre côté (à droite) de la jointure. S'il n'y a pas de ligne correspondante, les colonnes de l'autre côté (à droite) afficheront des valeurs NULL.


92
2017-09-01 22:38



Les jointures internes nécessitent qu'un enregistrement avec un ID associé existe dans la table jointe.

Les jointures externes renverront des enregistrements pour le côté gauche même si rien n'existe pour le côté droit.

Par exemple, vous avez une table Orders et une table OrderDetails. Ils sont liés par un "OrderID".

Ordres

  • Numéro de commande
  • CustomerName

Détails de la commande

  • OrderDetailID
  • Numéro de commande
  • ProductName
  • Qté
  • Prix

La demande

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

retournera uniquement les commandes qui ont également quelque chose dans la table OrderDetails.

Si vous le changez en OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

alors il retournera des enregistrements de la table Orders même s'ils n'ont pas d'enregistrements OrderDetails.

Vous pouvez utiliser ceci pour trouver des commandes qui n'ont pas de OrderDetails indiquant un ordre orphelin possible en ajoutant une clause where comme WHERE OrderDetails.OrderID IS NULL.


67
2017-09-01 22:47



En termes simples:

Jointure interne -> Conserver UNIQUEMENT les enregistrements communs des tables parent et enfant WHERE la clé primaire de la table parent correspond à la clé étrangère dans la table enfant.

Joint gauche ->

pseudo code

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Joindre à droite : Exactement à l'opposé de la gauche. Mettez le nom de la table dans LEFT JOIN à droite dans la jointure à droite, vous obtenez la même sortie que LEFT JOIN.

Jointure externe : Afficher tous les enregistrements dans les deux tables No matter what. Si les enregistrements de la table de gauche ne correspondent pas à la table de droite basée sur la clé primaire, Forieign, utilisez la valeur NULL comme résultat de la jointure.

Exemple :

Example

Supposons maintenant pour 2 tables

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

Ici, la table des employés est la table principale, phone_numbers_employees est la table enfant (elle contient emp_id en tant que clé étrangère qui se connecte employee.id donc sa table enfant.)

Jointures intérieures 

Prenez les dossiers de 2 tables SEULEMENT SI la clé primaire de la table des employés (son identifiant) correspond à la clé étrangère de la table enfant phone_numbers_employees (emp_id).

Donc, la requête serait:

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Ici, prenez uniquement les lignes correspondantes sur la clé primaire = clé étrangère comme expliqué ci-dessus. Les lignes non correspondantes sur la clé primaire = clé étrangère sont ignorées à la suite de la jointure.

Jointures à gauche :

La jointure gauche conserve toutes les lignes de la table de gauche, qu'il y ait ou non une ligne correspondant à la table de droite.

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Jointures externes :

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Diagramme, il ressemble à:

Diagram


58
2018-02-19 04:50