Maitriser le SQL chap.04

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 :             

1. l'union
2. l'intersection
3. 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.     

        

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 :     

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" :         

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 :         

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 :    

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 ci-dessous :            

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 :

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 (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 :   

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 :                    

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 :    

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.