LE SQL de A à Z : 4e partie - groupage et ensembles

SQLPro

Le groupage et les requêtes portant sur des sous-ensembles constituent souvent la partie la plus difficile à comprendre du SQL… C'est pourquoi nous y consacrerons un long article.
Enfin, nous verrons différents opérateurs ensemblistes pour manipuler les tables à la manière des patates de Venn. ♪

- Titre : LE SQL de A à Z : 4e partie - groupage et ensembles

- Auteur : SQLPro

- Parution : 26 août 2003

- Licence : Copyright ® 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Préambule

- 1. La clause GROUP BY

- 2. La clause HAVING

- 3. Les opérateurs ensemblistes

- 3-1. L'UNION

- 3-2. L'INTERSECTION

- 3-3. LA DIFFÉRENCE

- 3-4. Considérations diverses sur les opérations ensemblistes du SQL

- 3-4-1. Le mot clef ALL peut qualifier l'opérateur ensembliste

- 3-4-2. On peut restreindre l'intersection à certaines colonnes en utilisant le filtre CORRESPONDING BY

- 3-4-3. Les opérateurs UNION et INTERSECT sont réflexifs, mais EXCEPT ne l'est pas

- 3-4-4. On peut utiliser la clause ORDER BY

- 3-4-5. Le nom des colonnes du résultat est celui du premier des ordres SELECT composant la globalité de la requête

- 3-4-6. Mis à part l'union, les opérations ensemblistes d'intersection et de différence peuvent être construites à partir de requêtes de base

- 3-5. Quelques exemples

- 3-5-1. Exemple d'UNION

- 3-5-2. Exemple de DIFFÉRENCE

- 3-5-3. Exemple d'INTERSECTION

- 4. Résumé

Préambule

NOTA : La structure de la base de données exemple, ainsi qu'une version des principales bases utilisées sont disponibles dans la page « La base de données exemple »

1 - La clause GROUP BY

Vous trouverez des compléments d'information sur le sujet aux pages 153 à 154 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

La clause GROUP BY est nécessaire dès que l'on utilise des fonctions de calculs statistiques avec des données brutes. Cette clause groupe les lignes sélectionnées en se basant sur la valeur de colonnes spécifiées pour chaque ligne et renvoie une seule ligne par groupe.
On peut la comparer à une opération de découpage de sous-ensemble un peut à la manière des « niveaux de rupture » lorsque l'on réalise des états imprimés.

Cherchons à compter le nombre de chambres par étage de notre hôtel :

Exemple 1

SELECT COUNT(CHB_ID) AS NOMBRE, CHB_ETAGE
FROM   T_CHAMBRE

La requête, telle que présentée ci-dessus n'est pas calculable. En effet comment décider si le comptage doit se faire pour chaque chambre ou pour un groupe de chambre et notamment les groupes formés par chacun des étages ?

Si la requête se faisait pour chaque chambre, le résultat serait :

NOMBRE      CHB_ETAGE
----------- ---------
1           RDC
1           RDC
1           RDC
1           RDC
1           1er
1           1er
1           1er
1           1er
1           1er
...

Et n'aurait pas beaucoup de sens !

En revanche, un regroupement par étage, donne tout son sens à la requête :

NOMBRE      CHB_ETAGE
----------- ---------
8           1er
8           2e
4           RDC

Pour réaliser un tel regroupement, il faut introduire une clause de groupage dans la requête. Pour cela SQL fournit la clause GROUP BY :

Exemple 1 bis

SELECT COUNT(*) AS NOMBRE, CHB_ETAGE
FROM   T_CHAMBRE
GROUP  BY CHB_ETAGE

NOTA

- La présence de la clause GROUP BY est nécessaire dès que la clause de sélection, ou le filtre WHERE, ou encore les jointures comportent simultanément des calculs d'agrégation et la présence de colonnes de table hors de calculs d'agrégation.

- De plus, toutes les colonnes représentées hors des calculs d'agrégation doivent figurer dans la clause GROUP BY.

