Chapitre 21 : les opérations ensemblistes en SQL

Chapitre 21 : les opérations ensemblistes en SQL
1 - Introduction
Le langage SQL permet de réaliser sur un groupe de deux tables les trois
opérations de base de la théorie des ensembles, c'est à dire :
l'union
l'intersection
la différence
De ces trois opérations, seule l'union présente vraiment de l'intérêt. Si, dans les
exposés relatifs à SQL, on regroupe généralement les trois opérations
ensemblistes, c'est pour se conformer à une logique mathématicienne. Comme
nous le verrons dans la suite de ce chapitre, l'intersection et la différence sont en
fait des requêtes multi-tables particulières, que l'on peut réaliser même si l'on ne
dispose pas d'une commande spécifique -- ce qui est effectivement le cas
lorsqu'on utilise le SGBD Access.
La requête union, par contre, est quasi irremplaçable : elle seule permet de mettre
deux tables bout à bout en leur faisant jouer des rôles identiques, et en éliminant
systématiquement les doublons. Certes, la requête ajout permet d'ajouter une
table à une autre, mais dans ce cas les deux tables ne jouent pas le même rôle, et
l'élimination des doublons -- si elle s'avère nécessaire -- requiert soit la création
d'un index multi-champ, soit un traitement ultérieur (regroupement).
A ce chapitre nous rajouterons un paragraphe concernant le produit cartésien de
deux tables, requête qui n'est utilisée que très rarement et dans des cas très
spécifiques.
Comme pour les autres chapitres de ce tutoriel (ou tutorial, ou cours en ligne),
nous utiliserons le SGBD Access comme support pratique.
2 - L'union de deux tables
L'union de deux tables est une une feuille de données (ou une table) contenant
chaque ligne de la première table et chaque ligne de la seconde table. Les lignes
communes aux deux tables ne sont conservées qu'en un seul exemplaire, c'est à
dire que l'opération d'union élimine les doublons. Les champs que l'on fait
correspondre dans les deux tables n'ont pas besoin de porter les mêmes noms ni
de se présenter dans le même ordre -- ni même de posséder le même type de
donnée si la transposition est possible (une date en texte, par exemple).
Exemple 1. La figure ci-dessous illustre l'opération d'union dans le cas simple où
les champs portent le même nom, possèdent les mêmes propriétés, et sont situés
dans le même ordre.
nom
prénom
Chose Jules
Machin Pierre
Truc
Patrick
Table1
nom
nom
union Pouf
prénom
Jean
Chose Jules
Table2
prénom
Chose Jules
= Machin Pierre
Pouf
Jean
Truc
Patrick
Résultat
Il n'existe pas dans Access d'interface graphique permettant de créer une requête
Union. Il faut donc écrire soi-même le code SQL requis. Pour ouvrir l'éditeur de
requêtes SQL, nous sélectionnons l'objet "Requêtes" dans la fenêtre "Base de
données", nous n'introduisons aucune table dans l'interface graphique de
définition des requêtes, et nous basculons en mode SQL.
Si notre base contient les deux tables précitées, nous pouvons exécuter la requête
SQL suivante, contenant l'opérateur UNION :
SELECT nom, prénom
FROM Table1
UNION
SELECT nom, prénom
FROM Table2;
A l'exécution de la requête, nous constatons qu'une feuille de données est créée,
qui rassemble le contenu des deux tables, et en élimine les doublons. Si nous
enregistrons la requête (sa structure, pas son contenu), Access fait précéder son
nom de l'icône
pour rappeler qu'il s'agit d'une requête de type "Union".
Exemple 2. Les champs mis en correspondance n'ont pas besoin de porter le
même nom, comme le montre l'exemple suivant, dans lequel nous avons modifié
les intitulés des champs de la table "Table2" :
nom
nom
prénom
last-name first-name
Chose Jules
union Pouf
Machin Pierre
Truc
Jean
Chose
Patrick
Table1
prénom
Chose Jules
= Machin Pierre
Jules
Pouf
Jean
Truc
Patrick
Table2
Résultat
La requête union correspondante est représentée ci-dessous. Le SGBD sait que
"last-name" correspond à "nom", et "first-name" à "prénom", parce que les champs
sont cités dans cet ordre dans les deux clauses SELECT. La feuille de données
résultante emprunte les noms de ses champs à la première table. La présence
des crochets traduit le fait que SQL ne tolère pas le tiret dans les noms des tables
ni dans ceux des champs (caractère non autorisé).
SELECT nom, prénom
FROM Table1
UNION
SELECT [last-name], [first-name]
FROM Table2;
Exemple 3. Nous pouvons faire en sorte d'imposer les noms des champs dans la
feuille de données résultante pour obtenir, par exemple, le résultat suivant :
nom
Col1
prénom
Chose Jules
Machin Pierre
Truc
nom
union Pouf
Patrick
Table1
prénom
Jean
Chose Jules
Table2
Col2
Chose Jules
= Machin Pierre
Pouf
Jean
Truc
Patrick
Résultat
En SQL, la requête s'écrit ainsi :
SELECT nom AS Col1, prénom AS Col2
FROM Table1
UNION
SELECT nom AS Col1, prénom AS Col2
FROM Table2;
Exemple 4. Nous pouvons appliquer des critères de sélection (clause WHERE)
aux enregistrements de chacune des deux tables que nous voulons réunir. Il faut
cependant que nous fassions bien attention à bien définir le même critère dans les
deux clauses SELECT. Ainsi, pour exclure les noms commençant par A, B ou C
dans le résultat final :
nom
prénom
Chose Jules
Machin Pierre
Truc
Patrick
Table1
nom
union Pouf
nom
prénom
Machin Pierre
=
Jean
Chose Jules
Table2
prénom
Pouf
Jean
Truc
Patrick
Résultat (avec critères)
nous utilisons la requête union suivante :
SELECT nom, prénom
FROM Table1
WHERE nom>"D"
UNION
SELECT nom, prénom
FROM Table2
WHERE nom>"D";
Exemple 5. Pour faire en sorte que la requête union n'élimine pas les doublons,
nous rajoutons l'opérateur ALL après UNION, comme le montre la requête cidessous :
SELECT nom, prénom
FROM Table1
UNION ALL
SELECT nom, prénom
FROM Table2;
La feuille de données obtenue résulte de la simple mise bout à bout des deux
tables, sans tri ni élimination des doublons :
Col1
nom
prénom
Chose Jules
Machin Pierre
Truc
Patrick
nom
union all Pouf
Chose Jules
prénom
Jean
Chose Jules
Col2
=
Machin Pierre
Truc
Patrick
Pouf
Jean
Chose Jules
Table1
Table2
Résultat
Exemple 6. Comment faire en sorte que la requête Union crée une table ? Une
des techniques possibles consiste à emboîter la requête union dans une
commande d'insertion. Le code SQL suivant :
INSERT INTO Table3
SELECT *
FROM (SELECT nom, prénom
FROM Table1
UNION SELECT [last-name], [first-name]
FROM Table2);
peut être exécuté à condition que la table "Table3" ait été préalablement créée
(vide) -- mais cette condition n'est pas requise pour observer le résultat de la
requête en mode feuille de données. La requête ci-dessus est reconnue par le
SGBD Access comme une requête "Ajout" (à cause de la clause INSERT) et, si
nous l'enregistrons, son nom apparaît précédé de l'icône
correspondante. Le
code ci-dessus nous offre un exemple d'emboîtement en SQL.
Cet aspect didactique mis à part, il est plus simple, quand on opère dans le SGBD
Access, de réaliser d'abord la requête union, puis de réutiliser son résultat dans
une requête de sélection simple, à laquelle on demande de créer une table.
Remarque. Si nous tentons de réaliser l'union de deux tables contenant un champ
(nommé "essai") de type Mémo, Objet OLE ou Lien hypertexte, nous obtenons le
message suivant (rédigé dans le jargon franglais des informaticiens) :
Cela signifie que, dans le SGBD Access, la requête Union ne fonctionne pas sur
les champs de type Mémo, Objet OLE et Lien hypertexte. Pour réunir deux tables
comportant des champs de ce type sans les perdre, il faut utiliser la requête Ajout.
La requête Union présente un autre défaut : elle est lente à l'exécution, aussi bien
programmée soit-elle. Cela provient du fait qu'elle trie et dédoublonne, alors que la
requête Ajout se contente de compléter une table avec les données d'une autre. A
titre d'exemple, sur un PC de qualité standard, il faut environ plusieurs minutes (34) pour réaliser l'union de 10.000 enregistrements avec 300.000 autres.
3 - L'intersection de deux tables
L'intersection de deux tables est une une feuille de données (ou une table)
contenant seulement les lignes communes aux deux tables. Les conditions sont
les mêmes que pour l'union. La figure ci-dessous illustre l'opération intersection :
nom
prénom
Chose Jules
Machin Pierre
Truc
Patrick
nom
inter Pouf
prénom
Jean
Chose Jules
=
nom
prénom
Chose Jules
Le code SQL correspondant dépend de la version utilisée. En SQL1, il s'écrit :
SELECT nom, prénom
FROM Table1
WHERE Table1.nom IN (SELECT nom FROM Table2) AND Table1.prénom IN (SELECT prénom
FROM Table2);
Cette syntaxe, qui fait appel à l'emboîtement autant de fois qu'il y a de colonnes, a
été simplifiée par l'introduction de l'opérateur INTERSECT en SQL2. Le nouveau
code s'écrit :
SELECT nom, prénom
FROM Table1
INTERSECT
SELECT nom, prénom
FROM Table2;
Mais... le SGBD Access ne reconnaît pas l'opérateur INTERSECT. Il affiche un
message d'erreur qui, bien entendu, ne correspond pas à la situation (une
habitude bien ancrée en informatique). Seule reste en lice la première syntaxe, qui
apparaît de la manière suivante dans l'interface graphique :
Comme on peut le constater, il est possible d'utiliser des critères écrits en code
SQL dans l'interface graphique. Ma foi, autant utiliser la fenêtre SQL directement !
Que l'opérateur INTERSECT ne soit pas reconnu par Access n'est pas un drame.
Outre les deux commandes SQL déjà citées, nous en imaginer une troisième, qui
résulte directement de la définition de l'intersection :
SELECT DISTINCT Table1.nom, Table1.prénom
FROM Table1, Table2
WHERE Table1.nom=Table2.nom AND Table1.prénom=Table2.prénom;
La présence de l'opérateur DISTINCT est indispensable, sinon tous les
enregistrements de l'intersection figurent en double dans la feuille de données. Le
SGBD les écrit deux fois parce qu'il les trouve une fois dans la première table, et
une autre fois dans la seconde.
Le code ci-dessus est représenté ainsi dans l'interface graphique (on vérifie en
outre que la propriété "Valeurs distinctes" de la requête vaut "Oui") :
Nous pouvons encore traduire l'intersection en utilisant des relations (ou jointures)
entre les champs des deux tables. Rappelons qu'une relation créée dans la
fenêtre graphique de définition des requêtes possède une existence éphémère, ce
que prouve le fait qu'elle n'apparaît pas dans la fenêtre
"Relations". Pour
traduire qu'un enregistrement de la seconde table est identique à celui de la
première table, nous utilisons l'opérateur "Est Pas Null". Dans l'interface
graphique, la requête apparaît comme le montre la figure ci-dessous.
La version SQL de cette requête s'écrit :
SELECT Table1.nom, Table1.prénom
FROM Table1 INNER JOIN Table2 ON Table1.nom = Table2.nom AND Table1.prénom =
Table2.prénom
WHERE Table2.nom Is Not Null AND Table2.prénom Is Not Null;
4 - La différence de deux tables
La différence de deux tables est une une feuille de données (ou une table)
contenant les enregistrements de la première table qu'on ne retrouve pas dans la
seconde. Les conditions sont les mêmes que pour l'union. La figure ci-dessous
illustre l'opération différence :
nom
prénom
Chose Jules
Machin Pierre
Truc
Patrick
nom
diff Pouf
prénom
Jean
Chose Jules
nom
prénom
= Machin Pierre
Truc
Patrick
Le code SQL correspondant dépend de la version du langage utilisée. En SQL1, il
s'écrit comme pour l'intersection, à ceci près que IN est remplacé par NOT IN :
SELECT nom, prénom
FROM Table1
WHERE Table1.nom NOT IN (SELECT nom FROM Table2) AND Table1.prénom NOT IN (SELECT
prénom FROM Table2);
(dans l'interface graphique de définition des requêtes, "Not In" devient "Pas In" -un joyeux mélange de français et d'anglais).
La syntaxe précédente, qui fait appel à l'emboîtement autant de fois qu'il y a de
colonnes, a été simplifiée par l'introduction de l'opérateur EXCEPT dans SQL2
(MINUS dans le SGBD Oracle). Le nouveau code s'écrit :
SELECT nom, prénom
FROM Table1
EXCEPT
SELECT nom, prénom
FROM Table2;
Le SGBD Access ne reconnaît ni l'opérateur INTERSECT, ni l'opérateur MINUS,
et il affiche un message d'erreur. On peut bien sûr s'en tirer comme pour
l'intersection, mais il faut cette fois utiliser la jointure gauche. Voici comment
apparaît la requête dans l'interface graphique (où la jointure gauche est
représentée par une flèche allant de la première vers la seconde table) :
Le code SQL correspondant s'écrit :
SELECT Table1.nom, Table1.prénom
FROM Table1 LEFT JOIN Table2 ON Table1.prénom = Table2.prénom AND Table1.nom =
Table2.nom
WHERE Table2.nom Is Null AND Table2.prénom Is Null;
La jointure gauche est nécessaire parce qu'il faut que le SGBD, lors de l'exécution
de la requête, utilise tous les enregistrements de la première table, qu'ils soient ou
non présents dans la seconde. Si nous utilisions la jointure interne, le SGBD
n'examinerait que les enregistrements communs, et n'en retiendrait évidemment
aucun. Si nous utilisions la jointure droite, le SGBD utiliserait tous les
enregistrements de la deuxième table, mais seulement ceux de la première table
qui sont communs, et le résultat serait de nouveau vide.
Le choix du type de jointure vous pose problème ? Essayez les trois types sur un
exemple simple, vous trouverez ainsi quel est celui qui donne la réponse juste.
5 - Le produit cartésien de deux tables
Le produit cartésien de deux tables est une feuille de données (ou une table)
obtenue en associant tous les enregistrements de la seconde table à chacun des
enregistrements de la première.
Si chacune des deux tables contient un grand nombre d'enregistrements, le
résultat du produit est gigantesque. De plus, il ne présente généralement pas
d'intérêt. En général, le produit cartésien résulte d'une étourderie (oubli d'une
relation dans une requête multi-table). La figure ci-dessous illustre l'opération de
produit cartésien :
nom
nom
Chose
Machin
prénom
prénom
Chose Jean
X Jean
= Chose Jules
Jules
Machin Jean
Machin Jules
Le code SQL correspondant s'écrit :
SELECT Table1.nom, Table2.prénom
FROM Table1, Table2;
En mode graphique, on introduit les deux tables dans la fenêtre "Requête
sélection" en veillant bien à ce qu'il n'y ait pas de relation entre elles. Puis on
introduit dans la grille le champ "nom" de la première table, et le champ "prénom"
de la seconde.
Le produit cartésien rend service quand il faut rassembler dans une même table (à
une seule ligne) diverses tables ou feuilles de données comportant une seule
ligne. Le produit cartésien fournit alors une table ne comportant qu'une seule ligne.
Les tables à une seule ligne résultent généralement de calculs effectués
verticalement, tels que comptage du nombre d'enregistrements ou opérations
diverses (somme, moyenne, fonctions statistiques) sur les champs numériques ou
monétaires.
6 - Conclusion
Il est clair que les concepteurs du SGBD Access n'aimaient pas les ensembles.
Car si l'opérateur UNION est reconnu (mais inutilisable dans l'interface graphique)
par le logiciel, les opérateurs INTERCEPT et EXCEPT (ou MINUS) sont ignorés.
Pour réaliser une intersection ou une différence dans Access, la solution la plus
simple consiste à utiliser les jointures (sans se tromper), associées à des
conditions sur le Null. Il faut cependant reconnaître que, des trois opérations
ensemblistes, seule la requête Union a vraiment de l'importance.
Rappelons pour terminer que, dans une requête Union, les deux tables jouent le
même rôle, contrairement à ce qui se passe dans la requête "Ajout". De plus,
aucune des deux tables n'est modifiée par l'exécution de la requête, et les
doublons sont automatiquement éliminés, à moins que nous ne demandions à les
conserver. La requête union mérite à elle seule que l'on se familiarise un peu avec
le SQL.