SLAM / Développement / Jointures externes et vues

Jointures externes et vues

Deux outils du quotidien : afficher aussi les lignes qui n'ont pas de correspondance, et encapsuler une requête complexe derrière un nom.

1. Rappel : la jointure interne

INNER JOIN ne retourne que les lignes ayant une correspondance des deux côtés. Un candidat qui n'a passé aucune épreuve n'apparaît pas dans le résultat.

SELECT prenomCandidat, intituleEpreuve, note
FROM candidat
INNER JOIN obtenir ON candidat.numCandidat = obtenir.numCandidat
INNER JOIN epreuve ON obtenir.codeEpreuve = epreuve.codeEpreuve;

2. La jointure externe

La jointure externe conserve les lignes d'une table même sans correspondance dans l'autre. Les colonnes de la table absente sont alors remplies avec null.

SELECT prenomCandidat, intituleEpreuve, note
FROM candidat
LEFT OUTER JOIN obtenir ON candidat.numCandidat = obtenir.numCandidat
INNER JOIN epreuve ON obtenir.codeEpreuve = epreuve.codeEpreuve;

Fabrice n'a passé aucune épreuve : il apparaît quand même, avec des valeurs nulles. C'est exactement l'intérêt de la jointure externe — « tous les produits, avec pour ceux qui ont été vendus le nombre de ventes ».

3. LEFT, RIGHT, FULL

Le mot-clé OUTER est facultatif : LEFT JOIN et LEFT OUTER JOIN sont identiques.

Piège classique : enchaîner un LEFT JOIN puis un INNER JOIN sur la table jointe annule l'effet du premier, car l'INNER élimine ensuite les lignes à null. Autre piège : mettre une condition sur la table optionnelle dans le WHERE plutôt que dans le ON transforme silencieusement la jointure externe en jointure interne.

Pour ne garder que les lignes sans correspondance (les produits jamais vendus, par exemple) :

SELECT p.codeProduit, p.libelleProduit
FROM produit p
LEFT JOIN ligne_facture lf ON p.codeProduit = lf.codeProduit
WHERE lf.codeProduit IS NULL;

Et pour compter en conservant les zéros, on utilise COUNT sur une colonne de la table jointe : COUNT(*) compterait la ligne à null et retournerait 1 au lieu de 0.

SELECT p.codeProduit, p.libelleProduit, COUNT(lf.codeProduit) AS nbVentes
FROM produit p
LEFT JOIN ligne_facture lf ON p.codeProduit = lf.codeProduit
GROUP BY p.codeProduit, p.libelleProduit;

4. Les vues

• Une vue est un objet SQL, créé par le LDD.
• Elle s'utilise et s'apparente à une table virtuelle.
• Sa structure est définie par une requête LID.

CREATE VIEW Resultat_Maths AS
SELECT nomEtab, nomCandidat, prenomCandidat, note
FROM ETABLISSEMENT E
INNER JOIN CANDIDAT C ON C.codeEtab = E.codeEtab
INNER JOIN OBTENIR  O ON O.numCandidat = C.numCandidat
WHERE codeEpreuve = 'E2';

Elle s'interroge ensuite comme une table ordinaire :

SELECT * FROM Resultat_Maths;

SELECT nomCandidat FROM Resultat_Maths WHERE note >= 10;

Une vue ne stocke pas de données : elle stocke une requête. Elle reflète donc toujours l'état courant des tables sous-jacentes.

DROP VIEW Resultat_Maths;

5. Quand utiliser une vue

• Raccourci pour des requêtes compliquées, longues ou fréquemment utilisées : on écrit la jointure une fois, on la réutilise partout.
• Limiter l'accès à certaines informations pour certains utilisateurs : on donne le droit de lecture sur la vue, pas sur la table. Un utilisateur peut ainsi consulter les notes sans voir les dates de naissance.
• Optimisation : certains SGBDR pré-calculent, stockent et indexent les résultats des vues (vues matérialisées).
• Stabilité de l'interface : si la structure des tables change, on adapte la vue et les applications qui la consomment ne bougent pas.

Les vues sont composables : on peut interroger une vue depuis une autre requête, y compris avec des conditions et des jointures supplémentaires. Attention toutefois à ne pas empiler trop de niveaux, ce qui rend le plan d'exécution difficile à optimiser.