La plupart du temps, le moteur de requête vous avertira d'une probable incohérence de calcul des agrégats à l'aide d'un message d'erreur (avant exécution de la requête), du genre : « La colonne … est incorrecte dans la liste de sélection parce qu'elle n'est pas contenue dans une fonction d'agrégation et qu'il n'y a pas de clause GROUP BY » (SQL Server).

Cherchons maintenant à compter le couchage de chaque étage :

Exemple 2

SELECT SUM(CHB_COUCHAGE) AS NOMBRE, CHB_ETAGE
FROM   T_CHAMBRE
GROUP  BY CHB_ETAGE

NOMBRE      CHB_ETAGE
----------- ---------
23          1er
22          2e
9           RDC

Un peu plus compliqué, cherchons à savoir quel a été le nombre de nuitées pour chaque chambre au cours de l'année 1999 (une nuitée étant une personne passant une nuit dans une chambre. Si deux personnes occupent la même chambre, cela fait deux nuitées) :

Exemple 3

SELECT SUM(CHB_PLN_CLI_NB_PERS), C.CHB_ID
FROM   T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI P
ON C.CHB_ID = P.CHB_ID
WHERE  PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP  BY C.CHB_ID

PERSONNE    CHB_ID
----------- -----------
558         1
385         2
322         3
367         4
445         5
720         6
390         7
456         8
362         9
364         10
529         11
468         12
347         13
360         14
484         15
720         16
491         17
372         18
504         19
369         20

Partant de là, nous pouvons rechercher le taux d'occupation de chaque chambre dans cette période.
Le nombre maximal de nuitées étant le nombre de couchages de chaque chambre multiplié par toutes les dates de l'année, ce calcul s'obtient par :

Exemple 4

SELECT SUM(CHB_COUCHAGE) AS MAX_OCCUPATION, CHB_ID
FROM   T_CHAMBRE C
CROSS JOIN T_PLANNING P
WHERE  PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP  BY C.CHB_ID

MAX_OCCUPATION CHB_ID
-------------- -----------
1095           1
730            2
730            3
730            4
1095           5
1825           6
730            7
1095           8
730            9
730            10
1095           11
1095          12
730            13
730            14
1095           15
1825           16
1095           17
730            18
1095           19
730            20

Il ne suffit plus que de « raccorder » les requêtes des exemples 3 et 4 :

Exemple 5

SELECT (CAST(SUM(CHB_PLN_CLI_NB_PERS) AS FLOAT) /
CAST(SUM(CHB_COUCHAGE) AS FLOAT)) * 100
AS TAUX_OCCUPATION_POURCENT,
C.CHB_ID
FROM   T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
CROSS JOIN T_PLANNING P
WHERE  P.PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP  BY C.CHB_ID

TAUX_OCCUPATION_POURCENT   CHB_ID
-------------------------- ------
65.293040293040292         1
74.606580829756794         2
74.64488636363636          3
74.660326086956516         4
65.301318267419958         5
60.27972027972028          6
74.759284731774414         7
67.191977077363902         8
75.878378378378372         9
75.681198910081747         10
67.715458276333777         11
65.209471766848822         12
75.920873124147334         13
74.595687331536382         14
67.343256653134858         15
61.162162162162161         16
66.891284815813108         17
77.762982689747005         18
65.900900900900908         19
76.625172890733069         20

Nous allons voir, maintenant que la clause GROUP BY est souvent utilisée avec la clause HAVING…

2 - La clause HAVING

Vous trouverez des compléments d'information sur le sujet aux pages 153 à 154 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

La clause HAVING agit comme le filtre WHERE, mais permet de filtrer non plus les données, mais les opérations résultant des regroupements, c'est-à-dire très généralement toute expression de filtre devant introduire un calcul d'agrégation.

Pour tenter de comprendre l'utilité de la clause having, nous allons procéder par un exemple simple : recherchons un étage de l'hôtel capable de coucher au moins 20 personnes.
Partant de notre exemple 2, nous serions tentés d'écrire :

