Opérateurs ensemblistes en SQL : guide complet

Qu’est-ce qu’une opération ensembliste en SQL ?

Les opérations ensemblistes en SQL permettent de combiner ou de comparer les résultats de deux instructions SELECT ou plus. À l’image des ensembles en mathématiques, elles servent à trouver l’union, l’intersection ou la différence entre les lignes renvoyées par nos requêtes. Elles sont donc essentielles pour analyser des données provenant de plusieurs sources ou points de vue.

Voici un aperçu rapide des opérations clés :

• UNION : fusionne toutes les lignes uniques de deux (ou plusieurs) SELECT, en supprimant les doublons.

• UNION ALL : fusionne toutes les lignes de deux (ou plusieurs) SELECT, en conservant les doublons.

• INTERSECT : renvoie uniquement les lignes présentes dans les deux SELECT.

• EXCEPT : renvoie les lignes du premier SELECT qui n’apparaissent pas dans le second.

Comparaison avec l’algèbre relationnelle

L’algèbre relationnelle est un cadre théorique qui sert de base à la compréhension des requêtes. Elle propose des opérations abstraites comme la projection, la sélection et la jointure, fondées sur des principes mathématiques et indépendantes des systèmes de base de données. Voyez-la comme la logique « sous le capot » de nos interactions avec les bases.

Les opérateurs ensemblistes en SQL en sont la mise en œuvre pratique dans un SGBD. Ils permettent d’appliquer union, intersection et différence directement sur les jeux de résultats de requêtes SQL.

Si l’algèbre relationnelle fournit un socle formel, les opérateurs ensemblistes offrent une interface standardisée et accessible pour manipuler les données.

Comprendre le lien entre opérateurs ensemblistes et algèbre relationnelle éclaire les fondements théoriques de SQL et aide à écrire des requêtes avec un modèle mental plus précis de ce qui se passe en arrière-plan.

Types d’opérateurs ensemblistes en SQL

On distingue trois opérateurs principaux en SQL :

• UNION

• INTERSECT

• EXCEPT (ou MINUS selon les dialectes)

Ces opérateurs correspondent mathématiquement à l’union, l’intersection et la différence d’ensembles.

Règles d’utilisation des opérateurs ensemblistes

Avant de combiner des requêtes avec des opérateurs ensemblistes, quatre conditions doivent être réunies. Si l’une d’elles n’est pas respectée, la requête échouera.

• Même nombre de colonnes : chaque SELECT doit renvoyer le même nombre de colonnes. Si l’un en renvoie trois et l’autre deux, la base renvoie une erreur.

• Types de données compatibles : les colonnes correspondantes doivent être de types compatibles. Vous ne pouvez pas aligner une colonne VARCHAR de noms avec un identifiant INTEGER à la même position.

• Même ordre des colonnes : SQL aligne par position, pas par nom. La première colonne de la première requête s’aligne avec la première colonne de la seconde, quels que soient leurs noms.

• ORDER BY uniquement à la fin : ORDER BY ne s’écrit qu’une fois, tout à la fin de la requête combinée. Vous ne pouvez pas l’utiliser à l’intérieur des SELECT individuels.

Dernier détail : les noms de colonnes du résultat proviennent du premier SELECT. Les alias des requêtes suivantes sont ignorés : ajoutez donc vos alias uniquement au premier SELECT si vous souhaitez des en-têtes personnalisés.

Comment utiliser l’opérateur UNION en SQL

L’opérateur UNION combine les résultats de deux requêtes SELECT ou plus en un seul jeu de résultats, en supprimant par défaut les doublons.

Supposons par exemple deux tables, employees et contractors, avec des colonnes similaires comme name, department et salary. À des fins pédagogiques, considérons ces deux tables factices :

employees :

contractors :

Nous pouvons combiner les résultats des deux tables avec la commande suivante :

-- Utiliser UNION pour regrouper employés et prestataires
SELECT name, department, salary FROM employees
UNION
SELECT name, department, salary FROM contractors;

Cette requête sélectionne les colonnes name, department et salary dans les tables employees et contractors et les fusionne en un seul résultat. L’opérateur UNION supprime automatiquement les lignes en double.

Remarquez que Carol, présente dans les deux tables, n’apparaît qu’une seule fois dans le résultat. Si nous voulions conserver les deux occurrences de Carol (avec des salaires différents), nous utiliserions UNION ALL.

