Composer des requêtes — combiner JOIN, sous-requêtes et UNION

Données utilisées dans cet article — employee / department / sales

JOIN (connecter plusieurs tables), les sous-requêtes (une requête à l'intérieur d'une requête) et UNION (empiler des résultats verticalement) fonctionnent chacun de leur côté, mais quand tu les combines avec WHERE / ORDER BY / LIMIT tu peux écrire des requêtes pratiques et concrètes.

Dans cet article, tu vas parcourir, dans l'ordre, comment combiner ces techniques et les faire grandir en une seule requête.

Le matériel est constitué de trois tables d'employés — employee (30 employés), department (6 services) et sales (50 lignes de ventes).

Tu vas commencer par un JOIN simple, puis monter en puissance à travers les sous-requêtes et UNION étape par étape.

Avant de plonger dans les exercices, prends un moment pour vérifier les définitions de colonnes et un échantillon des données des trois tables utilisées dans cet article — employee / department / sales.

① Utilise PRAGMA table_info(...) pour vérifier les définitions de colonnes des trois tables.

② Utilise SELECT * FROM nom_table LIMIT 5; pour prévisualiser les 5 premières lignes de chaque table.

Mets au clair l'ordre d'évaluation des clauses — JOIN, WHERE, ORDER BY, LIMIT

L'ordre dans lequel tu écris une requête (SELECT … FROM … WHERE … ORDER BY) est différent de l'ordre dans lequel elle est évaluée.

La base de données connecte d'abord les tables avec FROM / JOIN, puis réduit les lignes avec WHERE, agrège avec GROUP BY / HAVING, construit les colonnes avec SELECT, et enfin trie avec ORDER BY et limite le nombre de lignes avec LIMIT.

Une fois cet ordre maîtrisé, des règles comme « pour filtrer sur un résultat agrégé utilise HAVING, pas WHERE » et « LIMIT prend effet après le tri » commencent à prendre du sens.

-- JOIN + WHERE + ORDER BY : employés des services d'Osaka, triés par date d'embauche
SELECT emp.name, dept.dept_name, emp.hired_on
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE dept.location = 'Osaka'
ORDER BY emp.hired_on;

Combine JOIN, WHERE, ORDER BY et LIMIT dans une seule requête. (Exécute-le correctement et l'explication s'affiche.)

① Fais une jointure interne de employee et department sur dept_id et extrais le nom de l'employé, le nom du service et le salaire.

② Ne garde que les employés dont la localisation du service (department.location) est Tokyo.

③ Trie par salaire du plus haut au plus bas et prends seulement le top 5.

Construire une valeur de référence avec une sous-requête et affiner le résultat du JOIN

Une sous-requête peut servir à construire la « valeur de référence » dont tu as besoin pour affiner davantage un résultat que tu as assemblé avec JOIN.

Une valeur de critère qui change par employé, comme « le salaire moyen de mon propre service », se calcule avec une sous-requête corrélée (une sous-requête recalculée pour chaque employé individuel) qui référence la ligne externe.

Quand tu attaches le nom du service avec JOIN et que tu réduis les lignes en utilisant une condition de sous-requête corrélée, plusieurs morceaux de syntaxe s'imbriquent dans une seule requête.

-- JOIN + sous-requête corrélée : employés dont le salaire est égal au max de leur propre service (le mieux payé du service)
SELECT emp.name, dept.dept_name, emp.salary
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE emp.salary = (
  SELECT MAX(dept_member.salary)
  FROM employee dept_member
  WHERE dept_member.dept_id = emp.dept_id
);

Cette fois tu vas empiler les sous-requêtes. Liste, avec le nom du service, les employés de chaque service qui gagnent « plus que le salaire moyen de leur propre service ».

① Joins employee et department et extrais le nom de l'employé, le nom du service et le salaire.

② Utilise une sous-requête corrélée pour calculer le salaire moyen du propre service de cet employé, et ne garde que les employés dont salary lui est supérieur.

③ Trie par dept_id croissant, et au sein d'un même service par salaire du plus haut au plus bas.

Combiner deux perspectives avec UNION et finir avec ORDER BY

UNION empile verticalement les résultats de deux SELECT.

Les SELECT supérieur et inférieur doivent être compatibles UNION (le même nombre de colonnes, avec les types des colonnes correspondantes compatibles).

UNION réduit les lignes totalement identiques en une seule (suppression des doublons), tandis que UNION ALL garde les doublons tels quels.

Le tri et les limites de nombre de lignes s'appliquent à l'ensemble du résultat combiné, donc écris ORDER BY / LIMIT une seule fois, tout à la fin.

-- UNION ALL : empiler deux perspectives verticalement avec une colonne d'étiquette (garde les doublons)
SELECT name, 'Kyoto' AS via FROM employee WHERE city = 'Kyoto'
UNION ALL
SELECT emp.name, 'HighSales' AS via
FROM employee emp
JOIN sales sale ON sale.emp_id = emp.emp_id
GROUP BY emp.emp_id
HAVING SUM(sale.amount) >= 1500000
ORDER BY name;

Enfin, combine deux perspectives en un seul répertoire avec UNION.

① Écris un SELECT qui extrait les noms des « employés habitant à Kyoto ».

② Écris un SELECT qui trouve les « employés dont le total des ventes est de 1,5 million de yens ou plus » en utilisant une jointure de employee et sales plus une agrégation (GROUP BY / HAVING).

③ Connecte les deux avec UNION pour réduire les doublons en une seule ligne, et trie par nom croissant.