Exemple 6

SELECT SUM(CHB_COUCHAGE) AS NOMBRE, CHB_ETAGE
FROM   T_CHAMBRE
WHERE  SUM(CHB_COUCHAGE) >= 20
GROUP  BY CHB_ETAGE

Mais cette requête va immanquablement provoquer une erreur avant exécution du fait de la clause WHERE. En effet, souvenons-nous que le filtre WHERE agit sur les données des tables et permet de filtrer ligne après ligne. Or le filtrage ne porte plus sur la notion de lignes, mais sur une notion de sous-ensemble de la table. En d'autres termes, le filtre, ici, doit porter sur chacun des groupes. C'est pourquoi SQL introduit le filtre HAVING qui porte, non pas sur les données, mais sur les calculs résultants des regroupements.

En l'occurrence, dans notre exemple, nous devons déporter le filtre WHERE dans la clause HAVING et l'opération deviendra possible :

Exemple 6 bis

SELECT SUM(CHB_COUCHAGE) AS NOMBRE, CHB_ETAGE
FROM   T_CHAMBRE
GROUP  BY CHB_ETAGE
HAVING SUM(CHB_COUCHAGE) >= 20

NOMBRE      CHB_ETAGE
----------- ---------
23          1er
22          2e

Autre exemple :
Partant de la requête vue à l'exemple 6, essayons de ne retenir que les chambres occupées à plus de 2/3, soit 66.666666… % ?

Une première tentative consisterait à écrire :

Exemple 7

SELECT (CAST(SUM(CHB_PLN_CLI_NB_PERS) AS FLOAT) /
CAST(SUM(CHB_COUCHAGE) AS FLOAT)) * 100 AS
TAUX_OCCUPATION_POURCENT, C.CHB_ID
FROM   T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
CROSS JOIN T_PLANNING P
WHERE  P.PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP  BY C.CHB_ID
HAVING CAST(SUM(CHB_PLN_CLI_NB_PERS) AS FLOAT) /
CAST(SUM(CHB_COUCHAGE) AS FLOAT) >= 2.0/3.0

TAUX_OCCUPATION_POURCENT   CHB_ID
--------------------------- -----
74.606580829756794          2
74.64488636363636           3
74.660326086956516          4
74.759284731774414          7
67.191977077363902          8
75.878378378378372          9
75.681198910081747          10
67.715458276333777          11
75.920873124147334          13
74.595687331536382          14
67.343256653134858          15
66.891284815813108          17
77.762982689747005          18
76.625172890733069          20

Un autre exemple intéressant est de rechercher quelles sont les chambres qui ont été inoccupées plus de 10 jours au cours du mois de janvier 2000…

Pour calculer le nombre de jours où les chambres ont été occupées au cours de janvier 2000, il suffit de faire :

Exemple 8

SELECT COUNT(C.CHB_ID) AS NOMBRE, C.CHB_ID
FROM   T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
WHERE  EXTRACT(MONTH FROM P.PLN_JOUR) = 1
AND  EXTRACT(YEAR FROM P.PLN_JOUR) = 2000
GROUP  BY C.CHB_ID

NOMBRE      CHB_ID
----------- -----------
19          1
20          2
18          3
23          4
21          5
19          6
18          7
20          8
21          9
24          10
21          11
22          12
20          13
24          14
23          15
20          16
19          17
24          18
19          19
23          20

Mais nous voulons le complément de cette colonne NOMBRE avec le nombre de jours du mois de janvier 2000, ce qui représente 31 jours. La requête devient donc :

Exemple 9

SELECT 31 - COUNT(C.CHB_ID) AS NOMBRE, C.CHB_ID
FROM   T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
WHERE  EXTRACT(MONTH FROM P.PLN_JOUR) = 1
AND  EXTRACT(YEAR FROM P.PLN_JOUR) = 2000
GROUP  BY C.CHB_ID

