Jointures
Les jointures (joins en anglais) permettent de combiner des enregistrements de plusieurs tables d'une base de données en fonction d'une condition définie. Elles sont utilisées lorsque l'on veut extraire des données provenant de plusieurs sources qui partagent un lien commun, souvent une clé étrangère (foreign key).
Le type de jointure utilisé dépend du résultat attendu et de la structure des données.
Jointure interne
INNER JOIN est le type de jointure le plus courant. Il ne renvoie que les lignes qui ont des correspondances exactes dans les deux tables gauche et droite.
SELECT gauche.colonne, droite.colonne
FROM gauche
INNER JOIN droite ON gauche.id = droite.id;
Dans cet exemple, seules les lignes qui ont le même id dans gauche et droite seront incluses dans le résultat.
Jointure externe gauche
La jointure LEFT JOIN ou LEFT OUTER JOIN renvoie toutes les lignes de la table de gauche, même si elles n'ont pas de correspondance dans la table de droite. Si aucune correspondance n'est trouvée, les colonnes de la table de droite contiendront des valeurs NULL.
SELECT gauche.colonne, droite.colonne
FROM gauche
LEFT JOIN droite
ON gauche.id = droite.id;
Jointure externe droite
RIGHT JOIN ou RIGHT OUTER JOIN est l'inverse de la jointure externe gauche. Elle renvoie toutes les lignes de la table de droite et les lignes correspondantes de la table de gauche. Si une ligne de la table de droite n'a pas de correspondance dans la table de gauche, les colonnes de la table de gauche auront des valeurs NULL.
SELECT gauche.colonne, droite.colonne
FROM gauche
RIGHT JOIN droite
ON gauche.id = droite.id;
Jointure externe complète
FULL JOIN ou FULL OUTER JOIN combine les fonctionnalités des jointures gauche et droite. Elle renvoie toutes les lignes des deux tables, avec des valeurs NULL là où il n'y a pas de correspondance dans l'autre table. Toutes les lignes des deux tables sont conservées, qu’il y ait correspondance ou pas.
SELECT gauche.colonne, droite.colonne
FROM gauche
FULL JOIN droite
ON gauche.id = droite.id;
Le fait d’avoir des NULL des deux côtés est le cœur de l’intérêt du FULL OUTER JOIN :
Cela permet d’obtenir tous les enregistrements présents dans l’une ou l’autre table, même quand il n’existe aucune intersection.
Utile pour comparer des ensembles de données et voir ce qui est :
- présent uniquement à gauche,
- présent uniquement à droite,
- présent dans les deux.
C'est jointure est utilisée pour:
- des comparaisons d’inventaires (ex. produits d’un magasin vs produits d’un fournisseur).
- la synchronisation de données (trouver ce qui manque d’un côté ou de l’autre).
- de l'audit de cohérence (détecter des écarts entre deux tables censées contenir la même information).
Jointure d'exclusion
Un jointure d'exclusion à gauche (left anti-join) garde uniquement les lignes pour lesquelles il n’y a pas de correspondance (c’est-à-dire que les colonnes de la table de droite sont NULL).
SELECT gauche.colonne, droite.colonne
FROM gauche
LEFT JOIN droite
ON gauche.id = droite.id
WHERE droite.id IS NULL;
Cela permet de trouver les lignes de gauche sans correspondance dans la table de droite.
Un jointure d'exclusion à droite (right anti-join) garde uniquement les lignes pour lesquelles il n’y a pas de correspondance (c’est-à-dire que les colonnes de la table de gauche sont NULL).
SELECT gauche.colonne, droite.colonne
FROM gauche
RIGHT JOIN droite
ON gauche.id = droite.id
WHERE gauche IS NULL;
Exercice : Lister les articles dont la famille n'existe pas.
select * from article
left join famille on famille.code = article.famille_code
where famille.code is null
>> MENMA Plant Menthe Pomme
Attention ! La valeur null ne peut être comparée à rien. L'égalité avec null retourne ni vrai ni faux mais null. Ainsi null n'est pas égal à null. Pour la comparaison avec null il faut utiliser les mots-clés is ou is not.
Jointure croisée ou produit cartésien
La jointure croisée renvoie le produit cartésien des deux tables. Chaque ligne de la première table est combinée avec chaque ligne de la deuxième table. Cela produit un très grand nombre de lignes de résultat.
SELECT gauche.colonne, droite.colonne
FROM gauche
CROSS JOIN droite;
Cela peut être utile dans des situations spécifiques mais doit être utilisé avec prudence car cela peut générer des résultats volumineux.
Table Gauche (3 lignes) :
| id | couleur |
|---|---|
| 1 | Rouge |
| 2 | Vert |
| 3 | Bleu |
Table Droite (2 lignes) :
| taille |
|---|
| M |
| XL |
Résultat (3 × 2 = 6 lignes) :
| id | couleur | taille |
|---|---|---|
| 1 | Rouge | M |
| 1 | Rouge | XL |
| 2 | Vert | M |
| 2 | Vert | XL |
| 3 | Bleu | M |
| 3 | Bleu | XL |
Jointure naturelle
La jointure naturelle ne spécifie pas la ou les colonnes de jointure. Cette jointure s’effectue à la condition qu’il y ai des colonnes de même nom et de même type dans les 2 tables.
SELECT *
FROM gauche
NATURAL JOIN droite
Ancienne syntaxe
L'ancienne syntaxe qui date de 1992 n'utilise pas les mots clés JOIN mais liste uniquement les tables dans la clause FROM.
SELECT *
FROM A, B;
Le résultat de cette requête est strictement équivalent au produit cartésien CROSS JOIN. Si une condition WHERE est ajoutée, l'équivalent est une jointure INNER.
SELECT *
FROM A, B
WHERE A.id = B.a_id;
Alternatives
Les jointures d'exclusion peuvent aussi s'écrire avec une sous requête et une condition EXISTS.
SELECT gauche.*
FROM gauche g
WHERE NOT EXISTS (
SELECT 1
FROM droite d
WHERE d.g_id = g.id
);
SELECT 1 car nous ne voulons pas récupérer de valeur de la table mais uniquement savoir si la ligne existe ou pas.
Avantages :
- Robuste face aux valeurs NULL (pas d’ambiguïté).
- Souvent optimisé par le moteur en anti-semi-join (c’est-à-dire que le moteur ne lit pas plus de lignes que nécessaire dans la table de droite). Les jointures qui passent par des produits cartésiens sont consommatrices en ressources.
Exercice : Lister les articles dont la famille n'existe pas.
select * from article
where not exists (
select 1 from famille
where a.famille_code = famille.code
)
Ou avec l'opérateur not in
select * from article
where famille_code not in (
select code from famille
where code = article.famille_code
)
Cette requête est elle aussi équivalente cependant il existe un risque de mauvaise interprétation. Si le sous-select contient au moins un NULL, alors toutes les lignes sont rejetées (car la comparaison avec la valeur NULL dans code NOT IN ( …, NULL, … ) est indéterminée.)
Il existe plusieurs syntaxes permettant d’obtenir le même résultat. Toutefois, certains points méritent d’être pris en compte :
- Consommation de ressources : un même résultat peut être produit par des requêtes dont le coût en temps d’exécution ou en mémoire diffère sensiblement.
- Lisibilité : les syntaxes modernes apportent plus de clarté et facilitent la compréhension de l’intention de la requête.
Auto-jointure
C'est une jointure dans laquelle une table est jointe avec elle-même (self join). Cela peut être utile pour comparer les lignes d'une même table. Comme la table apparaît deux fois dans la requête, des alias sont utilisés pour identifier la partie gauche de la partie droite
SELECT G.colonne, D.colonne
FROM table G
JOIN table D ON G.nom = D.nom
WHERE G.id <> D.id;
C'est une représentation purement théorique il n'y a pas de mot clé particulier, la différence est seulement que la même table apparaît plusieurs fois.
Ce type de requête est utilisées pour comparer des lignes entres elles ou détecter des doublons.
Quel adhérent est en double (même nom, prénom et date de naissance)
select a.nom, a.prenom
from adherent a
join adherent b on a.nom = b.nom
and a.prenom = b.prenom
and a.naissance = b.naissance
and a.id <> b.id