Modélisation et Initialisation des Données

Pour explorer les mécanismes de jointure et les sous-requêtes, nous allons utiliser un modèle de gestion d'entreprise simplifié comprenant deux entités principales : les départements et les collaborateurs.

-- Création des tables
CREATE TABLE departements (
    id_dept INT PRIMARY KEY,
    nom_dept VARCHAR(50)
);

CREATE TABLE collaborateurs (
    id_collab INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(50),
    prenom VARCHAR(50),
    genre ENUM('H', 'F') NOT NULL DEFAULT 'H',
    age INT,
    id_dept INT
);

-- Insertion des données de référence
INSERT INTO departements (id_dept, nom_dept) VALUES
(10, 'Recherche et Développement'),
(20, 'Ressources Humaines'),
(30, 'Marketing'),
(40, 'Logistique');

INSERT INTO collaborateurs (nom, prenom, genre, age, id_dept) VALUES
('Dupont', 'Jean', 'H', 28, 10),
('Martin', 'Sophie', 'F', 34, 20),
('Bernard', 'Luc', 'H', 45, 20),
('Dubois', 'Marie', 'F', 29, 30),
('Leroy', 'Thomas', 'H', 24, 10),
('Moreau', 'Julie', 'F', 31, 50); -- Département inexistant (50)

Mécanismes de Jointures Multi-Tables

La syntaxe fondamentale pour relier des tables repose sur l'opérateur JOIN et une condition de correspondance définie par ON.

1. Produit Cartésien (Cross Join)

L'absence de condition de jointure génère un produit cartésien, associant chaque ligne de la première table à toutes les lignes de la seconde. Cette approche est rarement utilisée en production en raison de son coût exponentiel en mémoire.

SELECT c.nom, d.nom_dept 
FROM collaborateurs c, departements d;

2. Jointure Interne (Inner Join)

Cette opération ne conserve que les enregistrements ayant une correspondance exacte dans les deux tables. Les collaborateurs sans département valide ou les départements vides sont exclus.

SELECT c.id_collab, c.nom, c.age, d.nom_dept 
FROM collaborateurs c 
INNER JOIN departements d ON c.id_dept = d.id_dept;

-- Syntaxe alternative implicite (ANSI SQL-89)
SELECT c.id_collab, c.nom, c.age, d.nom_dept 
FROM collaborateurs c, departements d 
WHERE c.id_dept = d.id_dept;

3. Jointure Externe Gauche (Left Join)

La table de gauche (ici, collaborateurs) dicte le résultat. Tous ses enregistrements sont retournés, même s'il n'y a aucune correspondance dans la table de droite. Les champs non correspondants affichent NULL.

SELECT c.nom, c.prenom, d.nom_dept AS departement 
FROM collaborateurs c 
LEFT JOIN departements d ON c.id_dept = d.id_dept;

4. Jointure Externe Droite (Right Join)

Le principe est inversé : la table de droite (departements) prévaut. Cela permet d'identifier, par exemple, les départements qui n'ont actuellement aucun collaborateur assigné.

SELECT c.nom, d.nom_dept 
FROM collaborateurs c 
RIGHT JOIN departements d ON c.id_dept = d.id_dept;

5. Simulation de la Jointure Externe Complète (Full Outer Join)

MySQL ne supporte pas nativement l'instruction FULL OUTER JOIN. Il est néanmoins possible de reproduire ce comportement en combinant une jointure gauche et une jointure droite via l'opérateur d'union.

SELECT c.nom, d.nom_dept FROM collaborateurs c LEFT JOIN departements d ON c.id_dept = d.id_dept
UNION
SELECT c.nom, d.nom_dept FROM collaborateurs c RIGHT JOIN departements d ON c.id_dept = d.id_dept;

Note : L'utilisation de UNION élimine les doublons, contrairement à UNION ALL.

Jointures avec Filtrage et Tri

Il est courant de combiner des jointures avec des clauses de filtrage (WHERE) et d'ordonnancement (ORDER BY) pour affiner les jeux de résultats.

-- Obtenir les collaborateurs de plus de 30 ans avec leur département, triés par âge décroissant
SELECT c.nom, c.age, d.nom_dept 
FROM collaborateurs c 
INNER JOIN departements d ON c.id_dept = d.id_dept
WHERE c.age > 30
ORDER BY c.age DESC;

Maîtriser les Sous-Requêtes

Une sous-requête est une instruction SELECT imbriquée qui fournit un jeu de résultats temporaire à la requête principale. Elles peuvent être introduites par des mots-clés comme IN, EXISTS, ou des opérateurs de comparaison.