NOMBRE      CHB_ID
----------- -----------
12          1
11          2
13          3
8           4
10          5
12          6
13          7
11          8
10          9
7          10
10          11
9           12
11          13
7           14
8           15
11          16
12          17
7           18
12          19
8           20

Dès lors il ne suffit plus que de filtrer le calcul :
31 - COUNT(C.CHB_ID)
pour ne retenir que les valeurs supérieures à 10. Comme cette expression contient une fonction d'agrégation, ce qui suppose un groupage, il est nécessaire d'utiliser le filtre WHERE :

Exemple 10

SELECT 31 - COUNT(C.CHB_ID) AS NOMBRE, C.CHB_ID
FROM   T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
WHERE  EXTRACT(MONTH FROM CPC.PLN_JOUR) = 1
AND  EXTRACT(YEAR FROM CPC.PLN_JOUR) = 2000
GROUP  BY C.CHB_ID
HAVING 31 - COUNT(C.CHB_ID) > 10

NOMBRE      CHB_ID
----------- -----------
12          1
11          2
13          3
12          6
13          7
11          8
11          13
11          16
12          17
12          19

Pour dernier exemple, nous allons nous placer dans un contexte plus proche de la réalité. Voici notre directeur qui souhaite inciter les clients avec lesquels il a peu travaillé à venir plus souvent. L'idée lui prend d'offrir un bon de réduction de 15 % sur l'ensemble des prestations hôtelières pour tous les clients ayant eu un chiffre d'affaires inférieur à 15 000 F HT au cours de l'année 2000.
Une première approche consisterait par exemple à rechercher toutes les chambres libres entre les deux dates :

Exemple 11

SELECT F.CLI_ID,
-- le calcul est le suivant :
-- (quantité * montant * remise en %) - remise en francs
-- les colonnes "remises" pouvant avoir des valeurs NULL
-- il faut les remplacer par un zéro à l'aide de la fonction COALESCE
SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) AS CA
FROM   T_FACTURE F
INNER JOIN T_LIGNE_FACTURE L
ON F.FAC_ID = L.FAC_ID
WHERE  EXTRACT(YEAR FROM F.FAC_DATE) = 2000
GROUP  BY C.CLI_ID
HAVING SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) < 15000

CLI_ID  CA
------- ------------------
1       14510.6
37      12488.799999999999
39      13327.4
40      14785.200000000001
52      13890.799999999999
61      14168.799999999999
87      14992.4

Les clients 1, 37, 39, 40, 52, 61, 87 étant bien les personnes visées par la promotion.

Mais poussons notre raisonnement… Imaginons une personne ayant été cliente en 1999 et pas en 2000. Pour cela, rajoutons les lignes suivantes dans notre jeu de données :

INSERT INTO T_CLIENT (CLI_ID, CLI_NOM) VALUES (101, 'DUGUDU')

INSERT INTO T_FACTURE (FAC_ID, CLI_ID, FAC_DATE, FAC_PMT_DATE) VALUES (2375, 101, '25/03/1999', '15/04/1999')

INSERT INTO T_LIGNE_FACTURE (LIF_ID, FAC_ID, LIF_QTE, LIF_MONTANT, LIF_TAUX_TVA) VALUES (16791, 2375, 1, 320, 18.6)

L'exécution de la requête de l'exemple 10, nous délivre le même résultat, alors que nous aimerions récupérer le client 101… Quelle erreur avons-nous commise ?
Le fait est que nous avons à nouveau fait l'hypothèse du monde clos ! (voir à la page Le SQL de A à Z, 3e partie, le paragraphe 2.4) Il faut impérativement remonter jusqu'à la table des clients et réaliser des jointures externes sur les tables des factures et des lignes de facture :

Exemple 12

