Sous-requêtes ② — tables dérivées, sous-requêtes SELECT, CREATE / INSERT SELECT

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

La dernière fois, tu as utilisé une sous-requête dans WHERE.

Dans cet article, tu vas parcourir quatre applications dans l'ordre : mettre une sous-requête dans la clause `FROM` (une table dérivée), en écrire une dans une colonne `SELECT`, la combiner avec `CASE`, et construire une nouvelle table à partir du résultat d'une sous-requête.

Le matériel est constitué de trois tables de données d'employés.

En utilisant employee (30 employés), department (6 services) et sales (50 lignes de ventes), tu vas construire une table d'agrégation par service et aligner le total des ventes de chaque employé en colonne.

Avant de commencer les exercices, regarde les définitions de colonnes et les données d'exemple des trois tables utilisées dans cet article — employee / department / sales.

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

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

Tables dérivées dans FROM — traiter le résultat d'une sous-requête comme une seule table

Une table dérivée est une façon d'écrire une sous-requête dans la clause FROM afin que son résultat soit traité comme une seule table virtuelle.

Tu peux d'abord construire « le salaire moyen par service » comme une table d'agrégation, puis appliquer d'autres JOIN ou WHERE à cette table.

C'est pratique quand tu veux affiner un résultat agrégé d'un cran de plus.

La règle clé pour les tables dérivées est que tu dois lui donner un alias.

