ATTENTION : le contenu des ces pages web est soumis aux dispositions légales françaises et internationales en matière de droits d'auteurs et de copyright.

NOTA : ces pages sont encore en construction !!! Merci d'être indulgent ...

LE SQL de A à Z

3eme partie

Dans le précédent article nous avons commencer à décortiquer le simple SELECT. Dans le présent, nous allons nous consacrer aux jointures entre tables ainsi quà la clause HAVING puis aux opérateurs ensemblistes.
 

Courriel
Par Frédéric BROUARD
 

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 Les Jointures ou comment faire des requêtes sur plusieurs tables

Les jointures permettent d'exploiter pleinement le modèle relationnel des tables d'une base de données.
Elle sont faites pour mettre en relation deux (ou plus) tables concourant à rechercher la réponse à des interrogations. Une jointure permet donc de combiner les colonnes de plusieurs tables.

Il existe en fait différentes natures de jointures que nous expliciterons plus en détail. Retenez cependant que la plupart des jointures entre tables s'effectuent en imposant l'égalité des valeurs d'une colonne d'une table à une colonne d'une autre table. On parle alors de jointure naturelle ou équi-jointure. Mais on trouve aussi des jointures d'une table sur elle même. On parle alors d'auto-jointure. Enfin il arrive que l'on doive procéder à des jointures externe, c'est à dire joindre une table à une autre, même si la valeur de liaison est absente dans une table ou l'autre. Enfin, dans quelques cas, on peut procéder à des jointures hétérogènes, c'est à dire que l'on remplace le critère d'égalité par un critère d'inégalité ou de différence. Nous verrons au moins un cas de cet espèce.

Une jointure entre tables peut être mise en oeuvre, soit à l'aide des éléments de syntaxe SQL que nous avons déjà vu, soit à l'aide d'une clause spécifique du SQL, la clause JOIN. Nous allons commencer par voir comment à l'aide du SQL de base nous pouvons exprimer une jointure.
 

1.1 Retour sur la clause WHERE et le FROM

Rappel de la syntaxe du SELECT :
 
SELECT [DISTINCT ou ALL] * ou liste de colonnes FROM nom des tables ou des vues

C'est ici le pluriel du la partie FROM qui change tout...

Tâchons donc de récupérer les n° des téléphones associés aux clients

exemple 2.1
SELECT CLI_NOM, TEL_NUMERO
FROM T_CLIENT, T_TELEPHONE
CLI_NOM  TEL_NUMERO
-------  --------------
DUPONT   01-45-42-56-63
DUPONT   01-44-28-52-52
DUPONT   01-44-28-52-50
DUPONT   06-11-86-78-89
DUPONT   02-41-58-89-52
DUPONT   01-51-58-52-50
DUPONT   01-54-11-43-21
DUPONT   06-55-41-42-95
DUPONT   01-48-98-92-21
DUPONT   01-44-22-56-21
...
Cette requête ne possède pas de critère de jointure entre une table et l'autre. Dans ce cas, le compilateur SQL calcule le produit cartésien des deux ensembles, c'est à dire qu'à chaque ligne de la première table, il accole l'ensemble des lignes de la seconde à la manière d'une "multiplication des petits pains" ! Nous verrons qu'il existe une autre manière, normalisée cette fois, de générer ce produit cartésien. Mais cette requête est à proscrire. Dans notre exemple elle génère 17 400 lignes...

Il faut donc définir absolument un critère de jointure.