SELECT C.CLI_ID,
SUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0))
AS CA
FROM   T_CLIENT C
LEFT OUTER JOIN T_FACTURE F
ON C.CLI_ID = F.CLI_ID
LEFT OUTER JOIN T_LIGNE_FACTURE L
ON F.FAC_ID = L.FAC_ID
WHERE  EXTRACT(YEAR FROM F.FAC_DATE) = 2000
GROUP  BY C.CLI_ID
HAVING SUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) < 15000

CLI_ID  CA
------- ------------------
1       14510.6
37      12488.799999999999
39      13327.4
40      14785.200000000001
52      13890.799999999999
61      14168.799999999999
87      14992.4

Or et pour aussi bizarre que cela puisse paraître, cette requête ne nous donne toujours pas le client 101… Pourquoi ? Il persiste en fait deux raisons à cela :

1. Nous avons supposé que le calcul du CA retournait une valeur… ;

2. Nous avons filtré sur l'année 2000, après avoir effectué la jointure.

Pour contrer le cas n° 1, nous ne devons pas oublier que les NULL se propagent dans les calculs et que le marqueur NULL ne peut être comparée à aucune valeur. Il faut donc réaliser le filtre HAVING en retenant l'expression si elle vaut moins de 15 000, mais aussi si elle vaut NULL.

Pour contrer le cas n° 2, et c'est beaucoup plus subtil, il faut inclure dans la condition de jointure entre les tables T_CLIENT et T_FACTURE l'expression située dans la clause WHERE.

La requête ainsi corrigée devient :

Exemple 13

SELECT C.CLI_ID,
SUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) AS CA
FROM   T_CLIENT C
LEFT OUTER JOIN T_FACTURE F
ON C.CLI_ID = F.CLI_ID
AND EXTRACT(YEAR FROM F.FAC_DATE) = 2000
LEFT OUTER JOIN T_LIGNE_FACTURE L
ON F.FAC_ID = L.FAC_ID
GROUP  BY C.CLI_ID
HAVING SUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) < 15000
OR  SUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) IS NULL

CLI_ID  CA
------- ------------------
1       14510.6
37      12488.799999999999
39      13327.4
40      14785.200000000001
52      13890.799999999999
61      14168.799999999999
87      14992.4
101     NULL

Nous pourrions ainsi passer encore plusieurs cas en revue tant le mélange des clauses WHERE et HAVING associées aux jointures externes peut devenir passionnant, mais ce site n'a pas pour but de vous poser des pièges, mais plutôt de vous faire toucher du doigt la problématique de ce type de requête et la logique ensembliste sous-jacente.

Dans la suite du site et notamment lorsque vous aborderez les sous-requêtes, de nouveaux exemples utilisant la clause HAVING et le groupage, sont présentés.

3 - Les opérateurs ensemblistes

Vous trouverez des compléments d'information sur le sujet aux pages 180 à 187 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

C'est une des parties les plus simples de l'ordre SELECT. À la fois par sa syntaxe, mais aussi par sa compréhension facile. Il s'agit ni plus ni moins que de réaliser des opérations sur les ensembles représentés par des tables ou des extraits de table. Les opérations ensemblistes du SQL sont l'union, l'intersection et la différence.

3-1 - L'UNION

Pour faire une union, il suffit de disposer de deux ensembles de données compatibles et d'utiliser le mot clef UNION. La syntaxe est alors :

SELECT ...
UNION
SELECT ...

Bien entendu il est indispensable que les deux ordres SELECT :

- produisent un même nombre de colonnes ;