Écris AS alias comme dans FROM (SELECT ...) AS ds (SQLite permet aussi d'omettre AS) — sans cela, tu obtiens une erreur.

C'est par cet alias que tu références les colonnes de la table dérivée, comme ds.avg_salary.

-- Table dérivée dans FROM : agréger l'effectif et le salaire moyen par service, puis joindre avec department
SELECT d.dept_name, ds.headcount, ds.avg_salary
FROM (
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) AS ds
JOIN department d ON d.dept_id = ds.dept_id
ORDER BY ds.avg_salary DESC;

Imagine ce besoin : « agréger le salaire moyen par service, et lister uniquement les services dont la moyenne est supérieure à 5 900 000, avec leur nom de service ». (Exécute-le correctement et l'explication s'affiche.)

① Dans une sous-requête, agrège employee par dept_id et construis une table dérivée qui contient dept_id et le salaire moyen (alias avg_salary). Exclus de l'agrégation les employés dont dept_id est NULL.

② Donne à la table dérivée un alias, joins-la avec la table department sur dept_id, et extrais le nom du service.

③ Dans le WHERE externe, ne garde que les services dont avg_salary est supérieur à 5900000, et trie par avg_salary décroissant.

Sous-requêtes dans la clause SELECT — intégrer une valeur unique comme colonne

Quand tu écris une sous-requête scalaire dans la liste de colonnes SELECT, tu peux ajouter une colonne dont la valeur est calculée une par ligne.

Mets (SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id) dans une colonne, et tu peux aligner « le total des ventes de cet employé » comme une colonne par employé.

C'est une sous-requête corrélée qui référence le emp.emp_id externe.

-- Sous-requête dans la clause SELECT : ajouter le total des ventes de chaque employé comme une colonne
SELECT emp.name,
  COALESCE(
    (SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id),
    0
  ) AS total_amount
FROM employee emp
ORDER BY emp.emp_id
LIMIT 6;

Un employé sans ventes fait renvoyer NULL à la sous-requête, donc le remplacer par 0 avec COALESCE(..., 0) rend la table plus lisible.

Une sous-requête dans la clause SELECT doit aussi tenir dans une ligne et une colonne.

Imagine ce besoin : « à côté d'une liste de tous les employés, afficher le total des ventes de cet employé comme une colonne supplémentaire, en affichant 0 pour les employés sans ventes ».

① Donne à la table employee l'alias emp et extrais name.

② Dans les colonnes SELECT, ajoute une sous-requête qui calcule le total sales de cet employé, avec l'alias total_amount. Corrèle-la par sale.emp_id = emp.emp_id, et remplace le total par 0 pour les employés dont le total ressort NULL.

③ Trie par total_amount décroissant, en départageant les ex æquo par emp_id croissant, et limite aux 8 premières lignes.

Combiner avec CASE — attacher une étiquette de branchement selon la valeur d'une sous-requête

Une sous-requête dans la clause SELECT fonctionne tout aussi bien comme condition dans CASE.

Tu peux construire une colonne de rang du type « si le total des ventes est de 1 500 000 ou plus alors High, si supérieur à 0 alors Mid, sinon None » en jugeant la valeur de la sous-requête avec CASE WHEN.

Note que tu dois répéter la même sous-requête dans chaque WHEN de CASE (si la lisibilité prime, il y a aussi une façon de ne l'écrire qu'une fois avec WITH (expressions de table communes), que tu apprendras dans un chapitre ultérieur).

Dans cet article, tu vas couvrir la forme de base consistant à combiner directement une sous-requête et CASE.

-- Classer les employés selon leur nombre de ventes
SELECT emp.name,
  (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) AS sale_count,
  CASE
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 4 THEN 'Frequent'
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 1 THEN 'Occasional'
    ELSE 'None'
  END AS activity
FROM employee emp
ORDER BY sale_count DESC, emp.emp_id
LIMIT 8;

Imagine ce besoin : « pour chaque employé, afficher côte à côte le total des ventes et un rang (High / Mid / None) ».

① Donne à employee l'alias emp et extrais name et le total des ventes de cet employé (NULL remplacé par 0, alias total_amount).

② Avec CASE, ajoute une colonne de rang d'alias grade où le total des ventes de 1500000 ou plus est 'High', supérieur à 0 et inférieur à 1500000 est 'Mid', et sinon (0) est 'None'. Utilise aussi la même sous-requête de total des ventes pour le jugement.

③ Trie par total_amount décroissant, en départageant les ex æquo par emp_id croissant, et limite aux 8 premières lignes.

CREATE / INSERT SELECT — transformer le résultat d'une sous-requête en table

Écrire CREATE TABLE nouvelle_table AS SELECT ... te permet de sauvegarder le résultat du SELECT directement comme une nouvelle table (CTAS : Create Table As Select).

C'est souvent utilisé quand tu veux conserver un résultat agrégé sous forme d'instantané.

Quand tu veux ajouter des lignes à une table qui existe déjà, utilise INSERT INTO table_existante SELECT ... pour ajouter en masse le résultat du SELECT.

Ce sont des opérations d'écriture qui créent une table ou ajoutent des lignes.

Dans cet article, tu vas utiliser une table de travail jetable top_seller pour l'agrégation.

Pour obtenir le même résultat quel que soit le nombre d'exécutions, tu vas nettoyer la table de travail avec DROP TABLE IF EXISTS avant de la recréer.

L'INSERT en masse est plus rapide

INSERT INTO table SELECT ... insère les lignes cibles toutes d'un coup en une seule instruction.

Comparé à répéter INSERT INTO table VALUES (...) pour chaque ligne, l'analyse du SQL, les mises à jour d'index et la gestion de la transaction n'arrivent qu'une seule fois, donc plus tu insères de lignes, plus c'est rapide.

Quand tu copies ou déplaces une autre table ou un résultat agrégé, préfère l'INSERT ... SELECT en masse plutôt que de répéter INSERT ligne par ligne.

-- Sauvegarder un agrégat par service dans une table instantané
DROP TABLE IF EXISTS dept_summary;
CREATE TABLE dept_summary AS
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id;

SELECT * FROM dept_summary ORDER BY avg_salary DESC;

Imagine ce besoin : « rassembler les employés dont le total des ventes est de 1 500 000 ou plus dans une table d'agrégation à 3 colonnes — identifiant d'employé, nom et total des ventes ». Comme c'est une opération d'écriture, structure-le pour que la table de travail soit recréée et ne casse pas à la réexécution.

① D'abord, nettoie la table de travail si elle existe avec DROP TABLE IF EXISTS top_seller;.

② Joins employee et sales, agrège le total des ventes par employé, écris un SELECT qui ne garde que les employés dont le total est de 1500000 ou plus, et à partir de ce résultat construis la table top_seller avec CREATE TABLE top_seller AS SELECT .... Fais en sorte que les colonnes soient les 3 : emp_id, name et total_amount (total des ventes).

③ Enfin, vérifie le contenu avec SELECT * FROM top_seller ORDER BY total_amount DESC;.

Imagine ce besoin : « à la table d'agrégation de l'Exercice 4, ajouter aussi les employés intermédiaires dont le total des ventes est de 900 000 ou plus mais inférieur à 1 500 000 ». Utilise INSERT INTO ... SELECT pour ajouter les lignes en masse. Pour que cela ne casse pas à la réexécution, structure-le pour recréer la table avant d'ajouter.

① Nettoie la table de travail avec DROP TABLE IF EXISTS top_seller;, puis recrée-la avec le même CREATE TABLE top_seller AS SELECT ... (1,5 M ou plus) que l'Exercice 4.

② Avec INSERT INTO top_seller SELECT ..., ajoute les employés dont le total des ventes est de 900000 ou plus et inférieur à 1500000 (3 colonnes : emp_id, name, total des ventes). Fais correspondre l'ordre des colonnes à top_seller.

③ Vérifie toutes les lignes avec SELECT * FROM top_seller ORDER BY total_amount DESC;.