UNION vs UNION ALL

L’opérateur UNION ne supprime pas les valeurs NULL. Si une colonne contient des NULL dans un résultat et des valeurs non NULL dans la colonne correspondante de l’autre, les NULL seront conservés dans le résultat final produit par UNION.

Si vous souhaitez inclure toutes les lignes, y compris les doublons et les NULL, utilisez plutôt UNION ALL. Cet opérateur combine les résultats de plusieurs SELECT en conservant toutes les lignes, qu’elles soient dupliquées ou contiennent des NULL.

Comment utiliser l’opérateur INTERSECT en SQL

L’opérateur INTERSECT renvoie uniquement les lignes présentes dans les deux jeux de résultats. Imaginez que l’on cherche les personnes appartenant aux deux groupes.

Utilisons INTERSECT sur nos tables ci-dessus. Pour l’exemple, ne sélectionnons que les colonnes name et department :

-- Utiliser INTERSECT pour trouver les personnes communes
SELECT name, department FROM employees
INTERSECT
SELECT name, department FROM contractors;

Cette requête sélectionne les colonnes name et department dans les tables employees et contractors, et renvoie uniquement les lignes existant dans les deux tables sur l’ensemble des colonnes sélectionnées.

L’opérateur INTERSECT gère les NULL selon les règles de comparaison standard, en considérant les NULL égaux lors de la comparaison de colonnes correspondantes. Il produit également un ensemble vide lorsqu’il traite des jeux de résultats vides.

Autrement dit, si une valeur NULL est présente dans un résultat et que la colonne correspondante de l’autre résultat contient une valeur non NULL, les lignes ne sont pas considérées égales : elles ne figureront pas dans l’intersection.

De plus, si l’un des jeux de résultats fournis à INTERSECT est vide, le résultat global l’est aussi. Il n’y a aucune ligne commune avec un ensemble vide.

Comment utiliser l’opérateur EXCEPT en SQL

L’opérateur EXCEPT renvoie les lignes du premier jeu de résultats qui ne figurent pas dans le second.

Dans Oracle Database, EXCEPT s’écrit MINUS ; les autres grands dialectes (PostgreSQL, SQL Server, MySQL 8.0.31+, SQLite) utilisent EXCEPT.

Par exemple, exécutons la requête suivante :

-- Utiliser EXCEPT pour trouver les employés qui ne sont pas prestataires
SELECT name, department, salary FROM employees
EXCEPT
SELECT name, department, salary FROM contractors;

Les colonnes name, department et salary sont sélectionnées depuis employees et seules les lignes qui n’existent pas dans contractors sont renvoyées.

L’opérateur EXCEPT suit également les règles standard de comparaison pour la gestion des NULL. Avec des ensembles vides, le résultat est vide si le premier ensemble est vide, et comprend toutes les lignes du premier ensemble si le second est vide.

Opérateurs ensemblistes SQL : performance et optimisation

L’impact des opérateurs ensemblistes sur les performances varie selon la taille des jeux de données, la complexité des requêtes et le SGBD utilisé.

Passons en revue les facteurs clés et les stratégies d’optimisation.

Volume de données et complexité des requêtes

Sur de gros volumes, les opérateurs ensemblistes peuvent peser sur les performances, car plus les jeux de résultats à fusionner, intercepter ou comparer sont grands, plus le traitement est coûteux.

Des requêtes complexes mêlant plusieurs sous-requêtes, jointures ou opérateurs ensemblistes ajoutent une charge supplémentaire. Les opérations chaînées ou imbriquées peuvent aggraver l’impact.

Indexation et techniques d’optimisation

Indexez correctement les colonnes impliquées : cela accélère la localisation et la récupération des lignes pertinentes, limite les scans complets et réduit le temps d’exécution.

Pour améliorer les performances, admins et développeurs peuvent recourir à la réécriture de requêtes, l’analyse des plans d’exécution et l’optimisation du schéma. Le caching de requêtes et les vues matérialisées permettent aussi de pré-calculer et stocker des résultats complexes, diminuant le coût des opérations ensemblistes.

Moteur de base et ressources matérielles

Les performances varient selon le moteur de base et ses capacités d’optimisation. Les SGBD emploient des stratégies et algorithmes différents pour traiter ces opérations, d’où des écarts de performance.

