Wie kann ich einen FULL OUTER JOIN in MySQL durchführen?

Question

Ich möchte eine [vollständige äußere Verknüpfung](https://en.wikipedia.org/wiki/Join(SQL)#Full_outerjoin) in MySQL. Ist dies möglich? Ist eine vollständige äußere Verknüpfung von MySQL unterstützt?

Answer 1

Sie müssen nicht Vollfugen in MySQL, aber Sie können sicher ihnen nacheifern .

Für einen Code Muster transkribiert von diese Stack Overflow-Frage Sie haben:

Mit zwei 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

---

Die obige Abfrage funktioniert für spezielle Fälle, in denen eine vollständige äußere Verknüpfung Operation würde keine doppelten Zeilen erzeugen. Die obige Abfrage hängt von der UNION set-Operator, um doppelte Zeilen zu entfernen, die durch das Abfragemuster entstanden sind. Wir können die Einführung doppelter Zeilen vermeiden, indem wir eine Anti-Join Muster für die zweite Abfrage, und verwenden Sie dann einen UNION ALL Mengenoperator, um die beiden Mengen zu kombinieren. Im allgemeineren Fall, in dem eine vollständige äußere Verknüpfung doppelte Zeilen ergeben würde, können wir dies tun:

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

Answer 2

Die Antwort, die Pablo Santa Cruz ist korrekt; falls jedoch jemand auf diese Seite gestoßen ist und mehr Klarheit wünscht, finden Sie hier eine detaillierte Aufschlüsselung.

Beispieltabellen

Angenommen, wir haben die folgenden Tabellen:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Inner Joins

Eine innere Verbindung, etwa so:

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

würde uns nur Datensätze liefern, die in beiden Tabellen erscheinen, etwa so:

1 Tim  1 Tim

Innere Verknüpfungen haben keine Richtung (wie links oder rechts), da sie explizit bidirektional sind - wir benötigen eine Übereinstimmung auf beiden Seiten.

Äußere Verbindungen

Outer-Joins hingegen dienen dazu, Datensätze zu finden, für die es in der anderen Tabelle möglicherweise keine Übereinstimmung gibt. Als solche müssen Sie angeben welche Seite der Verknüpfung darf einen fehlenden Datensatz enthalten.

LEFT JOIN y RIGHT JOIN sind Kurzformen für LEFT OUTER JOIN y RIGHT OUTER JOIN Ich werde im Folgenden ihre vollständigen Namen verwenden, um das Konzept der äußeren Joins gegenüber den inneren Joins zu verdeutlichen.

Linke äußere Verbindung

Eine linke äußere Verknüpfung, etwa so:

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

...würde uns alle Datensätze aus der linken Tabelle liefern, unabhängig davon, ob sie eine Übereinstimmung in der rechten Tabelle haben oder nicht, etwa so:

1 Tim   1    Tim
2 Marta NULL NULL

Rechter äußerer Join

Eine rechte äußere Verknüpfung, etwa so:

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

...würde uns alle Datensätze aus der rechten Tabelle liefern, unabhängig davon, ob sie eine Übereinstimmung in der linken Tabelle haben oder nicht, etwa so:

1    Tim   1  Tim
NULL NULL  3  Katarina

Vollständiger äußerer Join

Eine vollständige äußere Verknüpfung würde uns alle Datensätze aus beiden Tabellen liefern, unabhängig davon, ob sie eine Übereinstimmung in der anderen Tabelle haben oder nicht, mit NULLs auf beiden Seiten, wenn es keine Übereinstimmung gibt. Das Ergebnis würde wie folgt aussehen:

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

Wie Pablo Santa Cruz jedoch feststellte, unterstützt MySQL dies nicht. Wir können es emulieren, indem wir eine UNION aus einem linken Join und einem rechten Join machen, etwa so:

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

Sie können sich eine UNION bedeutet: "Führen Sie beide Abfragen aus und stapeln Sie dann die Ergebnisse übereinander"; einige der Zeilen stammen aus der ersten Abfrage und einige aus der zweiten.

Es ist zu beachten, dass ein UNION in MySQL werden exakte Duplikate eliminiert: Tim würde hier in beiden Abfragen erscheinen, aber das Ergebnis der UNION wird er nur einmal aufgeführt. Mein Kollege, der Datenbankguru, ist der Meinung, dass man sich auf dieses Verhalten nicht verlassen sollte. Um es also deutlicher zu machen, könnten wir eine WHERE Klausel in die zweite Abfrage einfügen:

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;

Andererseits, wenn Sie gesucht um aus irgendeinem Grund Duplikate zu sehen, können Sie UNION ALL .

Answer 3

Mit einer Gewerkschaft Abfrage werden Duplikate entfernt, und das ist anders als das Verhalten von vollständige äußere Verknüpfung die niemals Duplikate entfernt:

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

Dies ist das erwartete Ergebnis einer vollständige äußere Verknüpfung :

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

Dies ist das Ergebnis der Verwendung von links y richtige Verbindung con Gewerkschaft :

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

SQL-Gefiedel

Meine vorgeschlagene Anfrage lautet:

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

Das Ergebnis der obigen Abfrage ist dasselbe wie das erwartete Ergebnis:

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

SQL-Gefiedel

---

@Steve Chambers : (Aus den Kommentaren, mit herzlichem Dank!)

Note : Dies könnte die beste Lösung sein, sowohl im Hinblick auf die Effizienz als auch auf die Erzielung der gleichen Ergebnisse wie bei einer FULL OUTER JOIN . Dieser Blogbeitrag erklärt es auch gut - um aus Methode 2 zu zitieren: "Dies behandelt doppelte Zeilen korrekt und enthält nichts, was nicht enthalten sein sollte. Es ist notwendig, die UNION ALL anstelle von einfachem UNION , wodurch die Duplikate, die ich behalten möchte, eliminiert würden. Dies kann bei großen Ergebnismengen wesentlich effizienter sein, da keine Duplikate sortiert und entfernt werden müssen."

---

Ich habe beschlossen, eine weitere Lösung hinzuzufügen, die von vollständige äußere Verknüpfung Visualisierung und Mathematik. Es ist nicht besser als das oben genannte, aber es ist besser lesbar:

Vollständige äußere Verknüpfung bedeutet (t1 t2) : alles in t1 o en t2 (t1 t2) = (t1 t2) + t1_only + t2_only : alles in beiden t1 y t2 plus alle in t1 die nicht in t2 und plus all in t2 die nicht in 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-Gefiedel

Answer 4

MySQL hat keine FULL-OUTER-JOIN-Syntax. Sie müssen sie emulieren, indem Sie sowohl LEFT JOIN als auch RIGHT JOIN wie folgt durchführen:

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

Aber MySQL verfügt auch nicht über eine RIGHT JOIN-Syntax. Laut MySQLs Vereinfachung des Outer Join wird die rechte Verknüpfung in die äquivalente linke Verknüpfung umgewandelt, indem die Werte t1 und t2 in der Datei FROM y ON Klausel in der Abfrage. So übersetzt der MySQL-Abfrageoptimierer die ursprüngliche Abfrage in die folgende -

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

Es kann nicht schaden, die ursprüngliche Abfrage so zu schreiben, wie sie ist, aber wenn Sie Prädikate wie die WHERE-Klausel haben, die eine vor der Verbindung Prädikat oder ein AND-Prädikat auf die ON Klausel, die eine während der Verbindung Prädikat, dann sollten Sie vielleicht einen Blick auf den Teufel werfen, der im Detail steckt.

Der MySQL-Abfrageoptimierer überprüft routinemäßig die Prädikate, wenn sie null-abgelehnt .

Wenn Sie nun den RIGHT JOIN durchgeführt haben, aber mit einem WHERE-Prädikat für die Spalte von t1, dann besteht die Gefahr, dass Sie in ein null-abgelehnt Szenario.

Zum Beispiel kann die Abfrage

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'

wird durch den Abfrageoptimierer wie folgt übersetzt:

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'

Die Reihenfolge der Tabellen hat sich also geändert, aber das Prädikat wird immer noch auf t1 angewandt, aber t1 steht jetzt in der "ON"-Klausel. Wenn t1.col1 definiert ist als NOT NULL Spalte, dann wird diese Abfrage null-abgelehnt .

Jede äußere Verknüpfung (links, rechts, vollständig), die null-abgelehnt wird von MySQL in einen Inner-Join umgewandelt.

Daher können die Ergebnisse, die Sie erwarten, völlig anders sein als die, die MySQL zurückgibt. Sie könnten denken, dass es sich um einen Fehler im RIGHT JOIN von MySQL handelt, aber das ist nicht richtig. Es ist einfach so, wie der MySQL-Abfrageoptimierer arbeitet. Der verantwortliche Entwickler muss also auf diese Nuancen achten, wenn er/sie die Abfrage konstruiert.

Answer 5

Keine der vorangegangenen Antworten ist wirklich richtig, weil sie nicht der Semantik folgen, wenn es doppelte Werte gibt.

Für eine Abfrage wie (von dieses Duplikat ):

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

Die korrekte Entsprechung ist:

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;

Wenn Sie dies für die Arbeit mit NULL Werte (was auch notwendig sein kann), dann verwenden Sie die NULL -sicherer Vergleichsoperator, <=> statt = .