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.
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 ... |
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 ... |
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 ... |
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 ... |
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 ... |
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 ... |
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 ... |
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 |
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 |
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 |
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 !
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 :
SELECT [DISTINCT ou ALL]
*
ou liste de colonnes
FROM laTable t1, laTable t2 WHERE t1.laClef = t2.laPseudoClefEtrangère |
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