Décidons par exemple de retrouver quelles chambres, de quels hôtels ont été occupés par nos clients :
exemple 2.2
SELECT CLI_NOM, TEL_NUMERO
FROM T_CLIENT, T_TELEPHONE
WHERE CLI_ID = CLI_ID
CLI_NOM  TEL_NUMERO
-------  --------------
DUPONT   01-45-42-56-63
DUPONT   01-44-28-52-52
DUPONT   01-44-28-52-50
DUPONT   06-11-86-78-89
DUPONT   02-41-58-89-52
DUPONT   01-51-58-52-50
DUPONT   01-54-11-43-21
DUPONT   06-55-41-42-95
DUPONT   01-48-98-92-21
DUPONT   01-44-22-56-21
...
Nous n'avons pas fait mieux que la précédente, parceque dans la clause WHERE, on ne sait pas de quelle table provient la colonne CLI_ID.
Il est nécessaire d'indiquer au compilateur la provenance de chacune des colonnes NUM_CLI et donc d'opérer une distinction entre l'une et l'autre colonne.
En fait il suffit d'utiliser une notation pointée pour différencier les colonnes. Ainsi, chaque colonne devra être précédée du nom de la table, suivi d'un point.
exemple 2.3
SELECT CLI_NOM, TEL_NUMERO
FROM T_CLIENT, T_TELEPHONE
WHERE T_CLIENT.CLI_ID = T_TELEPHONE.CLI_ID
CLI_NOM   TEL_NUMERO
-------   --------------
DUPONT    01-45-42-56-63
DUPONT    01-44-28-52-52
DUPONT    01-44-28-52-50
BOUVIER   06-11-86-78-89
DUBOIS    02-41-58-89-52
DREYFUS   01-51-58-52-50
DUHAMEL   01-54-11-43-21
BOYER     06-55-41-42-95
MARTIN    01-48-98-92-21
MARTIN    01-44-22-56-21
...
On tombe ici à 174 enregistrements dans la table !!!

Mais il existe une autre façons de faire, plus simple encore. On utilise la technique du "surnommage", c'est à dire que l'on attribut un surnom à chacune des tables présente dans la partie FROM du SELECT :
exemple 2.4
SELECT CLI_NOM, TEL_NUMERO
FROM T_CLIENT C, T_TELEPHONE T
WHERE C.CLI_ID = T.CLI_ID
CLI_NOM   TEL_NUMERO
-------   --------------
DUPONT    01-45-42-56-63
DUPONT    01-44-28-52-52
DUPONT    01-44-28-52-50
BOUVIER   06-11-86-78-89
DUBOIS    02-41-58-89-52
DREYFUS   01-51-58-52-50
DUHAMEL   01-54-11-43-21
BOYER     06-55-41-42-95
MARTIN    01-48-98-92-21
MARTIN    01-44-22-56-21
...
Ici, la table T_CLIENT a été surnommée "C" et la table T_TELEPHONE "T".

Bien entendu, et comme dans les requêtes monotabulaires ont peut poser des conditions supplémentaires de filtrage dans la clause where. Cherhons par exemple les client dont les numéros de téléphone correspondent à un fax :
exemple 2.5
SELECT CLI_NOM, TEL_NUMERO
FROM T_CLIENT C, T_TELEPHONE T
WHERE C.CLI_ID = T.CLI_ID
  AND TYP_CODE = 'FAX' 
CLI_NOM   TEL_NUMERO
-------   --------------
DUPONT    01-44-28-52-50
MARTIN    01-44-22-56-21
DUHAMEL   01-54-11-43-89
DUPONT    05-59-45-72-42
MARTIN    01-47-66-29-55
DUBOIS    04-66-62-95-64
DREYFUS   04-92-19-18-58
DUHAMEL   01-55-60-93-81
PHILIPPE  01-48-44-86-19
DAUMIER   01-48-28-17-95
...

Le fait de placer comme critère de jointure entre les tables, l'opérateur logique "égal" donne ce que l'on apelle une "équi-jointure".