- que les types de données de chaque paire ordonnée de colonnes soient de même type (ou d'un type équivalent).

3-2 - L'INTERSECTION

La démarche est la même pour faire une intersection, que pour le mécanisme de l'union. La syntaxe utilisant le mot clef INTERSECT :

SELECT ...
INTERSECT
SELECT ...

Avec les mêmes contraintes d'ordre syntaxique.

3-3 - LA DIFFÉRENCE

La différence de deux ensembles s'obtient de la même manière, en utilisant le mot clef EXCEPT :

SELECT ...
EXCEPT
SELECT ...

3-4 - Considérations diverses sur les opérations ensemblistes du SQL

3-4-1 - Le mot clef ALL peut qualifier l'opérateur ensembliste

ALL récupérera tous les doublons des ensembles considérés.
ATTENTION : l'opérateur par défaut opère le dédoublonnage contrairement à la clause SELECT de l'ordre SELECT.
Ainsi lorsque l'on va utiliser une telle construction pour des opérations comptable récupérant des sommes financières en utilisant l'opérateur UNION, on devra impérativement utiliser le mot clef ALL sinon les sommes identiques disparaîtrons.

3-4-2 - On peut restreindre l'intersection à certaines colonnes en utilisant le filtre CORRESPONDING BY

Ainsi on pourra récupérer dans le résultat plus de colonnes que celles qui opèrent la véritable intersection.

3-4-3 - Les opérateurs UNION et INTERSECT sont réflexifs, mais EXCEPT ne l'est pas

Ainsi l'ordre des requêtes de sélection n'a pas d'importance pour les opérations d'union et d'intersection, mais pour la différence cet ordre est essentiel et ne donne pas le même résultat en cas de permutation (c'est le propre de la soustraction et de la division).

3-4-4 - On peut utiliser la clause ORDER BY

La clause ORDER BY doit être la clause finale, car il ne peut y en avoir qu'une.

3-4-5 - Le nom des colonnes du résultat est celui du premier des ordres SELECT composant la globalité de la requête

Autrement dit si les noms des colonnes sont différents entre les divers ordres SELECT s'enchainant avec des opérateurs ensemblistes, ceux qui ont prépondérance sont ceux des colonnes de la première requête SELECT.

3-4-6 - Mis à part l'union, les opérations ensemblistes d'intersection et de différence peuvent être construites à partir de requêtes de base

Exemples :

L'intersection peut être établie par :

SELECT t1.col1, t1.col2 FROM t1
INTERSECT
SELECT t2.col1, t2.col2 FROM t2

SELECT DISTINCT t1.col1, t1.col2
FROM   t1
INNER JOIN t2
ON t1.col1 = t2.col1 AND t1.col2 = t2.col2

SELECT DISTINCT t1.col1, t1.col2
FROM   t1
WHERE  EXISTS (SELECT *
FROM t2
WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2)

La différence peut être établie par :

SELECT t1.col1, t1.col2 FROM t1
EXCEPT
SELECT t2.col1, t2.col2 FROM t2

SELECT DISTINCT t1.col1, t1.col2
FROM   t1
WHERE  NOT EXISTS (SELECT *
FROM t2
WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2)

SELECT DISTINCT t1.col1, t1.col2
FROM   t1
LEFT OUTER JOIN t2
ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
GROUP  BY t1.col1, t1.col2
HAVING COUNT(t2.*) = 0
-- t2.* étant n'importe quelle colonne de t2

3-5 - Quelques exemples

Pour présenter des exemples significatifs, voici le jeu de test que nous allons utiliser :

CREATE TABLE T_OBJET
(OBJ_NOM CHAR(16),
OBJ_PRIX DECIMAL(16,2))

CREATE TABLE T_MACHINE
(MAC_NOM VARCHAR(20),
MAC_PRIX FLOAT (16),
MAC_REF CHAR(8))

INSERT INTO T_OBJET (OBJ_NOM, OBJ_PRIX) VALUES ('MOTO', 43528)
INSERT INTO T_OBJET (OBJ_NOM, OBJ_PRIX) VALUES ('ASSIETTE', 26.5)
INSERT INTO T_OBJET (OBJ_NOM, OBJ_PRIX) VALUES ('LIVRE', 128)
INSERT INTO T_OBJET (OBJ_NOM, OBJ_PRIX) VALUES ('TABLE', 5600)
INSERT INTO T_OBJET (OBJ_NOM, OBJ_PRIX) VALUES ('PERCEUSE', 259.99)

