left - outer join sql server




Quelle est la différence entre “INNER JOIN” et “OUTER JOIN”? (20)

Quelle est la différence entre “INNER JOIN” et “OUTER JOIN”?

Ils sont les opérateurs existentiels les plus couramment utilisés dans SQL, où INNER JOINest utilisé pour «existe» et LEFT OUTER JOINest utilisé pour «n'existe pas».

Considérez ces requêtes:

users who have posted and have votes
users who have posted but have no badges

Les personnes qui recherchent des solutions basées sur les ensembles (terme industriel) reconnaissent les requêtes respectives comme étant:

users who have posted INTERSECT users who have votes
users who have posted MINUS users who have badges

Traduction de ceux-ci en SQL standard:

SELECT UserId FROM Posts
INTERSECT 
SELECT UserId FROM Votes;

SELECT UserId FROM Posts
EXCEPT 
SELECT UserId FROM Badges;

D'autres penseront dans le même sens d'inclusion d'ensemble:

users who have posted and IN the set of users who have votes
users who have posted and NOT IN the set of users who have badges

Traduction de ceux-ci en SQL standard:

SELECT UserId 
  FROM Posts
 WHERE UserId IN ( SELECT UserId FROM Votes );

SELECT UserId 
  FROM Posts
 WHERE UserId NOT IN ( SELECT UserId FROM Badges );

Certains penseront en termes d'existence dans des ensembles, par exemple

users who have posted and EXIST in the set of users who have votes
users who have posted and do NOT EXIST in the set of users who have badges

