Comment faire une jointure complète en MySQL?

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

Exemples De Tables

supposons que nous ayons les tableaux suivants:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Jointures Internes

une jonction intérieure, comme ceci:

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

ne nous obtiendrait que des enregistrements apparaissent dans les deux tableaux, comme ceci:

1 Tim  1 Tim

les jonctions intérieures n'ont pas de direction (comme gauche ou droite) parce qu'elles sont explicitement bidirectionnelles - nous avons besoin d'une correspondance des deux côtés.

Joints Extérieurs

les jointures Externes, d'autre part, sont pour rechercher 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 manquant dossier.

LEFT JOIN et RIGHT JOIN sont un raccourci pour LEFT OUTER JOIN et RIGHT OUTER JOIN ; je vais l'utiliser leurs noms, prénoms ci-dessous afin de renforcer le concept de jointures externes vs jointures internes.

Joint Extérieur Gauche

une jointure extérieure gauche, comme ceci:

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

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

1 Tim   1    Tim
2 Marta NULL NULL

Droit Extérieur Jointure

une jointure extérieure droite, comme ceci:

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

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

1    Tim   1  Tim
NULL NULL  3  Katarina

Full Outer Join

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

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

cependant, comme Pablo Santa Cruz l'a souligné, MySQL ne soutient pas cela. Nous pouvons l'émuler en faisant L'UNION d'une gauche et d'une 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 des lignes viendront de la première requête et certains à partir de la deuxième.

il est à noter qu'un UNION dans MySQL éliminera les doublons exacts: Tim apparaîtrait dans les deux requêtes ici, mais le résultat du UNION ne le répertorie qu'une seule fois. Mon collègue gourou de base de données pense que ce comportement ne devrait pas être fiable. Donc, pour être plus explicite à ce sujet, nous pourrions ajouter une clause WHERE à la seconde 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'autre part, si vous voulu pour voir les doublons pour une raison quelconque, vous pouvez utiliser UNION ALL .

L'utilisation d'une requête union supprimera les doublons, et ceci est différent du comportement de full outer join qui ne supprime 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

résultat de l'utilisation de 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 

résultat de la requête ci-dessus qui 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 : ^{[des commentaires, avec beaucoup de remerciements!]}

Note:: cela peut être la meilleure solution, à la fois pour l'efficacité et pour produire les mêmes résultats qu'un FULL OUTER JOIN . ce billet de blog l'explique aussi bien - pour citer la méthode 2: " cela gère les lignes dupliquées correctement et n'inclut rien qu'il ne devrait pas. Il est nécessaire d'utiliser UNION ALL au lieu de UNION , ce qui éliminerait les doublons que je veux garder. Cela peut être beaucoup plus efficace sur de grands ensembles de résultats, depuis il n'y a pas besoin de trier et supprimer les doublons."

j'ai décidé d'ajouter une autre solution qui vient de full outer join visualisation et les mathématiques, il n'est pas préférable que ci-dessus, mais plus lisible:

assemblage complet extérieur signifie (t1 ∪ t2) : tout dans t1 ou t2

(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only : tous dans les deux t1 et t2 plus tous dans t1 qui ne sont pas dans t2 et plus tous dans 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]

en SQLite vous devez 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

aucune des réponses ci-dessus n'est en fait correcte, parce qu'elles ne suivent pas la sémantique lorsqu'il y a des valeurs dupliquées.

Pour une requête telle que (à partir de ce 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 que cela fonctionne avec les valeurs NULL (qui peuvent également être nécessaires), puis utiliser le NULL - opérateur de comparaison Sécuritaire, <=> plutôt que = .

Modified shA.t de la requête 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 

MySql n'a pas la syntaxe FULL-OUTER-JOIN. Vous devez imiter en faisant à la fois gauche joindre et droit joindre 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 de jointure droite. Selon MySql simplification de la jointure extérieure , la jointure droite est convertie en jointure gauche équivalente en commettant les t1 et t2 dans la clause FROM et ON dans la requête. Ainsi, L'Optimiseur de requête MySql traduit l'original requête dans le -

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 aucun mal à écrire la requête originale telle quelle, Mais dites Si vous avez des prédicats comme la clause où, qui est un avant-joindre prédicate ou an et de prédire sur la ON clause, qui est un pendant-joindre prédicate, alors vous pourriez vouloir regardez le diable, qui est dans les détails.

MySql query optimizer vérifie régulièrement les prédicats s'ils sont null-rejeté . Maintenant, si vous avez fait la bonne jointure, mais avec où prédire sur la colonne de t1, alors vous pourriez être à un risque de tomber dans un null-rejeté scénario.

par exemple, La requête suivante -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'

est traduit au suivi par le Query Optimizer -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
WHERE t1.col1 = 'someValue'

ainsi l'ordre des tableaux a changé, mais le prédicat est toujours appliqué à t1, mais t1 est maintenant dans la clause "ON". Si t1.la col1 est définie comme NOT NULL colonne, alors cette requête sera null-rejeté .

toute jointure externe (gauche, droite, complète) qui est nulle-rejetée est convertie en jointure interne par MySql.

D'où les résultats vous pourriez vous attendre pourrait être complètement différent de ce que le MySql est de retour. Vous pensez peut-être que C'est un bug avec MySql droit rejoindre, mais ce n'est pas juste. C'est comme ça que fonctionne le MySQL query-optimizer. Ainsi, le promoteur responsable doit prêter attention à ces nuances lorsqu'il construit la requête.

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);

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

