Hoe doe je een VOLLEDIGE OUTER JOIN in MySQL?

Ik wil een Full Outer Join doen in MySQL. Is dit mogelijk? Wordt een Full Outer Join ondersteund door MySQL?

Antwoord 1, autoriteit 100%

Je hebt geen FULL JOINS op MySQL, maar je kunt er zeker van zijn emuleer ze.

Voor een code SAMPLE getranscribeerd van deze SO-vraagdie je hebt:

met twee tabellen 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

De bovenstaande query werkt voor speciale gevallen waarin een FULL OUTER JOIN-bewerking geen dubbele rijen zou opleveren. De bovenstaande query is afhankelijk van de set-operator UNIONom dubbele rijen te verwijderen die door het querypatroon zijn geïntroduceerd. We kunnen voorkomen dat dubbele rijen worden geïntroduceerd door een anti-joinpatroon te gebruiken voor de tweede query, en vervolgens een UNION ALL set-operator te gebruiken om de twee sets te combineren. In het meer algemene geval, waar een FULL OUTER JOIN dubbele rijen zou retourneren, kunnen we dit doen:

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

Antwoord 2, autoriteit 51%

Het antwoord dat Pablo Santa Cruzgaf, is correct; echter, voor het geval iemand op deze pagina is gestuit en meer uitleg wil, hier is een gedetailleerd overzicht.

Voorbeeldtabellen

Stel dat we de volgende tabellen hebben:

-- t1
id  name
1   Tim
2   Marta
-- t2
id  name
1   Tim
3   Katarina

Innerlijke samenvoegingen

Een inner join, zoals deze:

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

Hiermee krijgen we alleen records die in beide tabellen voorkomen:

1 Tim  1 Tim

Inner joins hebben geen richting (zoals links of rechts) omdat ze expliciet bidirectioneel zijn – we hebben een match aan beide kanten nodig.

Outer Joins

Outer joins zijn daarentegen bedoeld om records te vinden die mogelijk niet overeenkomen in de andere tabel. Daarom moet u aan welke kantvan de join een ontbrekend record mogen hebben.

LEFT JOINen right Joinzijn afkortingen voor LEFT OUTER JOINen RIGHT OUTER JOIN; Ik zal hun volledige namen hieronder gebruiken om het concept van outer joins versus inner joins te versterken.

Linker buitenste join

Een linker outer join, zoals deze:

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

…zou ons alle records van de linkertabel bezorgen, ongeacht of ze een overeenkomst hebben in de rechtertabel, als volgt:

1 Tim   1    Tim
2 Marta NULL NULL

Rechts buitenste join

Een rechter outer join, zoals deze:

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

…zou ons alle records van de rechtertabel bezorgen, ongeacht of ze een overeenkomst hebben in de linkertabel, als volgt:

1    Tim   1  Tim
NULL NULL  3  Katarina

Volledige externe deelname

Een volledige outer join zou ons alle records van beide tabellen geven, ongeacht of ze een overeenkomst hebben in de andere tabel, met NULL’s aan beide kanten waar er geen overeenkomst is. Het resultaat ziet er als volgt uit:

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

Maar, zoals Pablo Santa Cruz opmerkte, MySQL ondersteunt dit niet. We kunnen het emuleren door een UNION te doen van een left join en een right join, zoals dit:

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

Je kunt een UNIONzien als “voer beide zoekopdrachten uit en stapel de resultaten op elkaar”; sommige rijen komen uit de eerste zoekopdracht en sommige uit de tweede.

Opgemerkt moet worden dat een UNIONin MySQL exacte duplicaten zal elimineren: Tim zou in beide zoekopdrachten hier verschijnen, maar het resultaat van de UNIONvermeldt hem alleen een keer. Mijn collega database-goeroe vindt dat men niet op dit gedrag moet vertrouwen. Dus om er explicieter over te zijn, zouden we een WHERE-clausule kunnen toevoegen aan de tweede vraag:

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;

Aan de andere kant, als je wildeom duplicaten te zien om wat voor reden dan ook, zou je UNION ALLkunnen gebruiken.

Antwoord 3, autoriteit 5%

Als je een UNION-query gebruikt, worden duplicaten verwijderd, en dit is anders dan het gedrag van full outer joinwaarbij nooit een duplicaat wordt verwijderd:

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

Dit is het verwachte resultaat van full outer join:

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

Dit is het resultaat van het gebruik van leften right Joinmet UNION:

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

[SQL Fiddle]

Mijn voorgestelde vraag is:

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 

Resultaat van bovenstaande zoekopdracht is hetzelfde als verwacht resultaat:

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

[SQL Fiddle]

@Steve Chambers: ^{[Van reacties, hartelijk dank!]}< br>
Opmerking:dit kan de beste oplossing zijn, zowel voor efficiëntie als voor het genereren van dezelfde resultaten als een full outer join. Deze blogpostlegt het ook goed uit – om te citeren uit Methode 2: “Dit behandelt dubbele rijen correct en bevat niets wat niet zou moeten. Het is noodzakelijk om UNION ALLte gebruiken in plaats van gewoon UNION, waardoor de duplicaten die ik wil behouden, worden geëlimineerd. Dit kan aanzienlijk efficiënter zijn bij grote resultatensets, omdat het niet nodig is om duplicaten te sorteren en te verwijderen.”

