|
Chapitre 20 :
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 : |
|
|||||||||
|
|
|
|
|
|||||||||
|
|
|
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. |
|
|||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 |
|
|||||||||||||||||||||||||||||||||||||||||
|
|
|
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" : |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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é). |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
En
SQL, la requête s'écrit ainsi : |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
nous
utilisons la requête union suivante : |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 ci-dessous : |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
La
feuille de données obtenue résulte de la simple mise bout à bout des deux tables,
sans tri ni élimination des doublons : |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
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 |
|
|||||||||||||||||||||||||||||||||||||||||
|
|
|
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 (3-4) 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 : |
|
|||||||||||||||||||||||
|
|
||||||||||||||||||||||||||
|
||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||
|
|
|
Le
code SQL correspondant dépend de la version utilisée. En SQL1, il
s'écrit : |
|
|||||||||||||||||||||||
|
||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||
|
||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||
|
||||||||||||||||||||||||||
|
|
|
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 |
|
|||||||||||||||||||||||
|
|
||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||
|
|
|
La version SQL de cette
requête s'écrit : |
|
|||||||||||||||||||||||
|
||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||
|
|
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 : |
|
|||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||||
|
||||||||||||||||||||||||||||
|
|
|
(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 : |
|
|||||||||||||||||||||||||
|
||||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||||||
|
||||||||||||||||||||||||||||
|
|
|
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 : |
|
|||||||||||||||||||||
|
|
||||||||||||||||||||||||
|
||||||||||||||||||||||||
|
|
||||||||||||||||||||||||
|
|
|
Le
code SQL correspondant s'écrit : |
|
|||||||||||||||||||||
|
||||||||||||||||||||||||
|
|
|
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. |
|
|
|
|||