En les traduisant en SQL standard (notez qu'il faut maintenant utiliser les variables de plage, c.-à-d p. v, b):

SELECT p.UserId 
  FROM Posts p
 WHERE EXISTS ( SELECT *
                  FROM Votes v
                 WHERE v.UserId = p.UserId );

SELECT p.UserId 
  FROM Posts p
 WHERE NOT EXISTS ( SELECT *
                      FROM Badges b
                     WHERE b.UserId = p.UserId );

Cependant, j'ai constaté que l'approche "standard de l'industrie" consiste à utiliser exclusivement des jointures. Je ne sais pas ce que nous pensons ici ( loi de l'instrument ? Optimisation prématurée ?), Je vais donc passer à la syntaxe

SELECT p.UserId 
  FROM Posts p
       INNER JOIN Votes v ON v.UserId = p.UserId;

SELECT p.UserId 
  FROM Posts p
       LEFT JOIN Badges b ON b.UserId = p.UserId
 WHERE b.UserId IS NULL;

Choses à noter:

  • La seule projection est de , Usersmais nous avons encore besoin toutes les variables de gamme ( p, v, b) pour les conditions de recherche.
  • La UserId IS NULLcondition de recherche "appartient" à la OUTER JOINmais est déconnectée dans la requête.
  • LEFTest la norme de l'industrie: les professionnels vont réécrire une requête pour éviter de l'utiliser RIGHT!
  • Le OUTERmot clé de LEFT OUTER JOINest omis.

Remarques de clôture:

Parfois, les jointures sont utilisées dans les requêtes uniquement pour déterminer si des valeurs existent ou non dans un autre ensemble. Apprenez à bien regarder les attributs projetés (les colonnes de la SELECTclause): s’il n’y en a pas dans la table jointe, ils sont simplement utilisés comme opérateurs existentiels. De plus, pour une jointure externe, recherchez des instances de <key_column> IS NULLdans la WHEREclause.

De plus, comment se LEFT JOIN , RIGHT JOIN et FULL JOIN ?


Jointure interne

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

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 correspondant aux clés jointes.

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

Jointure complète

Sélectionnez tous les enregistrements de la deuxième table et tous les enregistrements de la première table correspondant aux clés jointes.

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

Références


En mots simples:

Une jointure interne récupère uniquement les lignes correspondantes.

Alors qu'une jointure externe récupère les lignes correspondantes d'une table et toutes les lignes d'une autre table ... le résultat dépend de celle que vous utilisez:

  • Gauche : lignes correspondantes dans le tableau de droite et toutes les lignes du tableau de gauche

  • Droite : lignes correspondantes dans le tableau de gauche et toutes les lignes du tableau de droite ou

  • Complet : toutes les lignes de toutes les tables. Peu importe s'il y a une correspondance ou non


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

  • Une jointure interne de A et B donne le résultat de l'intersection A de B, c'est-à-dire la partie interne d'une intersection de diagramme de Venn .

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

Exemples

Supposons que vous ayez deux tables, chacune avec une seule colonne, et les données suivantes:

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 des deux lignes communes.

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 lignes en A, plus toutes les lignes communes en 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 en B, plus toutes les lignes communes en 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 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

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

Par exemple, ils ne montrent aucune distinction entre une jointure croisée et une jointure interne, ni plus généralement aucune distinction entre différents types de prédicats de jointure ni ne fournissent un cadre pour raisonner sur la façon dont ils vont fonctionner.

Il n'y a pas de substitut à la compréhension du traitement logique et il est relativement simple à comprendre de toute façon.

  1. Imaginez une croix rejoindre.
  2. Évaluez la clause on par rapport à toutes les lignes de l'étape 1 en conservant celles où le prédicat est évalué à true
  3. (Pour les jointures externes uniquement) rajoutez dans toutes les lignes extérieures perdues à l'étape 2.

(NB: en pratique, l'optimiseur de requêtes trouvera peut-être des moyens plus efficaces d'exécuter la requête que la description purement logique ci-dessus, mais le résultat final devra être identique.)

Je commencerai par une version animée d'une jointure externe complète . Une explication supplémentaire suit.

Explication

Tables source

Commencez d'abord par un CROSS JOIN (produit cartésien AKA). Cela n'a pas de clause ON et renvoie simplement toutes les combinaisons de lignes des deux tables.

SÉLECTIONNER A.Couleur, B.Couleur DE UNE CROIX JOIN B

Les jointures internes et externes ont un prédicat de clause "ON".

  • Jointure interne. Evaluez la condition de la clause "ON" pour toutes les lignes du résultat de la jointure. Si true, retourne la ligne jointe. Sinon, jetez-le.
  • Jointure externe gauche. Identique à la jointure interne, puis pour toutes les lignes de la table de gauche qui ne correspondent à aucune sortie, ces valeurs avec des valeurs NULL pour les colonnes de la table de droite.
  • Jointure externe droite. Identique à la jointure interne, puis pour toutes les lignes de la table de droite qui ne correspondent à rien, les sorties avec des valeurs NULL pour les colonnes de la table de gauche.
  • Jointure extérieure complète. Identique à la jointure interne, puis conservez les lignes non appariées de gauche comme dans la jointure externe gauche et les lignes non appariées de droite conformément à la jointure externe droite.

Quelques exemples

SÉLECTIONNER A.Couleur, B.Couleur D'UN INTÉRIEUR JOIN B SUR A.Couleur = B.Couleur

Le ci-dessus est l'équi classique rejoindre.

Version animée

SÉLECTIONNER A.Couleur, B.Couleur D'UN INTÉRIEUR JOIN B ON A.Couleur Non IN ('Vert', 'Bleu')

La condition de jointure interne ne doit pas nécessairement être une condition d'égalité et il n'est pas nécessaire qu'elle référence les colonnes des deux (ou même de l'un ou l'autre) des tables. L'évaluation de A.Colour NOT IN ('Green','Blue') sur chaque ligne de la jointure croisée est renvoyée.

SÉLECTIONNER A.Couleur, B.Couleur DE UN INTÉRIEUR JOIN B ON 1 = 1

La condition de jointure est évaluée comme étant vraie pour toutes les lignes du résultat de la jointure croisée, elle est donc identique à une jointure croisée. Je ne répéterai plus l'image des 16 rangées.

SÉLECTIONNER A.Couleur, B.Couleur DE UN EXTERIEUR GAUCHE, JOIN B SUR A.Couleur = B.Couleur

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

SÉLECTIONNER A.Couleur, B.Couleur DE GAUCHE EXTERNE JOINDRE B SUR A.Couleur = B.Couleur O B. B.Couleur est NULL

Cela limite simplement le résultat précédent à ne renvoyer 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 qui ne correspond pas dans la table B Ceci est connu comme un anti semi rejoindre.

Il est important de sélectionner une colonne pour le test IS NULL qui n'est pas nullable ou pour laquelle la condition de jointure garantit que toutes les valeurs NULL seront exclues pour que ce modèle fonctionne correctement et pour éviter de simplement renvoyer des lignes ayant un Valeur NULL pour cette colonne en plus des lignes sans correspondance.

SÉLECTIONNER A.Couleur, B.Couleur DE DROITE EXTÉRIEURE JOIN B SUR A.Couleur = B.Couleur

Les jointures externes droites agissent de la même manière que les jointures externes gauches, à la différence qu’elles préservent les lignes non correspondantes de la table de droite et étendent nullement les colonnes de gauche.

SÉLECTIONNER A.Couleur, B.Couleur À PARTIR D'UN FULL OUTER REJOINDRE B SUR A.Couleur = B.Couleur

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

SÉLECTIONNER A.Couleur, B.Couleur À PARTIR D'UN FULL OUTER JOIN B ON 1 = 0

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

SÉLECTIONNER LA COALESCE (Couleur A., ​​Couleur B.) COMME UNE COULEUR À PARTIR D'UNE FULL OUTER JOIN A B ON 1 = 0

Avec une modification mineure à la requête précédente, on pourrait simuler un UNION ALL des deux tables.

SÉLECTIONNER A.Couleur, B.Couleur DE GAUCHE EXTERNE JOINDRE B SUR A.Couleur = B.Couleur WHERE B.Couleur = 'Vert'

Notez que la clause WHERE (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 exclut les lignes non correspondantes. Ce qui précède finit par effectuer la jointure externe ...

... Et ensuite la clause "Où" est exécutée. NULL= 'Green' n'est pas évalué à true. La ligne conservée par la jointure externe est donc supprimée (avec la ligne bleue), ce qui reconvertit la jointure en une jointure interne.

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

SÉLECTIONNER A.Couleur, B.Couleur DE GAUCHE EXTERNE JOINDRE B SUR A.Couleur = B.Couleur ET B.Couleur = 'Vert'

Violon SQL

Voir ces exemples exécutés en direct sur SQLFiddle.com .


Les jointures internes nécessitent la présence d'un enregistrement avec un ID associé 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 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

renverra 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

il retournera ensuite les enregistrements de la table Orders même s'ils ne possèdent pas d'enregistrements OrderDetails.

Vous pouvez utiliser cette option pour rechercher des commandes ne contenant aucun ordre OrderDetails indiquant une éventuelle commande orpheline en ajoutant une clause where telle que WHERE OrderDetails.OrderID IS NULL .



  • INNER JOINjoint le plus typique pour deux ou plusieurs tables. Il retourne la correspondance des données à la fois sur la table ON primarykey et sur la relation forignkey.
  • OUTER JOINest identique à INNER JOIN, mais il inclut également des NULLdonnées sur ResultSet.
    • LEFT JOIN= INNER JOIN+ Données non appariées de la table de gauche avec une correspondance nulle sur la table de droite.
    • RIGHT JOIN= INNER JOIN+ Données non appariées de la table de droite avec une correspondance nulle dans la table de gauche.
    • FULL JOIN= INNER JOIN+ Données non appariées sur les tables droite et gauche avec des correspondances nulles.
  • La jointure automatique n'est pas un mot clé en SQL, lorsqu'une table fait référence à des données en tant que telles. En utilisant INNER JOINet OUTER JOINnous pouvons écrire des requêtes auto-jointes.

Par exemple:

SELECT * 
FROM   tablea a 
       INNER JOIN tableb b 
               ON a.primary_key = b.foreign_key 
       INNER JOIN tablec c 
               ON b.primary_key = c.foreign_key 

INNER JOINnécessite au moins une correspondance dans la comparaison des deux tableaux. Par exemple, les tableaux A et B qui impliquent A ٨ B (intersection A).

LEFT OUTER JOINet LEFT JOINsont les mêmes. Il donne tous les enregistrements correspondants dans les deux tables et toutes les possibilités de la table de gauche.

De même RIGHT OUTER JOINet RIGHT JOINsont les mêmes. Il donne tous les enregistrements correspondants dans les deux tables et toutes les possibilités de la bonne table.

FULL JOINest la combinaison de LEFT OUTER JOINet RIGHT OUTER JOINsans duplication.


Définitions les plus simples

Jointure interne: Renvoie les enregistrements correspondants des deux tables.

Jointure externe complète: Retourne les enregistrements correspondants et non appariés des deux tables avec la valeur null pour les enregistrements non appariés des deux tableaux .

Jointure externe gauche: Renvoie les enregistrements correspondants et non identiques uniquement à partir de la table du côté gauche .

Jointure externe droite: Renvoie les enregistrements correspondants et non identiques uniquement à partir de la table du côté droit .

En bref

Matched + Left Unmatched + Right Unmatched = Jointure externe complète

Matched + Left Unmatched = Jointure externe gauche

Matched + Right Unmatched = Jointure externe droite

Matched = Inner Join


Jointure interne.

Une jointure combine les lignes de deux tables. Une jointure interne tente de faire correspondre les deux tables en fonction des critères spécifiés dans la requête et renvoie uniquement les lignes qui correspondent. Si une ligne de la première table de la jointure correspond à deux lignes de la seconde table, deux lignes seront renvoyées dans les résultats. Si une ligne de la première table ne correspond pas à une ligne de la seconde, elle n'est pas renvoyée. De même, s'il existe une ligne dans la deuxième table qui ne correspond pas à une ligne dans la première, elle n'est pas renvoyée.

Jointure externe.

Une jointure gauche tente de trouver une correspondance entre les lignes de la première table et les lignes de la seconde table. S'il ne peut pas trouver de correspondance, il retournera les colonnes de la première table et laissera les colonnes de la deuxième table vides (null).


Jointure interne Une jointure interne met l'accent sur les points communs entre deux tables. Lors de l'utilisation d'une jointure interne, il doit exister au moins des données correspondantes entre deux (ou plusieurs) tables comparées. Une jointure interne recherche dans les tables des données correspondantes ou qui se chevauchent. Une fois trouvée, la jointure interne combine et renvoie les informations dans une nouvelle table.

Jointure externe Une jointure externe renvoie un ensemble d'enregistrements (ou de lignes) comprenant ce que la jointure interne renverrait, mais également d'autres lignes pour lesquelles aucune correspondance correspondante n'est trouvée dans l'autre table.

Il existe trois types de jointures externes:

Jointure externe gauche (ou jointure gauche) Jointure externe droite (ou jointure droite) Jointure externe complète (ou jointure complète) Chacune de ces jointures externes fait référence à la partie des données comparée, combinée et renvoyée. Parfois, des valeurs NULL seront produites dans ce processus car certaines données sont partagées alors que d'autres ne le sont pas.


Ayant critiqué le très apprécié diagramme de Venn en rouge-ombré, je pensais qu'il était juste de poster ma propre tentative.

Bien que la réponse de @Martin Smith soit de loin la meilleure de ce groupe, elle ne montre que la colonne clé de chaque tableau, alors que dans l’idéal, les colonnes non clés devraient également être affichées.

Au mieux de ma capacité dans la demi-heure autorisée, je ne pense toujours pas que cela montre de manière adéquate que les valeurs nulles sont présentes en raison de l'absence de valeurs clés dans TableBou qu'il OUTER JOINs'agit en réalité d'une union plutôt que d'une union:


En mots simples:

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

Rejoindre à 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.
    }
  }

Droit rejoindre : Exactement opposé de gauche rejoindre. Mettez le nom de la table dans LEFT JOIN à droite dans la jointure à droite, vous obtenez le même résultat 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 en fonction de la clé primaire, Forieign, utilisez la valeur NULL comme résultat de la jointure.

Exemple :

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_idla clé étrangère qui connecte employee.idla table enfant).