REMARQUE IMPORTANTE
Comme vous pouvez le constater, le nom du client BOUVIER n'apparait pas. Il n'a pas été "oublié" par le traitement de la requête, mais le numéro de fax de ce client n'est pas présent dans la table T_TELEPHONE. Or le moteur SQL recherche les valeurs de la jointure par égalité. Comme l'ID_CLI de BOUVIER n'est pas présent dans la table T_TELEPHONE, il ne peut effectuer la jointure et ignore donc la ligne concernant le client BOUVIER. Nous verrons comment réparer cette lacune lorsque nous parlerons des jointures externes.

NOTA : on peut aussi utiliser les surnoms dans la partie qui suit immédiatement le mot clef SELECT. Ainsi l'exemple 2.4, peut aussi c'écrire :
exemple 2.6
SELECT C.CLI_ID, C.CLI_NOM, T.TEL_NUMERO
FROM T_CLIENT C, T_TELEPHONE T
WHERE C.CLI_ID = T.CLI_ID
  AND T.TYP_CODE = 'FAX' 
CLI_ID  CLI_NOM   TEL_NUMERO
------  -------   --------------
     1  DUPONT    01-44-28-52-50
    10  MARTIN    01-44-22-56-21
     8  DUHAMEL   01-54-11-43-89
     1  DUPONT    05-59-45-72-42
     2  MARTIN    01-47-66-29-55
     4  DUBOIS    04-66-62-95-64
     5  DREYFUS   04-92-19-18-58
     8  DUHAMEL   01-55-60-93-81
    13  PHILIPPE  01-48-44-86-19
    15  DAUMIER   01-48-28-17-95
...
C'est particulièrement pratique lorsque l'on veut récupérer une colonne qui se retrouve dans les deux tables, ce qui est souvent le cas de la (ou les) colonne de clef étrangère qui permet juutement d'assurer la jointure.

Pour joindre plusieurs tables, on peut utiliser le même processus de manière répétitive...
exemple 2.7
SELECT C.CLI_ID, C.CLI_NOM, T.TEL_NUMERO, E.EML_ADRESSE, A.ADR_VILLE
FROM T_CLIENT C, T_TELEPHONE T, T_ADRESSE A, T_EMAIL E
WHERE C.CLI_ID = T.CLI_ID
  AND C.CLI_ID = A.CLI_ID
  AND C.CLI_ID = E.CLI_ID
CLI_ID   CLI_NOM  TEL_NUMERO      EML_ADRESSE                        ADR_VILLE
------  --------  --------------  -----------------------            ---------
     1  DUPONT    01-45-42-56-63  alain.dupont@wanadoo.fr            VERSAILLES
     1  DUPONT    05-59-45-72-42  alain.dupont@wanadoo.fr            VERSAILLES
     1  DUPONT    05-59-45-72-24  alain.dupont@wanadoo.fr            VERSAILLES
     1  DUPONT    01-44-28-52-50  alain.dupont@wanadoo.fr            VERSAILLES
     1  DUPONT    01-44-28-52-52  alain.dupont@wanadoo.fr            VERSAILLES
     2  MARTIN    01-47-66-29-29  mmartin@transports_martin_fils.fr  VERGNOLLES CEDEX 452
     2  MARTIN    01-47-66-29-55  mmartin@transports_martin_fils.fr  VERGNOLLES CEDEX 452
     2  MARTIN    01-47-66-29-29  plongeur@aol.com                   VERGNOLLES CEDEX 452
     2  MARTIN    01-47-66-29-55  plongeur@aol.com                   VERGNOLLES CEDEX 452
     5  DREYFUS   04-92-19-18-58  pdreyfus@club-internet.fr          PARIS
...

MAIS ATTENTION ! De même que nous l'avons vu dans l'exemple 2.4, ne sont visible ici que les lignes clients ayant A LA FOIS, au moins une adresse, un e mail et au moins un numéro de téléphone... Si nous avions voulu une liste complète des clients avec toutes les coordonnées disponibles, nous aurions du faire une requête externe sur les tables...

1.2   Différents type de jointures (naturelles, équi, non equi, auto, externes, hétérogènes, croisée et union)