INSERT INTO T_MACHINE (MAC_NOM, MAC_PRIX, MAC_REF) VALUES ('AVION', NULL, 'A320    ')
INSERT INTO T_MACHINE (MAC_NOM, MAC_PRIX, MAC_REF) VALUES ('VENTILATEUR', 250, 'VTL 1200' )
INSERT INTO T_MACHINE (MAC_NOM, MAC_PRIX, MAC_REF) VALUES ('MOTO', 43528, 'YAM R1  ')
INSERT INTO T_MACHINE (MAC_NOM, MAC_PRIX, MAC_REF) VALUES ('RÉVEIL', 128, 'LIP-STAR')
INSERT INTO T_MACHINE (MAC_NOM, MAC_PRIX, MAC_REF) VALUES ('PERCEUSE', 260, 'BD 450A ')

3-5-1 - Exemple d'UNION

Commençons par la basique union des noms des objets et machines de nos deux tables :

Exemple 14

SELECT OBJ_NOM
FROM   T_OBJET
UNION
SELECT MAC_NOM
FROM   T_MACHINE

OBJ_NOM
--------------------
AVION
RÉVEIL
VENTILATEUR
LIVRE
MOTO
ASSIETTE
PERCEUSE
TABLE

Remarquons que la moto, comme la perçeuse ne figurent qu'une seule fois dans le résultat. Ce défaut peut être corrigé par l'emploi du mot clef ALL :

Exemple 15

SELECT OBJ_NOM
FROM   T_OBJET
UNION ALL
SELECT MAC_NOM
FROM   T_MACHINE

OBJ_NOM
--------------------
MOTO
ASSIETTE
LIVRE
TABLE
PERCEUSE
AVION
VENTILATEUR
MOTO
RÉVEIL
PERCEUSE

Voici symbolisé par un dessin, le principe de l'union tel que décrit dans les deux requêtes précédentes :

union de l'ensemble des noms (OBJ_NOM + MAC_NOM) des tables OBJET et MACHINE

3-5-2 - Exemple de DIFFÉRENCE

La différence de l'ensemble des noms des objets par rapport à celui des noms de machine, nous donnera :

Exemple 16

SELECT OBJ_NOM
FROM   T_OBJET
EXCEPT
SELECT MAC_NOM
FROM   T_MACHINE

OBJ_NOM
----------------
ASSIETTE
LIVRE
TABLE

Elle peut aussi être exprimée en l'absence d'opérateur EXCEPT, par :

Exemple 17

SELECT OBJ_NOM
FROM   T_OBJET
WHERE OBJ_NOM NOT IN (SELECT MAC_NOM
FROM   T_MACHINE)

OBJ_NOM
----------------
ASSIETTE
LIVRE
TABLE

Ou encore :

Exemple 18

SELECT O.OBJ_NOM
FROM   T_OBJET O
LEFT OUTER JOIN T_MACHINE M
ON O.OBJ_NOM = M.MAC_NOM
GROUP BY O.OBJ_NOM
HAVING COUNT(M.MAC_NOM) = 0

OBJ_NOM
----------------
ASSIETTE
LIVRE
TABLE

Voici le diagramme de Venn correspondant à la différence tel que les requêtes que nous venons de voir l'expriment :

différence entre les noms des objets et ceux des machines

3-5-3 - Exemple d'INTERSECTION

Quant à l'intersection, celle-ci n'est pas plus difficile :

Exemple 19

SELECT OBJ_NOM
FROM   T_OBJET
INTERSECT
SELECT MAC_NOM
FROM   T_MACHINE

OBJ_NOM
----------------
MOTO
PERCEUSE

Que l'on peut exprimer par l'équivalence logique :

Exemple 20

SELECT OBJ_NOM
FROM T_OBJET O
JOIN T_MACHINE M
ON O.OBJ_NOM = M.MAC_NOM

OBJ_NOM
----------------
MOTO
PERCEUSE

Ou encore :

Exemple 21

SELECT OBJ_NOM
FROM T_OBJET O
WHERE OBJ_NOM IN (SELECT MAC_NOM
FROM T_MACHINE)