Jointure intérieure

Prenez les enregistrements de 2 tables UNIQUEMENT SI la clé primaire de la table d'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;

Prenez ici 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 en raison de la jointure.

Jointure à 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;

Jointure externe :

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;

Schématiquement, cela ressemble à:


Je ne vois pas beaucoup de détails sur les performances et l'optimiseur dans les autres réponses.

Parfois, il est bon de savoir qu’il n’ya INNER JOINqu’associatif, ce qui signifie que l’optimiseur a le plus de possibilités pour jouer avec. Il peut réorganiser l'ordre de jointure pour le rendre plus rapide en conservant le même résultat. L'optimiseur peut utiliser la plupart des modes de jointure.

En règle générale, il est recommandé d’essayer d’utiliser INNER JOINdes types de jointures différents. (Bien sûr, si cela est possible en considérant l'ensemble de résultats attendus.)

Il existe quelques bons exemples et explications ici concernant cet étrange comportement associatif:


La différence entre jointure interne et jointure externe est la suivante:

  1. La jointure interne est une jointure combinant des tables basées sur des nuplets correspondants, alors que la jointure externe est une jointure combinant une table basée sur des tuples appariés et non appariés.
  2. La jointure interne fusionne les lignes correspondantes de deux tables dans lesquelles les lignes non appariées sont omises, tandis que la jointure externe fusionne les lignes de deux tables et les lignes non appariées se remplissent avec une valeur null.
  3. La jointure interne est comme une opération d'intersection, alors que la jointure externe est comme une opération d'union.
  4. La jointure interne est de deux types, alors que la jointure externe est de trois types.
  5. La jointure interne est plus lente, alors que la jointure externe est plus rapide que la jointure interne.

La différence réside dans la façon dont les tables sont jointes s'il n'y a pas d'enregistrements communs.

  • JOINest la même chose INNER JOINet signifie afficher uniquement les enregistrements communs aux deux tables. Le fait que les enregistrements soient communs est déterminé par les champs de la clause join. Par exemple:

    FROM t1
    JOIN t2 on t1.ID = t2.ID
    

    signifie afficher uniquement les enregistrements contenant la même IDvaleur dans les deux tables.

  • LEFT JOINest identique à LEFT OUTER JOINet signifie afficher tous les enregistrements de la table de gauche (c'est-à-dire celle qui précède dans l'instruction SQL) indépendamment de l'existence des enregistrements correspondants dans la table de droite.

  • RIGHT JOINest identique à RIGHT OUTER JOINet signifie en face de LEFT JOIN, c'est-à-dire qu'il affiche tous les enregistrements de la seconde table (droite) et uniquement les enregistrements correspondants de la première table (gauche).

Source: Quelle est la différence entre LEFT, RIGHT, INNER, OUTER, JOIN?


La réponse est dans le sens de chacun, donc dans les résultats.

Remarque :
En SQLiteil n'y a pas RIGHT OUTER JOINou FULL OUTER JOIN.
Et aussi dans MySQLil n'y a pas FULL OUTER JOIN.

Ma réponse est basée sur la note ci-dessus .

Quand vous avez deux tables comme celles-ci:

--[table1]               --[table2]
id | name                id | name
---+-------              ---+-------
1  | a1                  1  | a2
2  | b1                  3  | b2

CROSS JOIN / OUTER JOIN:
Vous pouvez avoir toutes ces données de tables avec CROSS JOINou juste ,comme ceci:

SELECT * FROM table1, table2
--[OR]
SELECT * FROM table1 CROSS JOIN table2

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2
1  | a1   | 3  | b2
2  | b1   | 1  | a2
2  | b1   | 3  | b2

INNER JOIN:
Lorsque vous souhaitez ajouter un filtre aux résultats ci-dessus en fonction d'une relation, table1.id = table2.idvous pouvez utiliser INNER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
--[OR]
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2

LEFT [OUTER] JOIN:
Lorsque vous voulez avoir toutes les lignes d'une des tables dans le résultat ci-dessus - avec la même relation - vous pouvez utiliser LEFT JOIN:
(Pour RIGHT JOIN, il suffit de changer le lieu des tables)

SELECT * FROM table1, table2 WHERE table1.id = table2.id 
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
--[OR]
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id   | name 
---+------+------+------
1  | a1   | 1    | a2
2  | b1   | Null | Null

FULL OUTER JOIN:
Lorsque vous souhaitez également que toutes les lignes de l'autre table figurent dans vos résultats, vous pouvez utiliser FULL OUTER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
UNION ALL
SELECT Null, Null, * FROM table2 WHERE Not table2.id In (SELECT id FROM table1)
--[OR] (recommended for SQLite)
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION ALL
SELECT * FROM table2 LEFT JOIN table1 ON table2.id = table1.id
WHERE table1.id IS NULL
--[OR]
SELECT * FROM table1 FULL OUTER JOIN table2 On table1.id = table2.id

--[Results:]
id   | name | id   | name 
-----+------+------+------
1    | a1   | 1    | a2
2    | b1   | Null | Null
Null | Null | 3    | b2

Eh bien, en fonction de vos besoins, vous choisissez chacun qui répond à vos besoins;).


