Beide operators stapelen de resultaten van twee query’s op elkaar, dus SQL UNION versus UNION ALL lijkt misschien een kleine keuze. Dat is het niet. Eén extra woord verandert zowel uw resultaat als hoe hard de database moet werken.

We hebben elke query hier in SQLite 3.50 uitgevoerd, en de weergegeven resultaten en timings zijn afkomstig van die runs. Het basisgedrag is standaard-SQL voor grote databases. De timing zal echter verschillen voor uw eigen database, data en hardware, dus gebruik de onze als voorbeeld.

Het korte antwoord

UNION combineert twee resultaatsets en retourneert alleen afzonderlijke rijen. UNION ALL combineert ze en retourneert alles, inclusief duplicaten. Dat is het hele verschil, en de rest van dit artikel gaat over waarom het ertoe doet.

UNION versus UNION ALLEN naast elkaar

De voorbeeldgegevens

Stel je een winkel voor die online en in-store bestellingen in aparte tabellen bijhoudt. Houd er rekening mee dat Asha twee identieke online bestellingen heeft en één identieke bestelling in de winkel.

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

Zie het verschil

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

Vijf invoerrijen werden drie met UNION, en alle vijf bleven bij UNION ALL. Twee dingen om op te merken:

• UNION verwijderde duplicaten uit het hele resultaat, inclusief die in de eerste tabel. Het gaat niet alleen om het vergelijken van tabellen.
• Een rij telt alleen als duplicaat als elke geselecteerde kolom overeenkomt.

Als je echt een duidelijke lijst wilt, heeft UNION precies gelijk. Dit geeft elke klant die via een van beide kanalen heeft gekocht:

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

De fout-totaal bug

Dit is de reden waarom de keuze er echt toe doet. Stel dat u de totale omzet wilt. De bovenstaande bestellingen zijn opgeteld 800. Bekijk wat elke operator geeft:

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

De UNION-versie klopt met 200 niet. Omdat we alleen de kolom total hebben geselecteerd, leken drie afzonderlijke bestellingen van 100 roepie op duplicaten en vielen ze samen in één. Er is geen foutmelding en 600 lijkt een volkomen geloofwaardig getal. Dit soort stille bugs kunnen maanden in een financieel rapport blijven zitten.

Regels die beide moeten volgen

Beide operators hebben dezelfde vereisten:

1. Elke SELECT moet hetzelfde aantal kolommen retourneren.
2. Kolommen worden gekoppeld op positie, niet op naam, dus houd ze in dezelfde volgorde.
3. Gegevenstypen op elke positie moeten compatibel zijn.
4. De namen van de resultaatkolommen komen uit de eerste SELECT.
5. ORDER BY staat helemaal aan het einde en is van toepassing op het hele resultaat.

Het overtreden van de eerste regel levert een fout op. Hier is het bericht van 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

De tweede regel is gevaarlijker, omdat deze zonder fouten kan mislukken. Als u twee kolommen van hetzelfde type verwisselt, zoals first_name en last_name, wordt de query uitgevoerd en komen de gegevens verkeerd uit. SQLite is ook ontspannen over typen, terwijl databases zoals PostgreSQL strenger zijn en mogelijk een expliciete CAST vereisen.

Label de bron

Een heel praktische truc is om een constante kolom toe te voegen die laat zien waar elke rij vandaan komt. Omdat het label verschilt, kunnen rijen uit verschillende tabellen niet langer als duplicaten van elkaar worden behandeld.

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

Zodra uw gegevens een label hebben, kunt u deze groeperen om verkopen per kanaal te rapporteren met behulp van GROUP BY. Als je meerdere stappen moet combineren, wikkel de unie dan in een CTE om deze leesbaar te houden.

Prestaties: wat er in onze test is gebeurd

