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

Date de publication : 26/08/2003

Par SQLPro (autres articles) (CV)
 

niveau : débutant

Le groupage et les requêtes portants 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.


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 BROUP 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 chambre 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 le 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, outes 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'un 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 chmabre 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 couchage 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 tenter 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 imanquablement 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'autre 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'occurence, 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é innocupées plus de 10 jours au cours du mois de janvier 2000...

Pour calculer le nombre de jour ou 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 eut un chiffre d'affaire inférieur à 15 000 F HT au cours de l'année 2000.
Une première approche consisterais par exemple a 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, 3eme 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 fitre HAVING en retenant l'expression si elle vaut moins de 15000 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 le 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. A 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 le 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 paires ordonnées 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 la 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 telle que les requêtes que nous venons, l'exprime :

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


3.5.3. Exemple d'INTERSECTION

Quand à 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 reprennant 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 a 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 correpondance des colonnes par leur 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
A noter que l'usage du mot clef CORRESPONDING [BY (liste de colonne)] est accepté dans l'intersection comme dans l'union. Cependant, rare sont les moteurs SQL a 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

Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

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'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérêts. Cette page est déposée à la SACD.

Vos questions techniques : forum d'entraide SGBD & SQL - Publiez vos articles, tutoriels et cours
et rejoignez-nous dans l'équipe de rédaction du club d'entraide des développeurs francophones
Nous contacter - Hébergement - Participez - Copyright © 2000-2009 www.developpez.com - Legal informations.