Les algorithmes précis pour INNER JOIN, LEFT/RIGHT OUTER JOINsont les suivants:

  1. Prenez chaque ligne de la première table: a
  2. Considérez toutes les lignes de la deuxième table à côté: (a, b[i])
  3. Evaluez la ON ...clause par rapport à chaque paire:ON( a, b[i] ) = true/false?
    • Lorsque la condition est évaluée à true, renvoyez cette ligne combinée (a, b[i]).
    • Lorsque vous atteignez la fin de la deuxième table sans aucune correspondance, il s'agit Outer Joinalors d'une paire (virtuelle) renvoyée en utilisant Nullpour toutes les colonnes de l'autre table: (a, Null)pour la jointure externe LEFT ou (Null, b)pour la jointure externe RIGHT. Cela permet de s'assurer que toutes les lignes de la première table existent dans les résultats finaux.

Remarque: la condition spécifiée dans la ONclause peut être n'importe quoi, il n'est pas nécessaire d'utiliser des clés primaires (et vous n'avez pas besoin de toujours faire référence aux colonnes des deux tables)! Par exemple:

  • ... ON T1.title = T2.title AND T1.version < T2.version(=> voir cet article à titre d'exemple: sélectionnez uniquement les lignes avec une valeur maximale sur une colonne )
  • ... ON T1.y IS NULL
  • ... ON 1 = 0 (juste comme échantillon)

Remarque: Jointure gauche = Jointure externe gauche, Jointure droite = Jointure externe droite.


Vous utilisez INNER JOINpour renvoyer toutes les lignes des deux tables contenant une correspondance. C'est-à-dire que dans la table résultante, toutes les lignes et toutes les colonnes auront des valeurs.

Dans OUTER JOINla table résultante peut avoir des colonnes vides. La jointure externe peut être soit LEFTou RIGHT.

LEFT OUTER JOIN renvoie toutes les lignes de la première table, même s'il n'y a pas de correspondance dans la seconde table.

RIGHT OUTER JOIN renvoie toutes les lignes de la deuxième table, même s'il n'y a pas de correspondance dans la première table.





outer-join