Question Comment faire un FULL OUTER JOIN dans MySQL?


Je veux faire une jointure externe complète dans MySQL. Est-ce possible? Est-ce qu'un Full Outer Join est supporté par MySQL?


485
2018-01-25 17:34


origine


Réponses:


Vous n'avez pas de JOINS COMPLETS sur MySQL, mais vous pouvez être sûr les émuler.

Pour un code SAMPLE transcrit de cette question SO tu as:

avec deux tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

La requête ci-dessus fonctionne dans des cas spéciaux où une opération FULL OUTER JOIN ne produirait aucune ligne en double. La requête ci-dessus dépend du UNION Définir l'opérateur pour supprimer les lignes en double introduites par le modèle de requête. Nous pouvons éviter d'introduire des lignes dupliquées en utilisant un anti-jointure pattern pour la deuxième requête, puis utilisez un opérateur d'ensemble UNION ALL pour combiner les deux ensembles. Dans le cas plus général, où un FULL OUTER JOIN retournerait des lignes en double, nous pouvons faire ceci:

  SELECT * FROM t1
  LEFT JOIN t2 ON t1.id = t2.id
  UNION ALL
  SELECT * FROM t1
  RIGHT JOIN t2 ON t1.id = t2.id
  WHERE t1.id IS NULL

484
2018-01-25 17:38



La réponse que Pablo Santa Cruz donné est correct; Cependant, au cas où quelqu'un serait tombé sur cette page et veut plus de clarification, voici une ventilation détaillée.

Tableaux d'exemple

Supposons que nous ayons les tableaux suivants:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Jointures intérieures

Une jointure interne, comme ceci:

SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

Ne nous obtiendrait que les enregistrements qui apparaissent dans les deux tableaux, comme ceci:

1 Tim  1 Tim

Les jointures internes n'ont pas de sens (comme à gauche ou à droite) car elles sont explicitement bidirectionnelles - nous avons besoin d'une correspondance des deux côtés.

Jointures extérieures

D'un autre côté, les jointures externes servent à trouver des enregistrements qui n'ont pas de correspondance dans l'autre table. En tant que tel, vous devez spécifier quel côté de la jointure est autorisé à avoir un enregistrement manquant.

LEFT JOIN et RIGHT JOIN sont sténographie pour LEFT OUTER JOIN et RIGHT OUTER JOIN; J'utiliserai leurs noms complets ci-dessous pour renforcer le concept de jointures externes et de jointures internes.

Jointure externe gauche

Une jointure externe gauche, comme ceci:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

... nous obtiendrait tous les enregistrements de la table de gauche, qu'ils aient ou non une correspondance dans la bonne table, comme ceci:

1 Tim   1    Tim
2 Marta NULL NULL

Jointure externe droite

Une jointure externe droite, comme ceci:

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

... nous obtiendrait tous les enregistrements de la bonne table, qu'ils aient ou non une correspondance dans la table de gauche, comme ceci:

1    Tim   1  Tim
NULL NULL  3  Katarina

Jointure externe complète

Une jointure externe complète nous donnerait tous les enregistrements des deux tables, qu'ils aient ou non une correspondance dans l'autre table, avec des valeurs NULL des deux côtés où il n'y a pas de correspondance. Le résultat ressemblerait à ceci:

1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina

Cependant, comme l'a souligné Pablo Santa Cruz, MySQL ne supporte pas cela. Nous pouvons l'imiter en faisant une UNION d'une jointure gauche et d'une jointure droite, comme ceci:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

Vous pouvez penser à un UNION comme signifiant "exécuter ces deux requêtes, puis empiler les résultats les uns sur les autres"; certaines lignes proviendront de la première requête et d'autres de la seconde.

Il convient de noter qu'un UNION dans MySQL va éliminer les doublons exacts: Tim apparaîtrait dans les deux requêtes ici, mais le résultat de la UNION seulement le liste une fois. Mon collègue gourou de base de données estime que ce comportement ne devrait pas être invoqué. Donc, pour être plus explicite à ce sujet, nous pourrions ajouter un WHEREclause à la deuxième requête:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;

D'un autre côté, si vous voulait pour voir des doublons pour une raison quelconque, vous pouvez utiliser UNION ALL.


296
2018-02-09 16:23



Utilisant un union requête va supprimer les doublons, et cela est différent du comportement de full outer join cela n'enlève jamais aucun doublon:

[Table: t1]                            [Table: t2]
value                                  value
-------                                -------
1                                      1
2                                      2
4                                      2
4                                      5

C'est le résultat attendu de full outer join:

value | value
------+-------
1     | 1
2     | 2
2     | 2
Null  | 5
4     | Null
4     | Null

C'est le résultat de l'utilisation left et right Join avec union:

value | value
------+-------
Null  | 5 
1     | 1
2     | 2
4     | Null

[SQL Fiddle]

Ma requête suggérée est:

select 
    t1.value, t2.value
from t1 
left outer join t2  
  on t1.value = t2.value
union all      -- Using `union all` instead of `union`
select 
    t1.value, t2.value
from t2 
left outer join t1 
  on t1.value = t2.value
where 
    t1.value IS NULL 

Le résultat de la requête ci-dessus est le même que le résultat attendu:

value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 5

[SQL Fiddle]


@Steve Chambers: [De commentaires, avec beaucoup de mercis!]
Remarque: Cela peut être la meilleure solution, à la fois pour l'efficacité et pour générer les mêmes résultats qu'un FULL OUTER JOIN. Ce blog explique également bien - pour citer de la méthode 2: "Cela gère correctement les lignes en double et n'inclut rien de ce qu'il ne devrait pas contenir. Il est nécessaire d'utiliser UNION ALL au lieu de plain UNION, ce qui éliminerait les doublons que je veux garder. Cela peut être significativement plus efficace sur les grands ensembles de résultats, car il n'est pas nécessaire de trier et de supprimer les doublons. "


