Les différents types de jointure
Définition
Jointure
En informatique et plus particulièrement dans les bases de données relationnelles, la jointure ou appariement est l’opération permettant d’associer plusieurs tables ou vues de la base par le biais d’un lien logique de données entre les différentes tables ou vues, le lien étant vérifié par le biais d’un prédicat. Le résultat de l’opération est une nouvelle table.
Exemple à partir de la base de données SOINS
L’exemple se base sur l’association entre les patients et leur médecin référent : Clé étrangère (FK) medecin_referent dans PATIENT qui référence la clé primaire (PK) medecin_id dans MEDECIN.
MEDECIN (medecin_id, rpss, nom, prenom, adresse, telephone, specialite)
PATIENT (patient_id, numsecu, genre, date_naissance, nom, prenom, rattachement, medecin_referent) où rattachement référence PATIENT (patient_id) et où medecin_referent référence MEDECIN (medecin_id)
VISITE (medecin, patient, date_visite, prix) où medecin référence MEDECIN (medecin_id) et où patient référence PATIENT (patient_id)
PRESCRIPTION (ordonnance_id, medicament, medecin, patient, date_visite, duree, posologie, modalites) où le triplet d’attributs (medecin, patient, date_visite) référence VISITE (medecin, patient, date_visite)
Liste des patients
select patient_id, nom, prenom, medecin_referent from patient
| patient_id | nom | prenom | medecin_referent |
|---|---|---|---|
| 1 | Dempsey | Justin | 1 |
| 2 | Dempsey | Ellen | 1 |
| 3 | Dempsey | Patrick | 1 |
| 4 | Knight | T.R. | 2 |
| 5 | Knight | Katherine | 1 |
| 6 | Knight | Sandra | 4 |
| 7 | Knight | Kate | |
| 8 | Knight | Isaiah | |
| 9 | Dane | Eric | |
| 10 | Dane | James Jr. | 2 |
| 11 | Ramirez | Sara | 2 |
| 12 | Ramirez | Chandra |
(12 rows)
Il y 12 patients dont 8 possèdent un médecin référent. Note, les champs nuls apparaissaient avec la valeur None dans le TP, ils sont ici vides.
Liste des médecins
select medecin_id, nom, prenom from medecin
| medecin_id | nom | prenom |
|---|---|---|
| 1 | Grey | Meredith |
| 2 | Shepherd | Derek |
| 3 | Karev | Alex |
| 4 | Stevens | Izzie |
| 5 | Bailey | Miranda |
| 6 | Webber | Richard |
| 7 | Yang | Cristina |
| 8 | Omalley | George |
| 9 | Burke | Preston |
| 10 | Montgomery-Shepherd | Addison |
| 11 | Torres | Callie |
| 12 | Sloan | Mark |
(12 rows)
Il y également 12 médecins. Remarque : comme seulement 8 des 12 patients possèdent un médecin référent, on est sûr que certains médecins ne sont pas médecins référents pour des patients.
Jointure interne pour afficher les médecins référents des patients (question 8 du TP requêtes simples)
select patient.nom nom_patient, patient.prenom prenom_patient, medecin.nom nom_medecin
from patient inner join medecin
on patient.medecin_referent=medecin.medecin_id
| nom_patient | prenom_patient | nom_medecin |
|---|---|---|
| Dempsey | Justin | Grey |
| Dempsey | Ellen | Grey |
| Dempsey | Patrick | Grey |
| Knight | T.R. | Shepherd |
| Knight | Katherine | Grey |
| Knight | Sandra | Stevens |
| Dane | James Jr. | Shepherd |
| Ramirez | Sara | Shepherd |
(8 rows)
Remarque : le mot clé inner utilisé dans la syntaxe de la jointure est facultatif, patient join medecin aurait donné le même résultat.
Les jointures internes sont les jointures les plus communes, elles renvoient pour résultat l’intersection entre l’ensemble des patients et l’ensemble des médecins, c’est à dire quand le médecin référent est renseigné donc non nul. Le résultat fait 8 lignes correspondant aux 8 patients possédant un médecin référent.
Dans les schémas fournis dans le TP requêtes avancées, ceci correspond au schéma suivant :
Jointure externe (à gauche)
select patient.nom nom_patient, patient.prenom prenom_patient, medecin.nom nom_medecin
from patient left outer join medecin
on patient.medecin_referent=medecin.medecin_id
| nom_patient | prenom_patient | nom_medecin |
|---|---|---|
| Dempsey | Justin | Grey |
| Dempsey | Ellen | Grey |
| Dempsey | Patrick | Grey |
| Knight | T.R. | Shepherd |
| Knight | Katherine | Grey |
| Knight | Sandra | Stevens |
| Knight | Kate | |
| Knight | Isaiah | |
| Dane | Eric | |
| Dane | James Jr. | Shepherd |
| Ramirez | Sara | Shepherd |
| Ramirez | Chandra |
(12 rows)
Remarque : le mot clé outer utilisé dans la syntaxe de la jointure est facultatif, patient left join medecin aurait donné le même résultat.
Cette requête est quasiment identique à la précédente mais on a effectué une jointure externe à gauche : left join. Le résultat de la jointure interne est inclus dans ce résultat mais on y trouve aussi les 4 patients qui n’ont pas de médecin référent, en gras dans le résultat ci-dessus. On a 12 lignes qui correspondent à l’intégralité des patients.
Dans les schémas fournis dans le TP requêtes avancées, ceci correspond au schéma suivant :
Jointure externe (à droite)
select patient.nom nom_patient, patient.prenom prenom_patient, medecin.nom nom_medecin
from patient right outer join medecin
on patient.medecin_referent=medecin.medecin_id
| nom_patient | prenom_patient | nom_medecin |
|---|---|---|
| Dempsey | Justin | Grey |
| Dempsey | Ellen | Grey |
| Dempsey | Patrick | Grey |
| Knight | T.R. | Shepherd |
| Knight | Katherine | Grey |
| Knight | Sandra | Stevens |
| Dane | James Jr. | Shepherd |
| Ramirez | Sara | Shepherd |
| Torres | ||
| Sloan | ||
| Montgomery-Shepherd | ||
| Bailey | ||
| Omalley | ||
| Webber | ||
| Karev | ||
| Burke | ||
| Yang |
(17 rows)
La requête est toujours quasiment identique à celle de la jointure interne mais on a effectué une jointure externe à droite : right join. Le résultat de la jointure interne est toujours inclus dans ce résultat mais on y trouve aussi les médecins qui ne sont référents d’aucun patient. On a 17 lignes qui correspondent aux 9 médecins sans patient et autant d’associations medecin/patient : 4 x pour Grey + 3 x pour Shepherd + 1 x pour Stevens.
Dans les schémas fournis dans le TP requêtes avancées, ceci correspond au schéma suivant :
ATTENTION : les termes right et left sont relatifs à l’ordre d’apparition des tables indiquées dans la requête et qui participent à la jointure. Ainsi patient right join medecin donnera le même résultat que medecin left join patient !
Jointure externe (à gauche et à droite)
select patient.nom nom_patient, patient.prenom prenom_patient, medecin.nom nom_medecin
from patient full outer join medecin
on patient.medecin_referent=medecin.medecin_id
| nom_patient | prenom_patient | nom_medecin |
|---|---|---|
| Dempsey | Justin | Grey |
| Dempsey | Ellen | Grey |
| Dempsey | Patrick | Grey |
| Knight | T.R. | Shepherd |
| Knight | Katherine | Grey |
| Knight | Sandra | Stevens |
| Knight | Kate | |
| Knight | Isaiah | |
| Dane | Eric | |
| Dane | James Jr. | Shepherd |
| Ramirez | Sara | Shepherd |
| Ramirez | Chandra | |
| Torres | ||
| Sloan | ||
| Montgomery-Shepherd | ||
| Bailey | ||
| Omalley | ||
| Webber | ||
| Karev | ||
| Burke | ||
| Yang |
(21 rows)
C’est ici tout simplement la combinaison de la jointure externe à gauche et de la jointure externe à droite. La contrainte de l’ordre d’apparition des tables dans la requête se s’applique évidemment plus.
Dans les schémas fournis dans le TP requêtes avancées, ceci correspond au schéma suivant :
Vous trouverez beaucoup de sites Web et de livres vous expliquant les différents types de jointures. Celui-ci, en français, est plutôt correct et vous pourrez aller encore plus loin si vous le désirez : https://sql.sh/cours/jointures