La disponibilité des ressources matérielles (CPU, mémoire, I/O disque) influe également. Des ressources suffisantes aident à atténuer les goulets d’étranglement et à assurer une exécution efficace.

Opérateurs ensemblistes SQL : mise en pratique

Les opérateurs ensemblistes ne sont pas qu’un concept théorique : leurs usages concrets peuvent peser dans les décisions business. Illustrons comment une entreprise peut segmenter sa clientèle pour des campagnes ciblées.

Le scénario

Imaginez une entreprise qui vend en ligne et en magasin. Elle dispose de deux jeux de données distincts :

1. Achats en ligne : ID client, historique d’achats, données démographiques, localisation.
2. Transactions en magasin : informations similaires pour les clients en boutique.

Utiliser les opérateurs ensemblistes

Pour une vision consolidée de tous les clients, on commence par UNION afin de fusionner les deux jeux en supprimant les doublons. On obtient ainsi une vue unifiée de la base clients.

Ensuite, on peut utiliser INTERSECT pour identifier les clients ayant acheté à la fois en ligne et en magasin, un segment particulièrement engagé sur plusieurs canaux.

Pour détecter des opportunités de cross-canal, on peut utiliser EXCEPT. Par exemple, SELECT * FROM online_purchases EXCEPT SELECT * FROM in_store_transactions isole les clients n’ayant acheté qu’en ligne. On peut alors leur proposer des offres incitant à visiter un point de vente.

Au-delà de la segmentation

Une fois ces segments identifiés, on peut les affiner selon la démographie ou l’historique d’achats. Cette compréhension granulaire permet de concevoir des campagnes beaucoup plus précises.

Opérateurs ensemblistes vs JOIN

Les opérateurs ensemblistes et les JOIN SQL combinent des données issues de plusieurs requêtes, mais différemment. La question clé : souhaitez-vous combiner des lignes ou des colonnes ?

Limites et points d’attention des opérateurs ensemblistes

Lorsqu’on utilise des opérateurs ensemblistes en SQL, plusieurs limites et facteurs peuvent influencer les performances, la justesse des résultats et l’ergonomie globale.

Compatibilité des types et valeurs NULL

Les colonnes correspondantes doivent avoir des types de données compatibles. Vérifiez-les avant de combiner des requêtes, car les incompatibilités provoquent des erreurs parfois subtiles.

Selon le SGBD et l’opérateur, les valeurs NULL peuvent être traitées différemment. Pour éviter les surprises, comprenez la manière dont les NULL sont gérés.

Impact sur les performances et lignes dupliquées

Les opérations ensemblistes peuvent fortement impacter les performances, surtout sur de grands ensembles ou des requêtes complexes. L’indexation, l’optimisation des requêtes et les ressources matérielles jouent un rôle majeur. Des stratégies d’optimisation sont indispensables pour limiter les goulots d’étranglement.

Par défaut, certains opérateurs suppriment les doublons. Or il peut être nécessaire de les conserver. Comprenez le comportement vis-à-vis des doublons et appliquez les techniques appropriées si besoin.

Ordre des résultats et contraintes mémoire

Les opérateurs ensemblistes ne garantissent pas l’ordre des résultats. Pour trier le jeu combiné, ajoutez une clause ORDER BY à la toute fin de la requête, après le dernier SELECT.

Ces opérations peuvent consommer beaucoup de mémoire et de ressources, surtout sur de gros volumes. Tenez compte des limites de capacité pour éviter dégradations de performance ou instabilités.

Complexité, maintenabilité et compatibilité inter-SGBD

Des requêtes mêlant plusieurs opérateurs ensemblistes, sous-requêtes et jointures peuvent devenir difficiles à lire, maintenir et déboguer. Pour plus de clarté, privilégiez des requêtes concises, bien commentées et modulaires.

La syntaxe et le comportement peuvent varier d’un SGBD à l’autre. Connaître ces différences est crucial pour écrire des requêtes SQL compatibles multi-plateformes.

En conclusion

Les opérateurs ensemblistes répondent à un besoin précis : combiner ou comparer des jeux de résultats sans connaître à l’avance les recouvrements.

Ils couvrent UNION, INTERSECT et EXCEPT : trois opérateurs pour fusionner, comparer et soustraire des jeux de résultats.

Pour aller plus loin, découvrez le cours Joining Data in SQL.