Entrambi gli operatori impilano i risultati di due query uno sopra l’altro, quindi SQL UNION vs UNION ALL potrebbe sembrare una scelta minore. Non lo è. Una parola in più cambia sia il risultato che l’impegno con cui il database deve lavorare.

Abbiamo eseguito tutte le query qui in SQLite 3.50 e i risultati e i tempi mostrati provengono da tali esecuzioni. Il comportamento di base è SQL standard nei principali database. I tempi, tuttavia, differiranno a seconda del database, dei dati e dell’hardware, quindi considera il nostro come un esempio.

La risposta breve

UNION combina due set di risultati e restituisce solo righe distinte. UNION ALL li combina e restituisce tutto, inclusi i duplicati. Questa è l’intera differenza e il resto di questo articolo spiega perché è importante.

UNIONE vs UNIONE TUTTI fianco a fianco

I dati del campione

Immagina un negozio che conserva gli ordini online e quelli in negozio in tabelle separate. Tieni presente che Asha ha due ordini online identici e un ordine identico in negozio.

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

Vedi la differenza

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

Cinque righe di input sono diventate tre con UNION e tutte e cinque sono rimaste con UNION ALL. Due cose da notare:

• UNION ha rimosso i duplicati dall’intero risultato, compresi quelli nella prima tabella. Non si tratta solo di confrontare tabelle.
• Una riga viene considerata duplicata solo quando ogni colonna selezionata corrisponde.

Quando vuoi veramente un elenco distinto, UNION è esattamente giusto. Ciò offre a ogni cliente che ha acquistato tramite uno dei due canali:

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

Il bug totale sbagliato

Questo è il motivo per cui la scelta conta davvero. Supponiamo di volere le entrate totali. Gli ordini sopra indicati ammontano a 800. Scopri cosa offre ciascun operatore:

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

La versione UNION è sbagliata di 200. Poiché abbiamo selezionato solo la colonna total, tre ordini separati da 100 rupie sembravano duplicati e sono collassati in uno solo. Non c’è alcun messaggio di errore e 600 sembra un numero perfettamente credibile. Questo tipo di bug silenzioso può rimanere in un rapporto finanziario per mesi.

Regole che entrambi devono seguire

Entrambi gli operatori hanno gli stessi requisiti:

1. Ciascuno SELECT deve restituire lo stesso numero di colonne.
2. Le colonne corrispondono alla posizione, non al nome, quindi mantienile nello stesso ordine.
3. I tipi di dati in ciascuna posizione devono essere compatibili.
4. I nomi delle colonne dei risultati provengono da first SELECT.
5. ORDER BY va proprio alla fine e si applica all’intero risultato.

Infrangere la prima regola genera un errore. Ecco il messaggio di 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

La seconda regola è più pericolosa perché può fallire senza errori. Se si scambiano due colonne dello stesso tipo, ad esempio first_name e last_name, la query viene eseguita e i dati risultano errati. SQLite è anche rilassato riguardo ai tipi, mentre database come PostgreSQL sono più rigidi e potrebbero richiedere un’impostazione esplicita CAST.

Etichetta la fonte

Un trucco molto pratico è aggiungere una colonna costante che mostri da dove proviene ciascuna riga. Poiché l’etichetta è diversa, le righe di tabelle diverse non possono più essere trattate come duplicati l’una dell’altra.

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

Una volta che i tuoi dati hanno un’etichetta, puoi raggrupparli per segnalare le vendite per canale, utilizzando GRUPPO PER. Se è necessario combinare più passaggi, racchiudere l’unione in un CTE per mantenerla leggibile.

Prestazioni: cosa è successo nel nostro test

Per rimuovere i duplicati, il database deve confrontare le righe, in genere ordinandole o effettuando l’hashing. UNION ALL può saltare questo passaggio. Abbiamo creato due tabelle con 500.000 righe ciascuna, contenenti numeri casuali da 0 a 50.000, quindi c’erano molti duplicati. Abbiamo eseguito ciascuna query più volte e abbiamo mantenuto il tempo migliore.

Innanzitutto, abbiamo contato i risultati, in modo che il tempo impiegato per inviare le righe al nostro programma non influisse sul confronto:

L’enorme divario è in parte dovuto al fatto che SQLite può calcolare quel conteggio senza leggere ogni riga, quindi per favore non leggerlo come “migliaia di volte più veloce”. Tuttavia, mostra l’idea centrale: UNION doveva fare un vero lavoro e UNION ALL no.

Quindi abbiamo recuperato tutte le righe in Python e il risultato è stato invertito:

In questo caso, UNION ALL ha impiegato più tempo perché ha consegnato venti volte più righe. La deduplica non è ciò che l’ha rallentata. Gestire un milione di righe era. Quindi la regola onesta è che UNION ALL è più economico da calcolare per il database, ma anche la dimensione del risultato è importante. Molti grafici online riducono questo a “UNION ALL è sempre più veloce” e il nostro test mostra perché non è così semplice.

Non scegliere nemmeno in base alla velocità. Scegli l’operatore che dà per primo la risposta corretta, quindi sintonizzati. Se una query è lenta, leggi la nostra guida all’ottimizzazione delle query SQL.

Quando utilizzarli ciascuno

Utilizza UNION ALL quando:

• Stai unendo dati da tabelle simili, ad esempio partizioni mensili.
• Calcolerai i totali o i conteggi in seguito.
• Sai che gli input non possono sovrapporsi.
• I duplicati sono significativi, come nei registri o nei record di controllo.

Utilizzare UNION quando:

• Hai bisogno di un elenco univoco, ad esempio ogni indirizzo email su due sistemi.
• Le fonti sovrapposte contengono lo stesso fatto più di una volta.

Tratta UNION ALL come predefinito e usa UNION deliberatamente. Aggiungere una clausola WHERE a ciascun ramo, per filtrare in anticipo, aiuta entrambi.

INTERSEZIONE e ECCEZIONE

Due operatori di insiemi correlati rispondono a domande diverse. Entrambi seguono le stesse regole di colonna:

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 ha tradizionalmente utilizzato MINUS anziché EXCEPT e il supporto per questi operatori varia leggermente in base al database e alla versione.

UNIONE vs UNISCITI

Questi sono spesso confusi, ma combinano i dati in direzioni diverse:

Errori comuni

• Utilizzo UNION per abitudine. Potrebbe costarti velocità o rimuovere righe valide.
• Selezione di un numero troppo limitato di colonne. Tralasciando un ID, le righe distinte sembreranno identiche.
• Ordine delle colonne non corrispondente. I valori possono finire nella colonna sbagliata senza alcun errore.
• Aggiunta ORDER BY a ciascun ramo. Ordina una volta, alla fine.
• Utilizzo di UNION per correggere un join errato. Se compaiono duplicati a causa di un join, correggi il join.
• Presupponendo l’ordine dei risultati. Senza ORDER BY, l’ordine delle righe non è garantito per nessuno degli operatori.

Considerazioni finali

Fai una domanda ogni volta: righe identiche sono lo stesso fatto o fatti separati? Se sono separati o se la sovrapposizione è impossibile, utilizzare UNION ALL. Se hai bisogno di un elenco univoco, usa UNION. Per controllare i tuoi rapporti, cerca il tuo codice per UNION vicino a SUM o COUNT e conferma che quei totali sono davvero corretti.

Domande frequenti

Continua a imparare