OBJ_NOM
----------------
MOTO
PERCEUSE

Et par un dessin reprenant les célèbres patates, le principe de l'intersection tel qu'exprimé décrit dans les requêtes ci-dessus :

intersection de l'ensemble des noms (OBJ_NOM + MAC_NOM) des tables OBJET et MACHINE

Un petit dernier exemple sera consacré à l'intersection avec correspondance. Pour cela nous avons besoin de modifier nos deux tables (ou au moins l'une d'entre elles) de façon à ce que les noms des colonnes des tables soient identiques.
Par exemple, notre nouveau jeu de données pourrait être :

CREATE TABLE T_OBJET2
(NOM CHAR(16),
PRIX DECIMAL(16,2))

CREATE TABLE T_MACHINE2
(NOM VARCHAR(20),
PRIX FLOAT (16),
REF CHAR(8))

INSERT INTO T_OBJET2 (NOM, PRIX) VALUES ('MOTO', 43528)
INSERT INTO T_OBJET2 (NOM, PRIX) VALUES ('ASSIETTE', 26.5)
INSERT INTO T_OBJET2 (NOM, PRIX) VALUES ('LIVRE', 128)
INSERT INTO T_OBJET2 (NOM, PRIX) VALUES ('TABLE', 5600)
INSERT INTO T_OBJET2 (NOM, PRIX) VALUES ('PERCEUSE', 259.99)
INSERT INTO T_OBJET2 (NOM, PRIX) VALUES ('ORDINATEUR', 7000)

INSERT INTO T_MACHINE2 (NOM, PRIX, REF) VALUES ('AVION', NULL, 'A320    ')
INSERT INTO T_MACHINE2 (NOM, PRIX, REF) VALUES ('VENTILATEUR', 250, 'VTL 1200' )
INSERT INTO T_MACHINE2 (NOM, PRIX, REF) VALUES ('MOTO', 43528, 'YAM R1  ')
INSERT INTO T_MACHINE2 (NOM, PRIX, REF) VALUES ('RÉVEIL', 128, 'LIP-STAR')
INSERT INTO T_MACHINE2 (NOM, PRIX, REF) VALUES ('PERCEUSE', 260, 'BD 450A ')
INSERT INTO T_MACHINE2 (NOM, PRIX, REF) VALUES ('ORDINATEUR', 7000, 'PC PII1G')

Dès lors, la requête suivante :

Exemple 22

SELECT *
FROM   T_OBJET
INTERSECT CORRESPONDING
SELECT *
FROM   T_MACHINE

NOM               PRIX
----------------  ----------
MOTO              43 528
ORDINATEUR        7 000

Le mot clef CORRESPONDING se comporte un peu à la manière du NATURAL JOIN et de son mot clef USING, c'est-à-dire qu'en le précisant, SQL va chercher la correspondance des colonnes par leurs position, nom et type.
On peut en outre préciser sur quelles colonnes la correspondance va porter, à l'aide du mot clef BY :

Exemple 23

SELECT *
FROM   T_OBJET
INTERSECT CORRESPONDING BY (NOM)
SELECT *
FROM   T_MACHINE

NOM
----------------
MOTO
ORDINATEUR
PERCEUSE

À noter que l'usage du mot clef CORRESPONDING [BY (liste de colonne)] est accepté dans l'intersection comme dans l'union. Cependant, rares sont les moteurs SQL à l'accepter !

4 - Résumé

Voici les différences entre les moteurs des bases de données :

Paradox

Access

Sybase

SQL Server

Oracle

GROUP BY

Oui

Oui

Oui

Oui

Oui

HAVING

Oui

Oui

Oui

Oui

Oui

UNION

Oui

Oui

Oui

Oui

Oui

INTERSECT

Non

Non

Non

Non

?

EXCEPT

Non

Non

Non

Non

MINUS

CORRESPONDING BY

Non

Non

Non

Non

Non