Lorsque nous étudions le modèle relationnel de notre base de données exemple nous avons vu que le modèle physique des données, répercute les clefs des tables maîtres en tant que clefs étrangères des tables pour lesquelles une jointure est nécessaire. En utilisant la jointure entre clefs primaires et clefs secondaires basée sur l'égalité des valeurs des colonnes nous exécutons ce que les professionnels du SQL appelle une jointure naturelle. Il est aussi possible de faire des équi-jointures qui ne sont pas naturelles, soit par accident (une erreur !), soit par nécessité. Il est aussi possible de faire des non équi-jointures, c'est à dire des jointures basée sur un critère différent de l'égalité, mais aussi des auto-jointures, c'est à dire de joindre la table sur elle même. Le cas le plus délicat à comprendre est celui des jointures externes, c'est à dire exiger que le résultat comprenne toutes les lignes des tables (ou d'au moins une des tables de la jointure), même s'il n'y a pas correspondance des lignes entre les différentes tables mise en oeuvre dans la jointure. La jointure d'union consiste à ajouter toutes les données des deux tables à condition qu'elles soient compatibles dans leurs structures.  Enfin on peut effectuer des requêtes hétérogènes, c'est à dire de joindre une table d'une base de données, à une ou plusieurs autres base de données eventuellement même sur des serveurs différents, voire même sur des serveurs de différents types (par exemple joindre une table T_CLIENT de la base BD_COMMANDE d'un serveur Oracle à la table T_PROSPECT de la base BD_COMMERCIAL d'un serveur Sybase !).

1.2.1 opérateur de jointure naturelle 

Il existe un opérateur normalisé pour effectué en SQL la jointure naturelle des tables :
 
SELECT [DISTINCT ou ALL] * ou liste de colonnes
FROM table1 NATURAL JOIN table2 [USING (colonne1 [, colonne2 ...])]

L'opérateur NATURAL JOIN permet d'éciter de préciser les colonnes concernées par la jointure.
Dans ce cas, le compilateur SQL va rechercher dans les 2 tables, les colonnes dont le nom est identique. Bien entendu, le type de données doit être le même !
NOTA : on veillera au niveau de la modélisation et notamment au niveau du MPD (Modèle Physique de Données) que les noms des colonnes de clefs en relation avec d'autres tables par l'intermédiaires des clefs étrangères soient strictement identiques.

exemple 2.8
SELECT CLI_NOM, TEL_NUMERO
FROM T_CLIENT NATURAL JOIN T_TELEPHONE
CLI_NOM  TEL_NUMERO
-------  --------------
DUPONT    01-45-42-56-63
DUPONT    01-44-28-52-52
DUPONT    01-44-28-52-50
BOUVIER   06-11-86-78-89
DUBOIS    02-41-58-89-52
DREYFUS   01-51-58-52-50
DUHAMEL   01-54-11-43-21
BOYER     06-55-41-42-95
MARTIN    01-48-98-92-21
MARTIN    01-44-22-56-21
...
Mais cette syntaxe est rarement accepté par les moteurs SQL actuels !

La partie optionnelle "USING" permet de restreindre les colonnes concernées, lorsque plusieurs colonnes servent à définir la jointure naturelle. Ainsi la commande SQL :
SELECT CLI_NOM, TEL_NUMERO
FROM T_CLIENT NATURAL JOIN T_TELEPHONE USING (CLI_ID)
Revient au même que la commande SQL de l'exemple 2.8.

1.2.2 Équi-jointure
L'équi jointure consiste à opérer une jointure avec une condition d'égalité. Cette condition d'égalité dans la jointure peut ne pas porter nécessairement sur les clefs (primaires et étrangères).
Recherchons par exemple les clients dont le nom est celui d'une ville contenu dans la table des adresses :
exemple 2.9
SELECT DISTINCT C.CLI_ID, C.CLI_NOM, A.ADR_VILLE
FROM T_CLIENT C,  T_ADRESSE A
WHERE C.CLI_NOM = A.ADR_VILLE
CLI_ID  CLI_NOM  ADR_VILLE
------  -------  ---------
    92  PARIS    PARIS
