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

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 »

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…

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.

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 :

 

Sélectionnez

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 :

 

Sélectionnez

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 :

 

Sélectionnez

SELECT ...
EXCEPT
SELECT ...

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

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)

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 :

 

Sélectionnez

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

Sélectionnez SELECT OBJ_NOM FROM T_OBJET UNION SELECT MAC_NOM FROM T_MACHINE

Sélectionnez 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

Sélectionnez SELECT OBJ_NOM FROM T_OBJET UNION ALL SELECT MAC_NOM FROM T_MACHINE

Sélectionnez 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

Sélectionnez SELECT OBJ_NOM FROM T_OBJET EXCEPT SELECT MAC_NOM FROM T_MACHINE

Sélectionnez OBJ_NOM ---------------- ASSIETTE LIVRE TABLE

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

Exemple 17

Sélectionnez SELECT OBJ_NOM FROM T_OBJET WHERE OBJ_NOM NOT IN (SELECT MAC_NOM FROM T_MACHINE)

Sélectionnez OBJ_NOM ---------------- ASSIETTE LIVRE TABLE

Ou encore :

Exemple 18

Sélectionnez 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

Sélectionnez 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

Sélectionnez SELECT OBJ_NOM FROM T_OBJET INTERSECT SELECT MAC_NOM FROM T_MACHINE

Sélectionnez OBJ_NOM ---------------- MOTO PERCEUSE

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

Exemple 20

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

Sélectionnez OBJ_NOM ---------------- MOTO PERCEUSE

Ou encore :

Exemple 21

Sélectionnez SELECT OBJ_NOM FROM T_OBJET O WHERE OBJ_NOM IN (SELECT MAC_NOM FROM T_MACHINE)

Sélectionnez 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 :

 

Sélectionnez

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

Sélectionnez SELECT * FROM T_OBJET INTERSECT CORRESPONDING SELECT * FROM T_MACHINE

Sélectionnez 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

Sélectionnez SELECT * FROM T_OBJET INTERSECT CORRESPONDING BY (NOM) SELECT * FROM T_MACHINE

Sélectionnez 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 !

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