J'ai décidé d'ajouter une autre solution qui vient de full outer join la visualisation et les maths, ce n'est pas mieux que ci-dessus mais plus lisible:

La jonction extérieure complète signifie (t1 ∪ t2): tout en t1 ou en t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: tous dans les deux t1 et t2 plus tout en t1 qui ne sont pas dans t2 et plus tout en t2 qui ne sont pas dans t1:

-- (t1 ∩ t2): all in both t1 and t2
select t1.value, t2.value
from t1 join t2 on t1.value = t2.value    
union all  -- And plus 
-- all in t1 that not exists in t2
select t1.value, null
from t1
where not exists( select 1 from t2 where t2.value = t1.value)    
union all  -- and plus
-- all in t2 that not exists in t1
select null, t2.value
from t2
where not exists( select 1 from t1 where t2.value = t1.value)

[SQL Fiddle]


25
2018-05-28 05:01



Dans SQLite, vous devriez faire ceci:

SELECT * 
FROM leftTable lt 
LEFT JOIN rightTable rt ON lt.id = rt.lrid 
UNION
SELECT lt.*, rl.*  -- To match column set
FROM rightTable rt 
LEFT JOIN  leftTable lt ON lt.id = rt.lrid

5
2018-01-14 13:50



Aucune des réponses ci-dessus n'est réellement correcte, car elles ne suivent pas la sémantique lorsqu'il y a des valeurs dupliquées.

Pour une requête telle que (de ceci dupliquer):

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;

L'équivalent correct est:

SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION  -- This is intentionally UNION to remove duplicates
      SELECT name FROM t2
     ) n LEFT JOIN
     t1
     ON t1.name = n.name LEFT JOIN
     t2
     ON t2.name = n.name;

Si vous avez besoin de cela pour travailler avec NULL valeurs (qui peuvent également être nécessaires), puis utilisez NULL-opérateur de comparaison sécurisé <=> plutôt que =.


4
2018-01-14 13:27



Requête modifiée de shA.t pour plus de clarté:

-- t1 left join t2
SELECT t1.value, t2.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value   

    UNION ALL -- include duplicates

-- t1 right exclude join t2 (records found only in t2)
SELECT t1.value, t2.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE t2.value IS NULL 

3
2018-03-11 08:47



MySql n'a pas de syntaxe FULL-OUTER-JOIN. Vous devez émuler en faisant à la fois LEFT JOIN et RIGHT JOIN comme suit -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id  
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

Mais MySql n'a pas non plus de syntaxe RIGHT JOIN. Selon MySql's simplification de la jointure externe, la jointure droite est convertie en jointure gauche équivalente en commutant les t1 et t2 dans le FROM et ON clause dans la requête. Ainsi, MySQL Query Optimizer traduit la requête d'origine en:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id  
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id

Maintenant, il n'y a pas de mal à écrire la requête originale telle quelle, mais dites si vous avez des prédicats comme la clause WHERE, qui est un avant-rejoindre prédicat ou un prédicat ET sur le ON clause, qui est un pendant la jointure prédicat, alors vous pourriez vouloir regarder le diable; qui est dans les détails.

L’optimiseur de requêtes MySQL vérifie régulièrement les prédicats s’ils sont null-rejeté. Null-Rejected Definition and Examples Maintenant, si vous avez fait le RIGHT JOIN, mais avec le prédicat WHERE sur la colonne de t1, alors vous pourriez courir le risque de tomber dans un null-rejeté scénario.

Par exemple, La requête suivante -     SELECT * FROM t1     LEFT JOIN t2 SUR t1.id = t2.id     O t t1.col1 = 'someValue'     SYNDICAT     SELECT * FROM t1     DROIT JOIGNEZ t2 SUR t1.id = t2.id     O t t1.col1 = 'someValue' est traduit à la suivante par l'optimiseur de requête     SELECT * FROM t1     LEFT JOIN t2 SUR t1.id = t2.id     O t t1.col1 = 'someValue'     SYNDICAT     SELECT * FROM t2     LEFT JOIN t1 ON t2.id = t1.id     O t t1.col1 = 'someValue' Donc l'ordre des tables a changé, mais le prédicat est toujours appliqué à t1, mais t1 est maintenant dans la clause 'ON'. Si t1.col1 est défini comme NOT NULLcolonne, alors cette requête sera null-rejeté.

Toute jointure externe (gauche, droite, complète) qui est null-rejeté est converti en une jointure interne par MySql.

Ainsi, les résultats que vous attendez peuvent être complètement différents de ce que renvoie MySql. Vous pourriez penser que c'est un bug avec RIGHT JOIN de MySql, mais ce n'est pas correct. C'est juste comment fonctionne l'optimiseur de requêtes MySQL. Ainsi, le développeur en charge doit prêter attention à ces nuances lorsqu'il construit la requête.


1
2017-10-12 19:22



Vous pouvez faire ce qui suit:

(SELECT 
    *
FROM
    table1 t1
        LEFT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t2.id IS NULL)
UNION ALL
 (SELECT 
    *
FROM
    table1 t1
        RIGHT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t1.id IS NULL);

1
2018-06-05 07:54



SELECT
    a.name,
    b.title
FROM
    author AS a
LEFT JOIN
    book AS b
    ON a.id = b.author_id
UNION
SELECT
    a.name,
    b.title
FROM
    author AS a
RIGHT JOIN
    book AS b
    ON a.id = b.author_id

0
2017-10-24 11:10



Qu'as-tu dit à propos de Croix rejoindre Solution?

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 
ON 1=1;

0
2018-06-13 19:53