...

Nous avons donc bien réalisé une équi jointure, mais elle n'est pas naturelle parce qu'elle ne repose pas sur les clefs des tables.

Bien entendu, il existe un opérateur normalisé SQL 2 permettant de traiter le cas de l'équi-jointure : 
 
SELECT [DISTINCT ou ALL] * ou liste de colonnes
FROM table1 [INNER] JOIN table2 ON condition de jointure

Le mot clef INNER n'étant pas obligatoire, mais voulant s'opposer au mot clef OUTER que nous verrons plus loin.
Ainsi, la requête précédente, s'écrit à l'aide de cette syntaxe :
exemple 2.9 bis
SELECT DISTINCT C.CLI_ID, C.CLI_NOM, A.ADR_VILLE
FROM T_CLIENT C INNER JOIN  T_ADRESSE A ON C.CLI_NOM = A.ADR_VILLE
CLI_ID  CLI_NOM  ADR_VILLE
------  -------  ---------
    92  PARIS    PARIS
...

 

1.2.3 Non équi-jointure

Il s'agit là d'utiliser n'importe quelle condition de jointure entre deux tables, exceptée la stricte égalité. Ce peuvent être les conditions suivantes :
 
> supérieur
>= supérieur ou égal
< inférieur
<= inférieur ou égal
<> différent de 
IN dans un ensemble
LIKE correspondance partielle
BETWEEN entre deux valeurs
EXISTS dans une table

En règle générale ou trouve des non équi-jointures dans le cadre de comparaisons temporelles ou de mesures physiques. Par exemple on pourrait rechercher une pièce mécanique dans un stock qui soit de même nature ou de même fonction qu'une pièce donnée, mais plus légère...

Nous voudons obtenir les factures qui ont été émises avant que le prix des petits déjeunés n'atteigne 45 F...
exemple 2.10
SELECT F.*
FROM   T_FACTURE F,  T_TARIF T
WHERE  F.FAC_DATE < T.TRF_DATE_DEBUT
  AND  TRF_PETIT_DEJEUNE <= 45
FAC_ID  CLI_ID  PMT_CODE  FAC_DATE    FAC_PMT_DATE
------  ------  --------  ----------  ------------
     1       1  CB        1999-01-31  1999-02-14
     3       1  CHQ       1999-02-28  1999-03-12
     5       1  CB        1999-03-31  1999-04-23
     7       1  CHQ       1999-04-30  1999-05-14
     9       1  CHQ       1999-05-31  1999-06-14
    11       1  CB        1999-06-30  1999-07-14
    13       1  CHQ       1999-07-31  1999-08-12
    15       1  CB        1999-08-31  1999-09-23
    25       2  CB        1999-01-31  1999-02-23
    27       2  CHQ       1999-02-28  1999-03-18
...
ou encore en utilisant l'opérateur normalisé :
SELECT F.*
FROM   T_FACTURE F INNER JOIN T_TARIF T ON F.FAC_DATE < T.TRF_DATE_DEBUT
WHERE  TRF_PETIT_DEJEUNE <= 45
NOTA : pour récupérer toutes les colonnes d'une table, on peut utiliser l'opérateur * suffixé par le nom de table, comme nous l'avons fait ici pour la table des factures.