Ik heb besloten om een andere oplossing toe te voegen die afkomstig is van full outer joinvisualisatie en wiskunde, het is niet beter dat hierboven, maar beter leesbaar:

Volledige outer join betekent (t1 ∪ t2): alles in t1of in t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: alles in zowel t1als t2plus alles in t1die niet in t2staan en plus alle in t2die niet in t1staan:

-- (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]

Antwoord 4

MySql heeft geen FULL-OUTER-JOIN-syntaxis. Je moet emuleren door zowel LEFT JOIN als RIGHT JOIN als volgt te doen-

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

Maar MySql heeft ook geen RIGHT JOIN-syntaxis. Volgens MySql’s outer join simplificationis het recht join wordt geconverteerd naar de equivalente left join door de t1 en t2 in de clausules FROMen ONin de query om te wisselen. Dus de MySql Query Optimizer vertaalt de originele query in het volgende –

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

Het kan geen kwaad om de oorspronkelijke query te schrijven zoals deze is, maar stel dat u predikaten heeft zoals de WHERE-clausule, een before-joinpredikaat of een AND predikaat op de ONclausule, die is een tijdens-joinpredikaat, dan wil je misschien eens naar de duivel kijken; dat is in details.

MySql query optimizer controleert routinematig de predikaten als ze null-verworpenzijn.
Als je nu de RIGHT JOIN hebt gedaan, maar met het predikaat WHERE op de kolom van t1, dan loop je mogelijk het risico dat je in een null-afgewezen-scenario terechtkomt.

Bijvoorbeeld
De volgende vraag –

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'

wordt als volgt vertaald door de 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'

Dus de volgorde van tabellen is veranderd, maar het predikaat wordt nog steeds toegepast op t1, maar t1 staat nu in de ‘ON’-clausule. Als t1.col1 is gedefinieerd als NOT NULL
kolom, dan wordt deze zoekopdracht null-geweigerd.

Elke outer-join (links, rechts, volledig) die null-rejectedis, wordt door MySql geconverteerd naar een inner-join.

Dus de resultaten die u misschien verwacht, kunnen compleet anders zijn dan wat de MySql retourneert. Je zou kunnen denken dat het een bug is met MySql’s RIGHT JOIN, maar dat klopt niet. Het is gewoon hoe de MySql-query-optimizer werkt. De verantwoordelijke ontwikkelaar moet dus op deze nuances letten bij het samenstellen van de query.

Antwoord 5

In SQLite moet je dit doen:

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

Antwoord 6

Geen van de bovenstaande antwoorden is juist, omdat ze de semantiek niet volgen als er dubbele waarden zijn.

Voor een zoekopdracht zoals (van deze duplicaat):

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

Het juiste equivalent is:

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;

Als dit moet werken met NULL-waarden (wat ook nodig kan zijn), gebruik dan de NULL-veilige vergelijkingsoperator, <=>in plaats van =.

Antwoord 7

U kunt het volgende doen:

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

Antwoord 8

De vraag van shA.t aangepast voor meer duidelijkheid:

-- 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 t1.value IS NULL 

Antwoord 9

Je kunt gewoon een volledige outer join converteren, bijvoorbeeld

SELECT fields
FROM firsttable
FULL OUTER JOIN secondtable ON joincondition

in:

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields (replacing any fields from firsttable with NULL)
FROM secondtable
WHERE NOT EXISTS (SELECT 1 FROM firsttable WHERE joincondition)

Of als u ten minste één kolom heeft, zeg foo, in firsttabledie NIET NULL is, kunt u het volgende doen:

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields
FROM firsttable
RIGHT JOIN secondtable ON joincondition
WHERE firsttable.foo IS NULL

Antwoord 10

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

Antwoord 11

wat zei je over de Cross join-oplossing?

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

Antwoord 12

Ik corrigeer de reactie en de werken omvatten alle rijen (gebaseerd op de reactie van 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
    );

13

antwoord:

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

kan als volgt worden hergebruikt:

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;

Het gebruik van een Unie of Unie Al het antwoord dekt niet de randcase waarin de basistabellen hebben gedupliceerde vermeldingen.

Uitleg:

Er is een randbehuizing dat een vakbond of unie niet kan dekken. We kunnen dit niet testen op MySQL omdat het geen volledige buitenste joins ondersteunt, maar we kunnen dit illustreren op een database die het ondersteunt:

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

De Unie-oplossing:

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

Geeft een onjuist antwoord:

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

De UNION ALL-oplossing:

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

Is ook onjuist.

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

Terwijl deze zoekopdracht:

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;

Geeft het volgende:

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

De volgorde is anders, maar komt verder overeen met het juiste antwoord.

Antwoord 14

Het is ook mogelijk, maar je moet dezelfde veldnamen vermelden in select.

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