Jointures de tables (3) — Empiler WHERE / ORDER BY / CASE sur un JOIN

Les données utilisées — department, employee et sales

Dans les articles précédents, tu as vu les différentes variantes de jointure — INNER JOIN, OUTER JOIN et auto-jointure.

Dans cet article, tu vas empiler des filtres WHERE, ORDER BY et des étiquettes CASE par-dessus un JOIN et construire la forme d'un rapport d'agrégation que tu rencontreras vraiment au travail.

Les données sont department (6 services) et employee (30 employés), plus une nouvelle table sales (50 lignes) de lignes de vente.

sales.emp_id est une clé étrangère qui pointe vers employee.emp_id.

On va joindre les trois tables pour construire un rapport qui affiche le service et le nom du commercial à côté de chaque vente.

Avant les exercices, confirmons les définitions de colonnes et les lignes d'exemple des trois tables — department, employee et sales.

① Exécute PRAGMA table_info(table_name); sur chaque table pour inspecter ses colonnes.

② Exécute SELECT * FROM table_name LIMIT 5; sur chaque table pour prévisualiser les 5 premières lignes. Remarque comment sales.emp_id pointe vers une ligne de employee.

Joindre trois tables — enchaîner les JOIN

Pour joindre trois tables ou plus, il suffit d'enchaîner les clauses `JOIN ... ON ...` l'une après l'autre.

Écris FROM sales s JOIN employee e ON s.emp_id = e.emp_id JOIN department d ON e.dept_id = d.dept_id — relie d'abord sales à employee sur emp_id, puis relie ce résultat à department sur dept_id.

La façon la plus naturelle de lire l'ordre est : « pars des lignes de vente (sales), puis attache-leur les informations d'employé et de service ».

Tout est en INNER JOIN, donc seules les lignes qui correspondent dans les trois tables survivent.

Dans ce jeu de données, les 12 employés qui ont des ventes appartiennent tous à un service, donc l'INNER JOIN à 3 tables renvoie les 50 lignes de sales.

-- Attacher le nom du commercial et dept_name à chaque vente (INNER JOIN 3 tables)
SELECT s.sale_id, e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY s.sale_id;

Imagine ce besoin : « Je veux un rapport des lignes de vente avec le nom de l'employé responsable et son nom de service attachés. » (L'explication apparaît une fois ta requête correctement exécutée.)

① INNER JOIN sales (alias s) avec employee (alias e) sur emp_id, puis INNER JOIN avec department (alias d) sur dept_id.

② Sélectionne les quatre colonnes s.sale_id, e.name, d.dept_name et s.amount.

③ Trie le résultat par s.sale_id ascendant.

Filtrer avec WHERE, trier avec ORDER BY

Une fois le JOIN en place, utilise WHERE pour ne garder que les lignes utiles et ORDER BY pour les disposer de façon lisible.

Les conditions de WHERE peuvent référencer des colonnes de n'importe quelle table jointe.

WHERE d.location = 'Tokyo' ne garde que les ventes des services situés à Tokyo, et WHERE s.amount >= 400000 ne garde que les ventes à forte valeur.

Les clauses SQL s'écrivent dans l'ordre FROM → JOIN → ON → WHERE → ORDER BY.

WHERE filtre les lignes une fois la jointure terminée, et ORDER BY trie en dernier.

Pour trier sur plusieurs colonnes, sépare-les par des virgules : ORDER BY col1 DESC, col2. Les lignes à égalité sur col1 sont ensuite ordonnées par col2 (une clé secondaire pour stabiliser l'ordre).

-- Seulement les ventes des services basés à Osaka, montant le plus élevé en tête
SELECT e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.location = 'Osaka'
ORDER BY s.amount DESC, s.sale_id;

Imagine ce besoin : « Je veux un rapport des ventes des services basés à Tokyo d'une valeur d'au moins 400 000, triées par montant décroissant. »

① INNER JOIN sales (s), employee (e) et department (d) sur emp_id et dept_id.

② Filtre sur les lignes où d.location vaut Tokyo et s.amount est au moins 400000.

③ Sélectionne les trois colonnes e.name, d.dept_name et s.amount, triées par s.amount décroissant. Pour les lignes à égalité sur le montant, utilise s.sale_id ascendant comme clé secondaire pour stabiliser l'ordre.

Ajouter des étiquettes de tranche avec CASE — finir avec un rapport classé

Par-dessus JOIN, WHERE et ORDER BY, une expression CASE peut ajouter une colonne avec des étiquettes de tranche pour le montant, transformant le résultat en un rapport qu'un humain peut parcourir d'un coup d'œil.

CASE évalue les conditions du haut vers le bas et renvoie la valeur de la première branche qui devient vraie. Tu peux la placer directement dans la liste des colonnes du SELECT (forme avec recherche : CASE WHEN condition THEN valeur ... ELSE défaut END).

Par exemple, sépare chaque vente en trois tranches selon amount : High quand amount >= 400000, Mid quand amount >= 200000, et Low sinon — en exposant la tranche dans une colonne aliasée band.

Garde les étiquettes en chaînes ASCII fixes ('High' / 'Mid' / 'Low') pour que le résultat reste portable entre les environnements.

En combinant JOIN à 3 tables, WHERE, ORDER BY et CASE dans une seule requête, tu obtiens presque exactement la forme des rapports d'agrégation qui sont construits chaque jour dans les vraies équipes.

-- Exemple : séparer amount en 2 tranches (Large >= 300000)
SELECT e.name, d.dept_name, s.amount,
  CASE
    WHEN s.amount >= 300000 THEN 'Large'
    ELSE 'Small'
  END AS size
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales'
ORDER BY s.sale_id;

Imagine ce besoin : « Je veux un rapport de ventes avec le nom du commercial, le nom du service et une étiquette de tranche, trié par montant décroissant. »

① INNER JOIN sales (s), employee (e) et department (d) sur les trois tables.

② Ajoute une colonne aliasée band en utilisant un CASE de la forme avec recherche qui renvoie 'High' quand s.amount vaut au moins 400000, 'Mid' quand au moins 200000, et 'Low' sinon.

③ Sélectionne les quatre colonnes e.name, d.dept_name, s.amount et band, triées par s.amount décroissant avec s.sale_id ascendant comme départage.

Imagine ce besoin : « Je veux une synthèse qui totalise les ventes de chaque commercial et les classe en Gold / Silver / Bronze selon le total. »

① INNER JOIN sales (s), employee (e) et department (d).

② Groupe par e.name et d.dept_name, et agrège SUM(s.amount) aliasé total.

③ Ajoute une colonne aliasée tier en utilisant un CASE de la forme avec recherche qui renvoie 'Gold' quand SUM(s.amount) vaut au moins 1500000, 'Silver' quand au moins 1000000, et 'Bronze' sinon.

④ Sélectionne les quatre colonnes e.name, d.dept_name, total et tier, triées par total décroissant avec e.name ascendant comme départage.

Les employés sans vente disparaissent avec INNER JOIN

Tous les rapports de cet article utilisent INNER JOIN, donc les 18 employés sans aucune vente n'apparaissent jamais dans le résultat.

Si tu as besoin d'un rapport qui inclut chaque employé — avec des zéros pour ceux qui n'ont pas eu de vente — pars de employee comme épine dorsale et LEFT JOIN sales dessus, puis enveloppe le SUM dans COALESCE(SUM(s.amount), 0) pour convertir les totaux NULL en 0.

Quand les chiffres d'un rapport ne tombent pas juste, la première chose à suspecter est « le mauvais type de jointure écarte silencieusement des lignes qui devraient être là ».