Opérations ensemblistes — UNION / INTERSECT / EXCEPT

Données utilisées dans cet article — employee et contractor

Les opérations ensemblistes (des opérations qui combinent plusieurs résultats SELECT en un seul comme l'union, l'intersection ou la différence d'ensembles) sont la syntaxe pour empiler verticalement des résultats récupérés séparément, ou extraire les lignes communes aux deux ou les lignes qui n'apparaissent que d'un seul côté.

Tu vas travailler avec quatre d'entre elles : UNION (union), UNION ALL (union qui garde les doublons), INTERSECT (intersection) et EXCEPT (différence).

Le matériel est la table des employés employee (30 lignes) et la table des prestataires contractor (6 lignes).

Tu vas essayer les quatre opérations ensemblistes une par une — en joignant deux résultats avec des conditions différentes au sein de employee, ou en combinant des résultats extraits de tables différentes comme employee et contractor en un seul.

Avant de plonger dans les exercices, regarde les définitions de colonnes et un échantillon des données des deux tables utilisées dans cet article — employee et contractor.

① Exécute PRAGMA table_info(employee); et PRAGMA table_info(contractor); pour vérifier les définitions de colonnes des deux tables.

② Exécute SELECT * FROM employee LIMIT 5; et SELECT * FROM contractor LIMIT 5; pour prévisualiser les 5 premières lignes de données.

Le prérequis des opérations ensemblistes — être compatible UNION

Les quatre opérations ensemblistes joignent deux SELECT ou plus sous la forme SELECT ... opérateur SELECT ....

Le prérequis pour les joindre est que le SELECT supérieur et le SELECT inférieur soient compatibles UNION (nombre de colonnes égal, et types des colonnes correspondantes compatibles).

SELECT name FROM employee et SELECT name FROM contractor sont tous deux une seule colonne chaîne, donc ils sont compatibles.

En revanche, SELECT name FROM employee et SELECT name, city FROM contractor ont un nombre de colonnes différent, donc tu ne peux pas les joindre.

-- compatible UNION : les deux côtés ont 2 colonnes (name, city)
SELECT name, city FROM employee WHERE city = 'Tokyo'
UNION
SELECT name, city FROM contractor
ORDER BY name;

-- ORDER BY ne va qu'une seule fois tout à la fin
-- tu ne peux pas écrire un ORDER BY par SELECT au milieu

UNION et UNION ALL — supprimer les doublons ou les garder

UNION empile deux résultats verticalement et fusionne les lignes complètement identiques en une seule (suppression des doublons).

UNION ALL ne supprime aucun doublon et garde chaque ligne des deux côtés telle quelle.

Quand tu n'as pas besoin de supprimer les doublons, ou quand tu veux conserver « combien de lignes sont apparues dans les deux », utilise UNION ALL.

UNION lance un tri interne pour détecter les doublons, donc UNION ALL est l'opération la plus légère.

L'exemple ci-dessous joint le résultat « habitant à Kyoto » et le résultat « salaire de 7 millions ou plus » au sein de employee.

Un employé qui satisfait les deux conditions devient 1 ligne avec UNION, et 2 lignes avec UNION ALL.

-- UNION : les lignes en double sont fusionnées en une seule
SELECT name FROM employee WHERE city = 'Osaka'
UNION
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

-- UNION ALL : les doublons sont gardés (les lignes correspondant aux deux apparaissent deux fois)
SELECT name FROM employee WHERE city = 'Osaka'
UNION ALL
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

Imagine le besoin : « je veux combiner les employés habitant à Kyoto et les employés à salaire élevé en un seul répertoire. Quiconque correspond aux deux ne doit apparaître qu'une fois ». (Exécute-le correctement et l'explication s'affiche.)

① Écris un SELECT qui extrait le name de employee où city est Kyoto.

② Joins-le avec un SELECT qui extrait le name des lignes où salary est de 7 000 000 ou plus, en utilisant un opérateur ensembliste qui supprime les doublons.

③ Trie le résultat par name croissant (pour garantir l'ordre des lignes).

Imagine le besoin : « je veux empiler les résultats des deux mêmes conditions que l'Exercice 1 sans supprimer les doublons — tous — pour pouvoir confirmer visuellement quels noms sont apparus dans les deux ».

① Joins les deux mêmes SELECT que l'Exercice 1 (le name pour habitant à Kyoto / salaire 7 000 000 ou plus) en utilisant un opérateur ensembliste qui garde les doublons.

② Trie le résultat par name croissant. Confirme qu'un nom apparaissant dans les deux ressort comme deux lignes consécutives.

INTERSECT et EXCEPT — lignes communes et lignes de différence

• INTERSECT (intersection) renvoie uniquement les lignes qui apparaissent en commun dans les deux résultats.
• EXCEPT (différence) renvoie les lignes qui sont dans le résultat supérieur mais pas dans le résultat inférieur.

Les deux suppriment automatiquement les doublons (le même comportement que UNION).

EXCEPT est une opération asymétrique dont le résultat change si tu inverses le côté supérieur et le côté inférieur — A EXCEPT B et B EXCEPT A sont des choses différentes.

L'exemple ci-dessous joint le name pour « habitant à Kyoto » et « salaire de 6,5 millions ou plus » au sein de employee avec INTERSECT, extrayant les employés qui satisfont les deux (les lignes communes).

-- INTERSECT : employés habitant à Osaka ET avec salaire 6000000 ou plus
SELECT name FROM employee WHERE city = 'Osaka'
INTERSECT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

-- EXCEPT : employés habitant à Osaka mais PAS avec salaire 6000000 ou plus
SELECT name FROM employee WHERE city = 'Osaka'
EXCEPT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

Résous le besoin « je veux extraire uniquement les employés habitant à Kyoto ET avec un salaire de 6 500 000 ou plus » en utilisant les opérations ensemblistes.

① Écris un SELECT qui extrait le name de employee où city est Kyoto.

② Joins-le avec un SELECT qui extrait le name des lignes où salary est de 6 500 000 ou plus, en utilisant un opérateur ensembliste qui ne renvoie que les lignes communes.

③ Trie le résultat par name croissant.

Imagine le besoin : « parmi les employés habitant à Kyoto, je veux extraire uniquement ceux qui ne sont PAS à un salaire de 6 500 000 ou plus (les Kyotoïtes moins bien payés) ».

① Écris un SELECT qui extrait le name de employee où city est Kyoto.

② Joins-le avec un SELECT qui extrait le name des lignes où salary est de 6 500 000 ou plus, en utilisant un opérateur ensembliste qui renvoie les lignes du côté supérieur mais pas du côté inférieur (attention — l'ordre des côtés supérieur et inférieur affecte le résultat).

③ Trie le résultat par name croissant.

Opérations ensemblistes entre tables différentes — employee et contractor

Les opérations ensemblistes fonctionnent non seulement au sein d'une seule table mais aussi entre des résultats extraits de tables séparées.

Tant qu'ils sont compatibles UNION (nombre de colonnes et type correspondants), les tables peuvent différer.

Quand tu veux trier le résultat joint, écris un seul ORDER BY après le tout dernier SELECT (il s'applique à l'ensemble du résultat joint).

Joindre le name de la table des employés employee et de la table des prestataires contractor avec INTERSECT te permet de faire ressortir les personnes qui apparaissent dans les deux avec le même nom.

L'exemple ci-dessous extrait uniquement la seule colonne name et les joint avec INTERSECT.

Comme le name des deux tables contient Alice et Bob, 2 personnes sont renvoyées comme lignes communes.

-- noms qui apparaissent dans employee et contractor
SELECT name FROM employee
INTERSECT
SELECT name FROM contractor
ORDER BY name;

-- noms dans contractor mais pas dans employee (externe seulement)
SELECT name FROM contractor
EXCEPT
SELECT name FROM employee
ORDER BY name;

Imagine le besoin : « je veux lister les personnes de la table des prestataires contractor dont le même nom n'existe PAS dans la table des employés employee (des noms purement externes uniquement) ».

① Écris un SELECT qui extrait le name de contractor.

② Joins-le avec un SELECT qui extrait le name de employee, en utilisant un opérateur ensembliste qui renvoie les lignes du côté supérieur mais pas du côté inférieur. Place le SELECT contractor en haut.

③ Trie le résultat par name croissant.