Beide Operatoren stapeln die Ergebnisse zweier Abfragen übereinander, sodass SQL UNION vs. UNION ALL wie eine untergeordnete Wahl erscheinen könnte. Das ist es nicht. Ein zusätzliches Wort ändert sowohl Ihr Ergebnis als auch den Arbeitsaufwand der Datenbank.

Wir haben hier jede Abfrage in SQLite 3.50 ausgeführt, und die angezeigten Ergebnisse und Zeitvorgaben stammen aus diesen Läufen. Das Grundverhalten ist Standard-SQL in allen gängigen Datenbanken. Die Zeitvorgaben unterscheiden sich jedoch je nach Datenbank, Daten und Hardware. Betrachten Sie unsere daher als Beispiel.

Die kurze Antwort

UNION kombiniert zwei Ergebnismengen und gibt nur unterschiedliche Zeilen zurück. UNION ALL kombiniert sie und gibt alles zurück, einschließlich Duplikate. Das ist der ganze Unterschied, und im Rest dieses Artikels geht es darum, warum er wichtig ist.

UNION vs. UNION ALL nebeneinander

Die Beispieldaten

Stellen Sie sich einen Shop vor, der Online- und In-Store-Bestellungen in separaten Tabellen speichert. Beachten Sie, dass Asha zwei identische Online-Bestellungen und eine identische Bestellung im Geschäft hat.

CREATE TABLE online_orders (customer TEXT, total INT);
CREATE TABLE store_orders (customer TEXT, total INT);

INSERT INTO online_orders VALUES ('Asha',100), ('Ben',200), ('Asha',100);
INSERT INTO store_orders VALUES ('Asha',100), ('Dev',300);

Sehen Sie den Unterschied

SELECT customer, total FROM online_orders
UNION
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Dev  | 300

SELECT customer, total FROM online_orders
UNION ALL
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Asha | 100
-- Asha | 100
-- Dev  | 300

Aus fünf Eingabezeilen wurden mit UNION drei, und alle fünf blieben bei UNION ALL. Zwei Dinge sind zu beachten:

• UNION hat Duplikate aus dem gesamten Ergebnis entfernt, einschließlich derjenigen in der ersten Tabelle. Es handelt sich nicht nur um einen tabellenübergreifenden Vergleich.
• Eine Zeile zählt nur dann als Duplikat, wenn jede ausgewählte Spalte übereinstimmt.

Wenn Sie wirklich eine eindeutige Liste wünschen, ist UNION genau das Richtige. Dies bietet jedem Kunden, der über einen der beiden Kanäle gekauft hat:

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

Der falsche Fehler

Aus diesem Grund ist die Wahl wirklich wichtig. Angenommen, Sie möchten einen Gesamtumsatz erzielen. Die oben genannten Bestellungen ergeben zusammen 800. Sehen Sie, was jeder Betreiber gibt:

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION
  SELECT total FROM store_orders
);
-- 600

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION ALL
  SELECT total FROM store_orders
);
-- 800

Die Version UNION ist um 200 falsch. Da wir nur die Spalte total ausgewählt haben, sahen drei separate 100-Rupien-Bestellungen wie Duplikate aus und wurden zu einer zusammengefasst. Es gibt keine Fehlermeldung und 600 scheint eine absolut glaubwürdige Zahl zu sein. Diese Art von stillem Bug kann monatelang in einem Finanzbericht stecken bleiben.

Regeln müssen beide befolgen

Für beide Betreiber gelten die gleichen Anforderungen:

1. Jeder SELECT muss die gleiche Anzahl von Spalten zurückgeben.
2. Spalten werden nach Position und nicht nach Namen abgeglichen. Behalten Sie daher die gleiche Reihenfolge bei.
3. Datentypen an jeder Position müssen kompatibel sein.
4. Die Namen der Ergebnisspalten stammen vom ersten SELECT.
5. ORDER BY steht ganz am Ende und gilt für das gesamte Ergebnis.

Ein Verstoß gegen die erste Regel führt zu einem Fehler. Hier ist die Nachricht von SQLite:

SELECT customer, total FROM online_orders
UNION
SELECT customer FROM store_orders;

-- SELECTs to the left and right of UNION do not have
-- the same number of result columns

Die zweite Regel ist gefährlicher, da sie ohne Fehler fehlschlagen kann. Wenn Sie zwei Spalten desselben Typs vertauschen, z. B. first_name und last_name, wird die Abfrage ausgeführt und die Daten werden falsch ausgegeben. SQLite ist auch in Bezug auf Typen locker, während Datenbanken wie PostgreSQL strenger sind und möglicherweise eine explizite Angabe erfordern CAST.

Beschriften Sie die Quelle

Ein sehr praktischer Trick besteht darin, eine konstante Spalte hinzuzufügen, die anzeigt, woher jede Zeile stammt. Da die Beschriftung unterschiedlich ist, können Zeilen aus verschiedenen Tabellen nicht mehr als Duplikate voneinander behandelt werden.

SELECT customer, total, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, total, 'store' FROM store_orders
ORDER BY customer, channel;

-- Asha | 100 | online
-- Asha | 100 | online
-- Asha | 100 | store
-- Ben  | 200 | online
-- Dev  | 300 | store