Si notre table des tarifs avait été organisée par tranches, comme ceci :
TRF_DATE_DEBUT  TRF_DATE_FIN  TRF_TAUX_TAXES  TRF_PETIT_DEJEUNE
--------------  ------------  --------------  -----------------
1999-01-01      1999-08-31             18,60            40,00 F
1999-09-01      1999-12-31             20,60            45,00 F
2000-01-01      2000-08-31             20,60            50,00 F
2000-09-01      2000-12-31             20,60            55,00 F
2001-01-01      2001-12-31             20,60            60,00 F
alors,récupérer le tarif des chambres pour chacune des dates du planning devient un exercice très simple :
exemple 2.11
SELECT CPC.CHB_ID, CPC.PLN_JOUR, TC.TRF_CHB_PRIX
FROM   TJ_CHB_PLN_CLI CPC,  T_TARIF T, TJ_TRF_CHB TC
WHERE  CPC.PLN_JOUR BETWEEN T.TRF_DATE_DEBUT AND T.TRF_DATE_FIN
  AND  T.TRF_DATE_DEBUT = TC.TRF_DATE_DEBUT
  AND  CPC.CHB_ID = TC.CHB_ID
CHB_ID  PLN_JOUR    TRF_CHB_PRIX
------  ----------  ------------
     1  2000-01-22      264,00 F
     1  2000-01-28      264,00 F
     1  1999-01-02      220,00 F
     1  1999-01-03      220,00 F
     1  1999-01-05      220,00 F
     1  1999-01-07      220,00 F
     1  1999-01-09      220,00 F
     1  1999-01-10      220,00 F
     1  1999-01-11      220,00 F
     1  1999-01-13      220,00 F
...
ou encore en utilisant l'opérateur normalisé :
SELECT CPC.CHB_ID, CPC.PLN_JOUR, TC.TRF_CHB_PRIX
FROM TJ_CHB_PLN_CLI CPC 
     INNER JOIN T_TARIF T     ON CPC.PLN_JOUR BETWEEN T.TRF_DATE_DEBUT AND T.TRF_DATE_FIN
     INNER JOIN TJ_TRF_CHB TC ON T.TRF_DATE_DEBUT = TC.TRF_DATE_DEBUT
WHERE CPC.CHB_ID = TC.CHB_ID
Nous avons donc à nouveau un exemple remarquable de non equi-jointure.

Remarque : Constatons que la colonne TRF_DATE_FIN de cette nouvelle version de la table des tarifs implique une redondance de l'information. En effet cette date de fin est déductible de la date de début de la ligne qui contient la date immédiatement postérieure avec un jour de moins. De plus le problème induit par cette organisation des données fait qu'il faut obligatoirement définir une date de fin des tarifs, même dans le futur, sinon certaines tarifications ne pourront être établies par cette requête... Il ne s'agit donc pas d'une modélisation correcte !

1.2.4 Auto jointure

Le problème consiste à joindre une table à elle même. Il est assez fréquent que l'on ait besoin de telles auto jointures car elle permettent notamment de modéliser des structures de données complexes comme des arbres. Voici quelques exemples de relation nécessitant une auto jointure de tables :

La représentation d'une telle jointure dans le modèle de données, se fait par le rajout d'une colonne contenant une pseudo clef étrangère basée sur le clef de la table.
Dans ce cas, une syntaxe possible pour l'auto jointure est la suvante :
 
SELECT [DISTINCT ou ALL] * ou liste de colonnes
FROM laTable t1, laTable t2
WHERE t1.laClef = t2.laPseudoClefEtrangère
C'est l'exemple typique ou l'utilisation de surnoms pour les tables est obligatoire, sinon il y a risque de confusion pour le moteur SQL...
On peut bien entendu utiliser la syntaxe normalisée de jointure du SQL 2.

Pour donner un exemple concret à nos propos nous allons modéliser le fait qu'une chambre puisse communiquer avec une autre (par une porte). Dès lors, le challenge est de trouver quelles sont les chambres qui communiquent entre elles par exemple pour réaliser une sorte de suite...Pour ce faire, nous allons ajouter à notre table des chambres une colonne de clef étrangère basée sur la clef de la table.
Dans ce cas, cette colonne doit obligatoirement accepter des valeurs n
 

précédente