Les deux opérateurs empilent les résultats de deux requêtes les uns sur les autres, donc SQL UNION vs UNION ALL peut ressembler à un choix mineur. Ce n’est pas. Un mot supplémentaire modifie à la fois votre résultat et la difficulté avec laquelle la base de données doit travailler.

Nous avons exécuté chaque requête ici dans SQLite 3.50, et les résultats et les délais affichés proviennent de ces exécutions. Le comportement de base est le SQL standard dans les principales bases de données. Cependant, les délais différeront en fonction de votre propre base de données, de vos données et de votre matériel, alors considérez le nôtre comme une illustration.

La réponse courte

UNION combine deux jeux de résultats et renvoie uniquement des lignes distinctes. UNION ALL les combine et renvoie tout, y compris les doublons. C’est toute la différence, et le reste de cet article explique pourquoi c’est important.

UNION contre UNION TOUS côte à côte

Les exemples de données

Imaginez une boutique qui conserve les commandes en ligne et en magasin dans des tableaux séparés. Notez qu’Asha a deux commandes identiques en ligne, et une commande identique en magasin.

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

Voyez la différence

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

Cinq lignes d’entrée sont devenues trois avec UNION, et les cinq sont restées avec UNION ALL. Deux choses à remarquer :

• UNION a supprimé les doublons de l’ensemble du résultat, y compris ceux du premier tableau. Il ne s’agit pas seulement de comparer entre les tableaux.
• Une ligne compte comme un doublon uniquement lorsque chaque colonne sélectionnée correspond.

Lorsque vous voulez vraiment une liste distincte, UNION est tout à fait exact. Cela donne à chaque client ayant acheté via l’un ou l’autre canal :

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

Le bug du mauvais total

C’est la raison pour laquelle le choix compte vraiment. Supposons que vous souhaitiez un revenu total. Les commandes ci-dessus totalisent 800. Voyez ce que donne chaque opérateur :

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 version UNION est erronée de 200. Comme nous avons sélectionné uniquement la colonne total, trois commandes distinctes de 100 roupies ressemblaient à des doublons et étaient regroupées en une seule. Il n’y a pas de message d’erreur et 600 semble être un nombre parfaitement crédible. Ce type de bug discret peut rester dans un rapport financier pendant des mois.

Règles que les deux doivent suivre

Les deux opérateurs ont les mêmes exigences :

1. Chaque SELECT doit renvoyer le même nombre de colonnes.
2. Les colonnes correspondent par position, et non par nom, alors conservez-les dans le même ordre.
3. Les types de données dans chaque position doivent être compatibles.
4. Les noms des colonnes de résultats proviennent du premier SELECT.
5. ORDER BY va à la toute fin et s’applique à l’ensemble du résultat.

Enfreindre la première règle donne une erreur. Voici le message de 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 deuxième règle est plus dangereuse, car elle peut échouer sans erreur. Si vous échangez deux colonnes du même type, telles que first_name et last_name, la requête s’exécute et les données sont erronées. SQLite est également plus souple en ce qui concerne les types, tandis que les bases de données telles que PostgreSQL sont plus strictes et peuvent nécessiter un explicite CAST.

Étiquetez la source

Une astuce très pratique consiste à ajouter une colonne constante indiquant la provenance de chaque ligne. Étant donné que l’étiquette diffère, les lignes de différentes tables ne peuvent plus être traitées comme des doublons les unes des autres.

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

Une fois que vos données ont une étiquette, vous pouvez les regrouper pour rapporter les ventes par canal, en utilisant GROUP BY. Si vous devez combiner plusieurs étapes, enveloppez l’union dans un CTE pour la garder lisible.

Performances : ce qui s’est passé lors de notre test

Pour supprimer les doublons, la base de données doit comparer les lignes, généralement en les triant ou en les hachant. UNION ALL peut ignorer cette étape. Nous avons créé deux tableaux de 500 000 lignes chacun, contenant des nombres aléatoires de 0 à 50 000, il y avait donc beaucoup de doublons. Nous avons exécuté chaque requête plusieurs fois et avons conservé le meilleur temps.

Tout d’abord, nous avons compté les résultats, afin que le temps passé à renvoyer les lignes à notre programme n’affecte pas la comparaison :

L’énorme écart est en partie dû au fait que SQLite peut calculer ce nombre sans lire chaque ligne, alors ne lisez pas cela comme “des milliers de fois plus rapide”. Cela montre cependant l’idée de base : UNION devait faire un vrai travail et UNION ALL ne l’a pas fait.

Ensuite, nous avons récupéré toutes les lignes dans Python, et le résultat a été inversé :

Ici, UNION ALL a pris plus de temps car il a livré vingt fois plus de lignes. Ce n’est pas la déduplication qui l’a ralenti. La gestion d’un million de lignes était. La règle honnête est donc que UNION ALL est moins cher à calculer pour la base de données, mais la taille du résultat compte également. De nombreux graphiques en ligne réduisent cela à “UNION ALL est toujours plus rapide”, et notre test montre pourquoi ce n’est pas si simple.

Ne choisissez pas non plus en fonction de la seule vitesse. Choisissez d’abord l’opérateur qui donne la bonne réponse, puis réglez. Si une requête est lente, lisez notre guide sur l’optimisation des requêtes SQL.

Quand utiliser chacun

Utilisez UNION ALL lorsque :

• Vous fusionnez des données provenant de tables similaires, telles que des partitions mensuelles.
• Vous calculerez ensuite des totaux ou des décomptes.
• Vous savez que les entrées ne peuvent pas se chevaucher.
• Les doublons sont significatifs, comme dans les journaux ou les enregistrements d’audit.

Utilisez UNION lorsque :

• Vous avez besoin d’une liste unique, telle que chaque adresse e-mail sur deux systèmes.
• Les sources qui se chevauchent contiennent le même fait plus d’une fois.

Traitez UNION ALL comme valeur par défaut et utilisez UNION délibérément. L’ajout d’une clause WHERE à chaque branche, pour filtrer tôt, aide les deux.

INTERSECT et SAUF

Deux opérateurs d’ensemble liés répondent à des questions différentes. Les deux suivent les mêmes règles de colonne :

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 utilise traditionnellement MINUS plutôt que EXCEPT, et la prise en charge de ces opérateurs varie légèrement selon la base de données et la version.

UNION vs REJOINDRE

Celles-ci sont souvent confondues, mais elles combinent les données dans des directions différentes :

Erreurs courantes

• Utiliser UNION par habitude. Cela peut vous coûter de la vitesse ou supprimer des lignes valides.
• Sélection de trop peu de colonnes. L’omission d’un identifiant rend les lignes distinctes identiques.
• Ordre des colonnes incompatible. Les valeurs peuvent atterrir dans la mauvaise colonne sans aucune erreur.
• Ajout de ORDER BY à chaque branche. Trier une fois, à la fin.
• Utiliser UNION pour corriger une mauvaise jointure. Si des doublons apparaissent à cause d’une jointure, corrigez la jointure.
• En supposant l’ordre des résultats. Sans ORDER BY, l’ordre des lignes n’est garanti pour aucun des deux opérateurs.

Pour conclure

Posez une question à chaque fois : les lignes identiques correspondent-elles au même fait ou à des faits distincts ? S’ils sont séparés ou si le chevauchement est impossible, utilisez UNION ALL. Si vous avez besoin d’une liste unique, utilisez UNION. Pour vérifier vos propres rapports, recherchez votre code UNION à côté d’un SUM ou d’un COUNT et confirmez que ces totaux sont vraiment corrects.

Questions fréquentes

Continuez à apprendre