qu'avez-vous dit à propos de la solution Cross join ?

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

Il est également possible, mais vous devez mentionner les mêmes noms de champs à sélectionner.

SELECT t1.name, t2.name FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.name, t2.name FROM t2
LEFT JOIN t1 ON t1.id = t2.id

Mysql en tant que tel ne supporte aucune commande nommée FULL OUTER JOIN. Les trois jointures supportées sont INNER JOIN,LEFT JOIN et RIGHT JOIN.

vous pouvez cependant mettre en œuvre la jointure externe complète en utilisant la commande UNION comme

(requête de join de gauche) UNION (requête de join de droite)

par exemple, considérez l'exemple suivant où j'ai deux élèves de tables et des notes. Pour effectuer la jointure externe complète, j'exécute le code suivant:

SELECT * FROM students  
LEFT JOIN marks   
ON students.id = marks.id  
UNION ALL  
SELECT * FROM students 
RIGHT JOIN marks  
ON students.id = marks.id;

je fixe la réponse, et les travaux incluent toutes les lignes (basé sur la réponse de Pavle Lekic)

    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    WHERE b.`key` is null
    )
    UNION ALL
    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    where  a.`key` = b.`key`
    )
    UNION ALL
    (
    SELECT b.* FROM tablea a
    right JOIN tableb b ON b.`key` = a.key
    WHERE a.`key` is null
    );

réponse:

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

peut être recréé comme suit:

 SELECT t1.*, t2.* 
 FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
 LEFT JOIN t1 ON t1.id = tmp.id
 LEFT JOIN t2 ON t2.id = tmp.id;

utilisant une UNION ou une UNION toutes les réponses ne couvrent pas le cas de bord lorsque les tables de base ont des entrées dupliquées.

explication:

il y a un cas limite qu'un syndicat ou un syndicat ne peut pas couvrir. Nous ne pouvons pas tester cela sur mysql car il ne supporte pas les

 WITH cte_t1 AS
 (
       SELECT 1 AS id1
       UNION ALL SELECT 2
       UNION ALL SELECT 5
       UNION ALL SELECT 6
       UNION ALL SELECT 6
 ),
cte_t2 AS
(
      SELECT 3 AS id2
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 6
)
SELECT  *  FROM  cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;

This gives us this answer:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

L'UNION de la solution:

SELECT  * FROM  cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION    
SELECT  * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

donne une réponse incorrecte:

 id1  id2
NULL  3
NULL  4
1  NULL
2  NULL
5  5
6  6

L'UNION de TOUS les solution:

SELECT  * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT  * FROM  cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

est également incorrect.

id1  id2
1  NULL
2  NULL
5  5
6  6
6  6
6  6
6  6
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

attendu que cette requête:

SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp 
LEFT JOIN t1 ON t1.id = tmp.id 
LEFT JOIN t2 ON t2.id = tmp.id;

donne le résultat suivant:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

L'ordre est différent, mais sinon, correspond à la réponse correcte.

la norme SQL dit full join on est inner join on lignes union all lignes de table à gauche non appariées étendues par nulls union all lignes de table à droite étendues par nulls. C'est à dire inner join on lignes union all lignes left join on , mais pas inner join on union all lignes right join on , mais pas inner join on .

Ie left join on lignes union all right join on les lignes qui ne sont pas inner join on . Ou si vous connaissez votre résultat inner join on ne peut pas avoir nul dans un particulier colonne de droite puis" right join on les lignes non dans inner join on " sont des lignes dans right join on avec la condition on prolongée par and que la colonne is null .

c'est à dire de la même façon right join on union all approprié left join on lignes.

à Partir de Quelle est la différence entre "INNER JOIN" et "OUTER JOIN"? :

(SQL Standard 2006 SQL/Foundation 7.7 syntaxe Règles 1, Règles générales 1 b, 3 C & D, 5 B.)