Fonctions ⑥ — CASE en pratique — ORDER BY / UPDATE / NULL

Données utilisées dans cet article — staff et customer

Le CASE appris jusqu'ici est une expression polyvalente que tu peux placer non seulement dans la liste de colonnes SELECT mais aussi dans `ORDER BY` et dans une clause SET de `UPDATE`. Cet article parcourt ces deux applications et le piège de la gestion de NULL avec CASE dans l'ordre.

Le sujet est la table staff (10 membres) dans la première moitié et la table customer (8 clients / contient NULL) dans la seconde. La première moitié trie city dans un ordre arbitraire ; la seconde utilise un CASE qui affiche un email NULL en « Not registered », et finit par exécuter un UPDATE CASE qui réécrit les valeurs de country en catégories de région.

Avant les exercices, confirmons les définitions de colonnes et un échantillon de données des deux tables utilisées ici — staff et customer.

① Utilise PRAGMA table_info(staff); et PRAGMA table_info(customer); pour vérifier les définitions de colonnes des deux tables.

② Utilise SELECT * FROM staff LIMIT 5; et SELECT * FROM customer LIMIT 5; pour prévisualiser les 5 premières lignes.

Utiliser CASE dans ORDER BY — Attribuer un ordre arbitraire

Le côté droit de ORDER BY peut contenir non seulement un nom de colonne mais aussi une expression CASE. Avec ça tu peux attribuer un ordre arbitraire que l'ordre alphabétique de chaîne ou la taille numérique ne peuvent produire. Utilise-le pour des priorités métier comme « Tokyo → Osaka → Kyoto → autres », des ordres de statut comme « Open → In progress → Done », ou un ordre de classement.

Écris-le ainsi ORDER BY CASE WHEN cond THEN nombre ... ELSE nombre END — CASE renvoie un nombre pour le tri et tu tries par ce nombre. ASC / DESC peuvent s'attacher comme pour une colonne. Les lignes avec le même nombre sont ordonnées par la colonne ORDER BY suivante (séparée par virgule), donc combiner clé de tri + clé secondaire détermine aussi l'ordre au sein d'une catégorie.

-- 1) Trier city dans l'ordre 'Osaka → Kyoto → Tokyo → autres'
SELECT name, city, salary FROM staff
ORDER BY
  CASE city
    WHEN 'Osaka' THEN 1
    WHEN 'Kyoto' THEN 2
    WHEN 'Tokyo' THEN 3
    ELSE 9
  END,
  salary DESC;  -- au sein de la même city, salaire le plus haut d'abord

-- 2) ORDER BY CASE + DESC peut aussi inverser l'ordre
--   si CASE attribue 1, 2, 3, alors DESC les ordonne 3, 2, 1

Imagine l'exigence « afficher le personnel par ordre de taille de bureau (Tokyo → Osaka → Kyoto → autres) ». (L'explication apparaît une fois que tu l'exécutes correctement.)

① Depuis la table staff, prends les 2 colonnes name, city.

② Avec ORDER BY CASE, attribue « Tokyo vaut 1, Osaka vaut 2, Kyoto vaut 3, tout le reste vaut 9 » et trie par ce nombre. Fais-en un tri à plusieurs niveaux où les lignes d'une même city sont ordonnées par name croissant.

Le piège CASE + NULL — Utilise `IS NULL`, pas `= NULL`

Comme dans WHERE, `= NULL` / `<> NULL` ne peuvent pas non plus s'utiliser dans CASE. Une comparaison avec NULL résulte toujours en NULL (inconnu), et quand une clause CASE WHEN renvoie NULL cette branche n'est pas prise, donc le résultat diffère de ce que tu voulais.

Pour tester NULL, utilise `colonne IS NULL` / `colonne IS NOT NULL`. C'est la même règle que la vérification NULL de WHERE vue plus tôt, et elle est commune partout où une condition peut s'écrire — CASE / IIF / WHERE / ON. La forme simple (CASE colonne WHEN ... THEN) ne peut pas tester NULL, donc quand tu dois gérer NULL utilise toujours la forme avec recherche (CASE WHEN colonne IS NULL THEN ...).

-- Faux : WHEN NULL ne peut pas isoler les lignes NULL ('Unknown' jamais affiché)
SELECT name, age,
  CASE age
    WHEN NULL THEN 'Unknown'
    ELSE age
  END AS age_display
FROM customer;

-- Juste : forme avec recherche + IS NULL
SELECT name, age,
  CASE
    WHEN age IS NULL THEN 'Unknown'
    ELSE age
  END AS age_display
FROM customer;

-- Note : COALESCE(age, 'Unknown') donne le même résultat
--   pour un simple remplacement NULL à 2 valeurs, COALESCE est plus court

Imagine l'exigence « sur un écran de liste de clients, afficher NULL dans la colonne email en 'Not registered' au lieu d'un blanc ».

① Depuis la table customer, prends name et email.

② Avec la forme avec recherche de CASE, ajoute une 3e colonne avec l'alias email_display qui vaut 'Not registered' pour les lignes où email est NULL, et la valeur email d'origine sinon.

Utiliser CASE dans UPDATE — Réécrire des valeurs selon une condition

Jusqu'ici CASE a généré une nouvelle colonne côté lecture, mais écrire CASE dans une clause SET de UPDATE te permet de réécrire les vraies valeurs de colonne dans la table selon une condition.

Écris-le ainsi UPDATE table SET colonne = CASE WHEN cond THEN valeur1 WHEN cond THEN valeur2 ... ELSE colonne END. Écrire ELSE colonne pour dire « les lignes ne correspondant à aucun WHEN gardent leur valeur d'origine » est la façon sûre ; oublie-le et ELSE est traité comme NULL, écrasant les lignes non concernées avec NULL.

Comme UPDATE est une opération destructrice qui change la vraie table, cet article la gère dans le dernier exercice. Avant de l'exécuter, la pratique standard en production est d'essayer la même condition avec SELECT name, colonne, CASE ..., de vérifier visuellement le résultat, et de le réécrire en UPDATE seulement s'il a l'air correct.

-- D'abord vérifier avec SELECT (en visant à mettre l'age NULL à 0)
SELECT name, age,
  CASE
    WHEN age IS NULL THEN 0
    ELSE age
  END AS new_age
FROM customer;

-- Si ça a l'air correct, réécris en UPDATE
UPDATE customer SET age = CASE
  WHEN age IS NULL THEN 0
  ELSE age
END;

Imagine l'exigence « convertir en masse la colonne country de la table customer de noms de pays en catégories de région (Asia / Western / Europe / Unknown) ». C'est le dernier exercice de l'article, donc tu réalises une opération destructrice qui change la vraie table.

① UPDATE en masse la colonne country de la table customer avec CASE. La correspondance est :

- Japan → Asia

- US ou UK → Western

- Italy → Europe

- NULL → Unknown

- Tout le reste → garder la valeur d'origine (pour qu'une réexécution donne le même résultat)

② Après l'UPDATE, exécute SELECT id, name, country FROM customer ORDER BY id; et confirme que le pays de chaque client est devenu un nom de région.