1. Sous-requêtes avec IN / NOT IN

Idéal pour vérifier l'appartenance à un ensemble de valeurs retourné par une requête interne.

-- Identifier les départements dont l'âge moyen des collaborateurs dépasse 30 ans
SELECT id_dept, nom_dept FROM departements
WHERE id_dept IN (
    SELECT id_dept FROM collaborateurs 
    GROUP BY id_dept 
    HAVING AVG(age) > 30
);

-- Trouver les départements n'ayant aucun collaborateur
SELECT nom_dept FROM departements 
WHERE id_dept NOT IN (
    SELECT DISTINCT id_dept FROM collaborateurs WHERE id_dept IS NOT NULL
);

2. Sous-requêtes Scalaires (Opérateurs de Comparaison)

Lorsque la sous-requête retourne une valeur unique (scalaire), elle peut être comparée directement.

-- Lister les collaborateurs plus âgés que la moyenne globale de l'entreprise
SELECT nom, prenom, age 
FROM collaborateurs 
WHERE age > (SELECT AVG(age) FROM collaborateurs);

3. Sous-requêtes Corrélatives avec EXISTS

Le mot-clé EXISTS évalue la présence de lignes. La requête interne est exécutée pour chaque ligne de la requête externe et retourne un booléen.

-- Sélectionner les départements qui possèdent au moins un collaborateur
SELECT nom_dept FROM departements d
WHERE EXISTS (
    SELECT 1 FROM collaborateurs c WHERE c.id_dept = d.id_dept
);

Cas Pratique Avancé : Système d'Évaluation Universitaire

Pour consolider ces concepts, analysons un schéma relationnel plus complexe modélisant un système de notes universitaires.

CREATE TABLE etudiants (
    id_etu INT PRIMARY KEY AUTO_INCREMENT,
    nom_etu VARCHAR(50),
    classe VARCHAR(20)
);

CREATE TABLE matieres (
    id_mat INT PRIMARY KEY AUTO_INCREMENT,
    nom_mat VARCHAR(50),
    id_prof INT
);

CREATE TABLE evaluations (
    id_eval INT PRIMARY KEY AUTO_INCREMENT,
    id_etu INT,
    id_mat INT,
    note INT
);

-- Insertion de données succinctes pour les tests
INSERT INTO etudiants (nom_etu, classe) VALUES ('Alice', 'Info-1'), ('Bob', 'Info-1'), ('Charlie', 'Math-2');
INSERT INTO matieres (nom_mat, id_prof) VALUES ('Algorithmique', 101), ('Base de Données', 102), ('Algèbre', 103);
INSERT INTO evaluations (id_etu, id_mat, note) VALUES 
(1, 1, 85), (1, 2, 92), (2, 1, 78), (2, 2, 60), (3, 3, 95);

Résolution de Problèmes Complexes

1. Identifier les étudiants dont la moyenne générale est strictement supérieure à 80.

SELECT e.nom_etu, avg_notes.moyenne
FROM etudiants e
INNER JOIN (
    SELECT id_etu, AVG(note) AS moyenne
    FROM evaluations
    GROUP BY id_etu
    HAVING AVG(note) > 80
) AS avg_notes ON e.id_etu = avg_notes.id_etu;

2. Trouver les étudiants n'ayant pas suivi le cours enseigné par le professeur 102.

SELECT nom_etu FROM etudiants
WHERE id_etu NOT IN (
    SELECT DISTINCT id_etu FROM evaluations
    WHERE id_mat IN (
        SELECT id_mat FROM matieres WHERE id_prof = 102
    )
);

3. Obtenir les deux meilleures notes pour chaque matière (Gestion du classement sans fonctions de fenêtrage).

Cette requête utilise une auto-jointure pour compter combien de notes sont supérieures ou égales à la note actuelle, permettant ainsi d'isoler le top 2.

SELECT ev1.id_mat, ev1.id_etu, ev1.note
FROM evaluations ev1
INNER JOIN evaluations ev2 
    ON ev1.id_mat = ev2.id_mat AND ev1.note <= ev2.note
GROUP BY ev1.id_mat, ev1.id_etu, ev1.note
HAVING COUNT(ev2.note) <= 2
ORDER BY ev1.id_mat, ev1.note DESC;

4. Lister les matières qui ont été évaluées par tous les étudiants du système.

SELECT m.nom_mat
FROM matieres m
WHERE m.id_mat IN (
    SELECT id_mat
    FROM evaluations
    GROUP BY id_mat
    HAVING COUNT(DISTINCT id_etu) = (SELECT COUNT(*) FROM etudiants)
);