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 UNION
om 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 JOIN
en right Join
zijn afkortingen voor LEFT OUTER JOIN
en 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 UNION
zien 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 UNION
in MySQL exacte duplicaten zal elimineren: Tim zou in beide zoekopdrachten hier verschijnen, maar het resultaat van de UNION
vermeldt 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 ALL
kunnen gebruiken.
Antwoord 3, autoriteit 5%
Als je een UNION
-query gebruikt, worden duplicaten verwijderd, en dit is anders dan het gedrag van full outer join
waarbij 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 left
en right Join
met UNION
:
value | value
------+-------
Null | 5
1 | 1
2 | 2
4 | Null
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
@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 eenfull 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 omUNION ALL
te gebruiken in plaats van gewoonUNION
, 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 join
visualisatie en wiskunde, het is niet beter dat hierboven, maar beter leesbaar:
Volledige outer join betekent
(t1 ∪ t2)
: alles int1
of int2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only
: alles in zowelt1
alst2
plus alles int1
die niet int2
staan en plus alle int2
die niet int1
staan:
-- (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)
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 FROM
en ON
in 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 ON
clausule, 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 firsttable
die 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