Om duplicaten te verwijderen, moet de database rijen vergelijken, meestal door ze te sorteren of te hashen. UNION ALL kan deze stap overslaan. We hebben twee tabellen gemaakt met elk 500.000 rijen, met willekeurige getallen van 0 tot 50.000, dus er waren veel duplicaten. We hebben elke zoekopdracht meerdere keren uitgevoerd en de beste tijd behouden.

Eerst telden we de resultaten, zodat de tijd die besteed werd aan het terugsturen van rijen naar ons programma geen invloed had op de vergelijking:

Het enorme verschil komt deels doordat SQLite dat aantal kan berekenen zonder elke rij te lezen, dus lees dat alsjeblieft niet als “duizenden keren sneller”. Het laat echter wel het kernidee zien: UNION moest echt werk doen en UNION ALL niet.

Vervolgens hebben we alle rijen in Python opgehaald en het resultaat is omgedraaid:

Hier duurde UNION ALL langer omdat het twintig keer zoveel rijen opleverde. Het is niet deduplicatie die dit heeft vertraagd. Het verwerken van een miljoen rijen was dat wel. De eerlijke regel is dus dat UNION ALL goedkoper is voor de database om te berekenen, maar de grootte van het resultaat is ook van belang. Veel online grafieken reduceren dit tot “UNION ALL is altijd sneller”, en onze test laat zien waarom dit niet zo eenvoudig is.

Kies ook niet alleen op basis van snelheid. Kies eerst de operator die het juiste antwoord geeft en stem vervolgens af. Als een zoekopdracht traag is, lees dan onze handleiding over SQL-queryoptimalisatie.

Wanneer moet u ze allemaal gebruiken?

Gebruik UNION ALL wanneer:

• U voegt gegevens uit vergelijkbare tabellen samen, zoals maandpartities.
• Achteraf berekent u totalen of tellingen.
• Je weet dat de inputs elkaar niet kunnen overlappen.
• Duplicaten zijn betekenisvol, zoals in logboeken of auditrecords.

Gebruik UNION wanneer:

• U hebt een unieke lijst nodig, zoals elk e-mailadres op twee systemen.
• Overlappende bronnen bevatten meer dan eens hetzelfde feit.

Behandel UNION ALL als standaard en gebruik UNION bewust. Het toevoegen van een WHERE-clausule aan elke vertakking, om vroegtijdig te filteren, helpt beide.

SNIJDEN en BEHALVE

Twee gerelateerde set-operatoren beantwoorden verschillende vragen. Beide volgen dezelfde kolomregels:

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 heeft traditioneel MINUS gebruikt in plaats van EXCEPT, en de ondersteuning voor deze operators varieert enigszins per database en versie.

UNION versus JOIN

Deze zijn vaak verward, maar ze combineren gegevens in verschillende richtingen:

Veel voorkomende fouten

• Uit gewoonte gebruik je UNION. Het kan je snelheid kosten of geldige rijen verwijderen.
• Als u te weinig kolommen selecteert. Als u een ID weglaat, zien afzonderlijke rijen er identiek uit.
• Niet-overeenkomende kolomvolgorde. Waarden kunnen zonder fouten in de verkeerde kolom terechtkomen.
• ORDER BY toevoegen aan elke tak. Sorteer één keer aan het einde.
• Gebruik UNION om een ​​slechte join te herstellen. Als er duplicaten verschijnen vanwege een join, repareer dan de join.
• Uitgaande van de resultaatvolgorde. Zonder ORDER BY kan de rijvolgorde voor geen van beide operators worden gegarandeerd.

Tot slot

Stel elke keer één vraag: zijn identieke rijen hetzelfde feit of afzonderlijke feiten? Als ze gescheiden zijn, of als overlap onmogelijk is, gebruik dan UNION ALL. Als je een unieke lijst nodig hebt, gebruik dan UNION. Om uw eigen rapporten te controleren, zoekt u in uw code naar UNION in de buurt van een SUM of COUNT en bevestigt u dat deze totalen echt kloppen.

Veelgestelde vragen

Blijf leren