Sobald Ihre Daten eine Bezeichnung haben, können Sie sie mit GROUP BY gruppieren, um Verkäufe nach Kanal zu melden. Wenn Sie mehrere Schritte kombinieren müssen, schließen Sie die Union in einen CTE ein, damit sie lesbar bleibt.

Leistung: Was in unserem Test passiert ist

Um Duplikate zu entfernen, muss die Datenbank Zeilen vergleichen, typischerweise durch Sortieren oder Hashing. UNION ALL kann diesen Schritt überspringen. Wir haben zwei Tabellen mit jeweils 500.000 Zeilen erstellt, die Zufallszahlen von 0 bis 50.000 enthalten, sodass es viele Duplikate gab. Wir haben jede Abfrage mehrmals ausgeführt und die beste Zeit beibehalten.

Zuerst haben wir die Ergebnisse gezählt, sodass die Zeit, die für das Zurücksenden von Zeilen an unser Programm aufgewendet wurde, keinen Einfluss auf den Vergleich hatte:

Die große Lücke liegt zum Teil daran, dass SQLite diese Zählung berechnen kann, ohne jede Zeile lesen zu müssen. Lesen Sie das also bitte nicht als „tausendmal schneller“. Es zeigt jedoch die Kernidee: UNION musste echte Arbeit leisten und UNION ALL nicht.

Dann haben wir alle Zeilen in Python abgerufen und das Ergebnis war umgekehrt:

Hier dauerte UNION ALL länger, da es zwanzigmal so viele Zeilen lieferte. Die Deduplizierung hat es nicht verlangsamt. Der Umgang mit einer Million Zeilen war. Die ehrliche Regel lautet also, dass die Berechnung von UNION ALL für die Datenbank günstiger ist, aber auch die Größe des Ergebnisses spielt eine Rolle. Viele Online-Charts reduzieren dies auf „UNION ALL ist immer schneller“, und unser Test zeigt, warum das nicht so einfach ist.

Entscheiden Sie sich auch nicht nur aufgrund der Geschwindigkeit. Wählen Sie zuerst den Operator aus, der die richtige Antwort gibt, und stimmen Sie dann ab. Wenn eine Abfrage langsam ist, lesen Sie unseren Leitfaden zur SQL-Abfrageoptimierung.

Wann jeweils zu verwenden ist

Verwenden Sie UNION ALL, wenn:

• Sie führen Daten aus ähnlichen Tabellen zusammen, beispielsweise aus monatlichen Partitionen.
• Anschließend berechnen Sie Summen oder Zählungen.
• Sie wissen, dass sich die Eingaben nicht überschneiden dürfen.
• Duplikate sind sinnvoll, beispielsweise in Protokollen oder Prüfaufzeichnungen.

Verwenden Sie UNION, wenn:

• Sie benötigen eine eindeutige Liste, beispielsweise jede E-Mail-Adresse in zwei Systemen.
• Überlappende Quellen enthalten denselben Sachverhalt mehr als einmal.

Behandeln Sie UNION ALL als Standard und verwenden Sie UNION bewusst. Das Hinzufügen einer WHERE-Klausel zu jedem Zweig, um frühzeitig zu filtern, hilft beiden.

INTERSECT und EXCEPT

Zwei verwandte Mengenoperatoren beantworten unterschiedliche Fragen. Beide folgen den gleichen Spaltenregeln:

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders;
-- Asha  (bought in both channels)

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders;
-- Ben   (online only)

Oracle verwendet traditionell MINUS anstelle von EXCEPT, und die Unterstützung dieser Operatoren variiert je nach Datenbank und Version ein wenig.

UNION vs. JOIN

Diese werden oft verwechselt, aber sie kombinieren Daten in unterschiedliche Richtungen:

Häufige Fehler

• Verwenden Sie UNION aus Gewohnheit. Es kann Sie an Geschwindigkeit kosten oder gültige Zeilen entfernen.
• Zu wenige Spalten auswählen. Das Weglassen einer ID führt dazu, dass verschiedene Zeilen identisch aussehen.
• Nicht übereinstimmende Spaltenreihenfolge. Werte können ohne Fehler in der falschen Spalte landen.
• Hinzufügen von ORDER BY zu jedem Zweig. Am Ende einmal sortieren.
• Verwendung von UNION zum Patchen eines fehlerhaften Joins. Wenn aufgrund eines Joins Duplikate auftreten, reparieren Sie den Join.
• Ergebnisreihenfolge vorausgesetzt. Ohne ORDER BY ist die Zeilenreihenfolge für keinen der Operatoren garantiert.

Fazit

Stellen Sie jedes Mal eine Frage: Sind identische Zeilen derselbe Sachverhalt oder unterschiedliche Sachverhalte? Wenn sie getrennt sind oder eine Überlappung nicht möglich ist, verwenden Sie UNION ALL. Wenn Sie eine eindeutige Liste benötigen, verwenden Sie UNION. Um Ihre eigenen Berichte zu überprüfen, suchen Sie in Ihrem Code nach UNION in der Nähe von SUM oder COUNT und bestätigen Sie, dass diese Summen wirklich stimmen.

Häufige Fragen

Lerne weiter