Colonnes calculées SQL, fonctions et CASE

Une colonne de résultat peut afficher une valeur qui n’est stockée nulle part dans la table. SQL la calcule à partir de la ligne courante. Une expression combine des colonnes, des valeurs fixes, des opérateurs et des appels de fonctions.

Calculer une valeur pour chaque ligne

SELECT order_id,
       quantity * unit_price AS line_total
FROM orders;

Pour une quantité de 3 et un prix unitaire de 12,50, la nouvelle colonne affiche 37.5. Chaque ligne fournit sa propre quantité et son propre prix. La requête laisse les colonnes stockées inchangées. Une fonction monoligne, comme ROUND(unit_price, 0), renvoie aussi une valeur par ligne d’entrée. L’ajouter à la liste SELECT ne combine pas les lignes. Une fonction d’agrégation comme SUM ou AVG calcule quant à elle une valeur pour un groupe.

Le nom après AS est un alias de colonne : un en-tête pour le résultat. Sans lui, SQLite utilise le texte de l’expression comme en-tête ; PostgreSQL utilise ?column? pour cette expression arithmétique. AS est facultatif, mais permet de distinguer plus facilement un nom d’une virgule manquante.

Choisir les noms et les guillemets

Utilise un nom simple comme line_total : des lettres, des chiffres et des tirets bas, en commençant par une lettre. Un nom contenant un espace ou correspondant à un mot réservé doit être entouré de guillemets doubles : AS "Line Total" ou AS "order". Les apostrophes délimitent les valeurs textuelles, comme 'Boston'. MySQL accepte aussi les accents graves pour les noms, et SQL Server accepte les crochets.

PostgreSQL convertit en minuscules les noms sans guillemets : AS LineTotal produit linetotal. Les guillemets doubles conservent la casse dans AS "LineTotal". SQLite conserve un alias tel qu’il est saisi. Les noms en minuscules séparées par des tirets bas conviennent aux deux.

Utiliser un alias à la bonne étape

L’ordre logique est FROM, WHERE, SELECT, puis ORDER BY. La table est choisie et les lignes sont filtrées avant que les expressions de sortie ne reçoivent leurs alias. Le tri a lieu ensuite.

SELECT order_id,
       quantity * unit_price AS line_total
FROM orders
WHERE quantity * unit_price > 100
ORDER BY line_total DESC;

ORDER BY peut utiliser line_total. Le SQL standard, PostgreSQL et MySQL ne peuvent pas utiliser ce nouvel alias dans WHERE ; répète l’expression à cet endroit. SQLite accepte un alias dans WHERE comme extension, mais cette forme échouera si elle est transférée vers PostgreSQL.

Un nouvel alias n’est pas non plus disponible pour une autre expression dans la même liste SELECT. Après unit_price * 1.08 AS with_tax, écrire with_tax - unit_price AS tax échoue si la table ne possède pas de colonne with_tax. Écris plutôt unit_price * 1.08 - unit_price AS tax.

Diagnostiquer un résultat surprenant

SELECT first_name last_name FROM customers; est du SQL valide. Cette requête affiche une colonne de prénoms avec l’alias last_name. Pour extraire les deux colonnes, écris SELECT first_name, last_name FROM customers;. Une virgule manquante peut donc produire un résultat trompeur sans erreur.

Pour vérifier rapidement une expression, une table est inutile : SELECT 19.99 * 3 AS total; renvoie 59.97 dans SQLite et PostgreSQL. Avant Release 23, Oracle exige FROM DUAL pour cette requête ; Release 23 et les versions ultérieures autorisent aussi SELECT sans FROM.

Les expressions arithmétiques transforment des quantités brutes en montants, en taux et en différences. Les opérateurs sont +, -, *, / et % ; le modulo donne le reste de la division.

Maîtriser l’ordre des calculs

La multiplication, la division et le modulo viennent avant l’addition et la soustraction. Les opérateurs de même priorité s’exécutent de gauche à droite. Ainsi, 10 + 3 * 2 donne 16, tandis que (10 + 3) * 2 donne 26.

Pour une quantité de 3, un prix unitaire de 12,50 et un pourcentage de remise de 10 :

quantity * unit_price * (1 - discount_pct / 100.0)

D’abord, 10 / 100.0 donne 0.1. Les parenthèses donnent la fraction payée, 0.9. Le montant brut est 37.5, donc le montant net est 33.75. Sans parenthèses, quantity * unit_price * 1 - discount_pct / 100.0 soustrait 0.1 de 37.5 et donne 37.4.

Conserver les fractions pendant la division

Dans SQLite, PostgreSQL et SQL Server, la division de deux entiers supprime la partie fractionnaire par troncation vers zéro : 7 / 2 donne 3, et -7 / 2 donne -3. MySQL et Oracle donnent un résultat décimal pour /. Dans SQLite et PostgreSQL, rends un opérande décimal avant de diviser : 7 / 2.0, 7 * 1.0 / 2 ou CAST(7 AS REAL) / 2 donne 3.5. Avec CAST(7 / 2 AS REAL), la conversion arrive trop tard et donne 3.0.

Pour une hausse de 80 à 92, (92 - 80) * 100.0 / 80 donne 15.0 pour cent. Diviser d’abord la différence entière par 80 fait perdre la fraction. Le modulo conserve le reste : 135 / 60 vaut 2 heures et 135 % 60 vaut 15 minutes. Dans SQLite et PostgreSQL, -7 % 3 donne -1 ; le reste prend le signe de l’opérande de gauche. Oracle écrit MOD(17, 5) à la place de %.

Un diviseur nul produit une erreur dans PostgreSQL et SQL Server ; SQLite et MySQL renvoient NULL. amount / NULLIF(divisor, 0) fait renvoyer NULL dans le cas d’un diviseur nul sur ces systèmes.

Arrondir ou choisir un entier

ROUND(x, n) arrondit à n décimales. Une lampe vendue 24,00 et coûtant 15,50 dégage un bénéfice de 8.5. ROUND((price - cost) / price * 100, 1) donne une marge de 35.4 pour cent. PostgreSQL accepte un nombre de décimales négatif : ROUND(1234.5678, -2) donne 1200 ; SQLite traite ce nombre comme zéro et donne 1235.0.

PostgreSQL fournit ces fonctions. SQLite fournit ABS ; CEIL, FLOOR et TRUNC exigent une version compilée avec les fonctions mathématiques, disponibles à partir de la version 3.35.

Adapter la précision aux données

ROUND dans SQLite et ROUND pour le type numeric de PostgreSQL arrondissent les valeurs exactement à mi-chemin en s’éloignant de zéro : ROUND(-2.5) donne -3. Dans PostgreSQL, l’arrondi des nombres à virgule flottante arrondit généralement les valeurs à mi-chemin à l’entier pair le plus proche ; sa fonction ROUND à deux arguments exige numeric, utilise donc ROUND(CAST(x AS numeric), 2) pour une colonne à double précision.

Les nombres à virgule flottante représentent de nombreuses fractions décimales de façon approximative. Dans SQLite, 0.1 + 0.2 = 0.3 peut être faux et ROUND(2.675, 2) peut renvoyer 2.67. Les littéraux décimaux de PostgreSQL utilisent le type exact numeric. Pour des montants exacts, utilise NUMERIC/DECIMAL dans PostgreSQL ou des centimes entiers dans SQLite. Déclarer DECIMAL dans SQLite ne crée pas de stockage décimal exact. Garde toutes les décimales pendant les calculs et arrondis pour l’affichage.

Le type de données d’une valeur influe sur les opérations arithmétiques, les comparaisons et le tri. Le nombre 42 est différent du texte '42', même lorsqu’une grille de résultats les affiche de la même façon.

Convertir explicitement une valeur

Parmi les types courants figurent INTEGER pour les entiers, NUMERIC ou DECIMAL pour les décimaux exacts dans PostgreSQL, REAL ou double precision pour les nombres approximatifs, TEXT ou VARCHAR pour le texte, ainsi que DATE, TIME, TIMESTAMP et BOOLEAN lorsqu’ils sont pris en charge.

SELECT CAST('42' AS INTEGER) AS number_value,
       CAST(42 AS TEXT) AS text_value;

CAST(expression AS type) fait partie du SQL standard. Le premier résultat est un nombre ; le second est du texte. PostgreSQL accepte aussi '42'::integer. Convertir NULL donne toujours NULL.

Pour une division décimale dans SQLite, écris CAST(quantity AS REAL) / 4 ; dans PostgreSQL, écris CAST(quantity AS NUMERIC) / 4. Une quantité de 7 donne 1.75. Dans SQLite, CAST(7 AS NUMERIC) / 2 donne toujours 3 : NUMERIC laisse cette valeur entière stockée comme un entier. REAL est le type de conversion adapté à cet usage dans SQLite.

Comprendre l’effet d’une conversion en entier

SQLite supprime la partie fractionnaire par troncation vers zéro. PostgreSQL arrondit une valeur numérique à l’entier le plus proche. Utilise d’abord ROUND, TRUNC, FLOOR ou CEIL lorsque la règle choisie compte. PostgreSQL refuse un texte qui n’est pas un nombre valide. SQLite lit une partie numérique initiale : '12abc' devient 12 et '12.5' devient 12 lors d’une conversion en INTEGER. Un zéro obtenu après une conversion dans SQLite ne prouve donc pas que le texte d’origine était un zéro valide. TRY_CAST dans SQL Server renvoie NULL lorsqu’une conversion échoue.

Distinguer les quantités des identifiants

Le texte est trié caractère par caractère. Les chaînes '9', '10' et '100' sont triées dans l’ordre '10', '100', '9'. Pour classer de véritables quantités numériques conservées sous forme de texte, utilise ORDER BY CAST(code AS INTEGER) après t’être assuré que ce sont des nombres valides.

Un code postal américain comme '02134' est un identifiant. Le convertir donne 2134 et fait perdre le premier zéro. Les numéros de téléphone doivent aussi être du texte : leurs chiffres servent à identifier quelque chose, plutôt qu’à décrire une quantité à additionner ou dont calculer la moyenne.

Reconnaître les conversions automatiques

SQLite et PostgreSQL convertissent tous deux le nombre en texte dans 'Order #' || 42, ce qui produit 'Order #42'. Tous deux évaluent aussi le littéral entre apostrophes dans '5' + 3 comme un nombre. Dans PostgreSQL, ce littéral a initialement un type non fixé ; une colonne TEXT n’a pas la même liberté. Ajouter 3 à une colonne TEXT échoue : convertis donc explicitement la colonne.

SQLite attribue aux colonnes une préférence de type, appelée affinité de type, et peut conserver différents types de valeurs dans une même colonne ordinaire. typeof(42), typeof(4.2), typeof('42') et typeof(NULL) indiquent 'integer', 'real', 'text' et 'null'. SQLite n’a pas de type DATE dédié : CAST('2026-03-14' AS DATE) donne 2026. Utilise date('2026-03-14') avec ses fonctions de date.

Oracle fournit TO_NUMBER, TO_CHAR et TO_DATE. TO_CHAR et TO_DATE dans PostgreSQL prennent des modèles de format comme 'MM/DD/YYYY'. CONVERT dans SQL Server utilise un code de style numérique : CONVERT(date, '03/14/2026', 101) lit le format de date américain.

NULL indique une valeur manquante ou inconnue. Une cellule de résultat vide peut afficher NULL, mais NULL n’est pas une valeur ordinaire que l’on compare avec =.

Suivre une entrée manquante dans une expression

Zéro est un nombre connu. La chaîne vide '' est une chaîne connue sans caractère. Le texte 'N/A' est un libellé enregistré. NULL indique qu’aucune valeur n’est disponible. Oracle traite la chaîne vide comme NULL ; SQLite et PostgreSQL les distinguent.

La plupart des opérations arithmétiques et des fonctions transmettent une entrée manquante sous la forme NULL. Avec un salaire de 50 000 et une prime NULL, salary + bonus donne NULL. Le total est inconnu parce que la prime est inconnue. UPPER(NULL), ROUND(NULL, 2) et 'Ana ' || NULL renvoient aussi NULL dans SQLite et PostgreSQL.

Tester directement NULL

bonus = NULL, bonus <> NULL et même NULL = NULL produisent UNKNOWN. On ne sait pas si deux valeurs manquantes sont égales. Utilise bonus IS NULL ou bonus IS NOT NULL. Ces tests renvoient toujours TRUE ou FALSE.

Les conditions SQL ont trois résultats possibles : TRUE, FALSE et UNKNOWN. SQLite affiche TRUE sous la forme 1, FALSE sous la forme 0 et UNKNOWN sous la forme NULL.

Comprendre les lignes conservées par WHERE

WHERE ne conserve que TRUE. FALSE et UNKNOWN excluent tous deux la ligne. Pour les primes 1 000, 0, NULL, 2 500 et NULL, WHERE bonus < 1000 ne conserve que le zéro. WHERE bonus >= 1000 conserve 1 000 et 2 500. Ces filtres couvrent trois lignes, et non cinq. WHERE NOT (bonus < 1000) ne conserve aussi que les deux primes connues d’au moins 1 000.

Le même problème concerne <>. Avec les villes Boston, Denver, NULL, Boston et Austin, WHERE city <> 'Boston' conserve Denver et Austin. Inclus explicitement la ville inconnue :

WHERE city <> 'Boston' OR city IS NULL

Cette condition conserve trois lignes. NOT ne réintègre pas automatiquement les valeurs manquantes.

Surveiller les listes et les comparaisons

3 IN (1, 2, NULL) vaut UNKNOWN : l’élément manquant pourrait être 3. Pour x NOT IN (1, 2, NULL), une valeur correspondante ne passe pas le test NOT IN et toute valeur sans correspondance donne UNKNOWN. Dans une clause WHERE, cette liste fixe ne conserve aucune ligne. Supprime tout NULL involontaire d’une liste d’exclusion avant de l’utiliser.

Pour une égalité qui considère deux NULL comme égaux, utilise a IS NOT DISTINCT FROM b. Son contraire, a IS DISTINCT FROM b, vaut TRUE lorsqu’une valeur est NULL et que l’autre ne l’est pas ; deux NULL ne sont pas distincts. PostgreSQL et SQLite à partir de la version 3.39 prennent en charge ces formes. SQLite possède aussi IS et IS NOT ; MySQL écrit l’égalité sûre avec NULL sous la forme <=>.

Le placement des NULL par défaut diffère selon la base : SQLite place NULL en premier dans l’ordre croissant ; PostgreSQL le place en dernier. Précise le placement lorsqu’il compte : ORDER BY bonus DESC NULLS LAST place les primes connues par ordre décroissant et les primes manquantes à la fin.

Les valeurs manquantes ont parfois besoin d’une valeur de repli et doivent parfois rester manquantes. Ce choix fait partie du sens d’un rapport. COALESCE comble une valeur manquante ; NULLIF transforme une valeur factice correspondante en valeur manquante.

Choisir la première valeur disponible

COALESCE(a, b, ...) examine les arguments de gauche à droite et renvoie le premier qui n’est pas NULL. La fonction renvoie NULL si tous les arguments sont NULL.

SELECT COALESCE(mobile_phone, home_phone, 'no phone') AS contact
FROM customers;

Sans numéro de portable et avec le numéro fixe '555-0100', le contact est '555-0100'. Si aucun des deux numéros n’est enregistré, c’est 'no phone'. Un zéro connu ou une chaîne vide compte comme disponible : COALESCE ignore NULL, pas les valeurs qui semblent vides ou fausses.

Pour la rémunération, salary + COALESCE(bonus, 0) donne 50 000 lorsque le salaire est de 50 000 et que la prime vaut NULL. Cela convient si une prime manquante signifie qu’aucune prime n’a été versée. Une note de produit manquante ne signifie pas une note de zéro ; la remplacer inventerait une mauvaise note. Laisse ce nombre manquant ou crée un libellé textuel pour l’affichage.

Garder des types compatibles pour les valeurs de repli

PostgreSQL ne peut pas combiner l’entier rating et le texte 'not rated' dans un même résultat numérique. Convertis d’abord les notes connues en texte :

COALESCE(CAST(rating AS TEXT), 'not rated')

Une note de 4 s’affiche sous forme du texte '4' ; un NULL s’affiche sous la forme 'not rated'. SQLite accepte des arguments numériques et textuels mélangés, mais la sortie peut alors contenir différents types. Rends le type de résultat attendu explicite lorsque tu transfères une requête d’une base à une autre.

COALESCE fait partie du SQL standard et accepte au moins deux arguments. Les variantes courantes à deux arguments sont IFNULL dans SQLite et MySQL, ISNULL dans SQL Server et NVL dans Oracle.

Transformer une valeur factice en NULL

NULLIF(a, b) renvoie NULL si les arguments sont égaux ; sinon, la fonction renvoie le premier argument. NULLIF(0, 0) vaut NULL, tandis que NULLIF(5, 0) vaut 5.

Voici une expression utile pour calculer un taux :

orders * 100.0 / NULLIF(visits, 0) AS conversion_pct

Avec 12 commandes et 300 visites, le diviseur reste égal à 300 et le taux est 4.0. Avec zéro visite, le diviseur devient NULL et le taux devient NULL. PostgreSQL évite une erreur de division par zéro et le résultat indique correctement un taux indéfini. Remplacer ce taux par zéro exprimerait une autre affirmation.

Nettoyer les valeurs manquantes avant de fournir un libellé

COALESCE(NULLIF(phone, ''), 'unknown')

Le NULLIF intérieur transforme une chaîne vide en NULL. Le COALESCE extérieur attribue ensuite le libellé 'unknown' aussi bien à un NULL d’origine qu’à une chaîne vide. Un numéro de téléphone enregistré est conservé tel quel.

Pour un deuxième prénom facultatif, inclus l’espace qui le suit dans la valeur de repli :

first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name

Un deuxième prénom NULL n’ajoute rien, ce qui produit 'Ana Lopez'. Un deuxième prénom 'Maria' ajoute 'Maria ', ce qui produit 'Ana Maria Lopez'.

Ces expressions ne modifient que le résultat de la requête. Elles laissent les valeurs d’origine stockées dans la table. Un filtre comme WHERE COALESCE(bonus, 0) = 0 regroupe les primes connues égales à zéro et les primes manquantes ; utilise-le donc lorsque ce sont les deux groupes recherchés.

SQL traite une valeur textuelle comme une chaîne de caractères. Les fonctions de texte peuvent construire un nom complet ou nettoyer une adresse e-mail pour la comparer. Leurs résultats apparaissent dans la requête ; le texte stocké reste inchangé.

Écrire et concaténer du texte

Les littéraux textuels utilisent des apostrophes. Pour inclure une apostrophe, double-la : 'O''Brien' représente « O'Brien ». Une paire d’apostrophes, '', est une chaîne vide.

SELECT first_name || ' ' || last_name AS full_name
FROM customers;

L’opérateur de concaténation || assemble les parties exactement telles qu’elles sont écrites. Avec 'Ana' et 'Lopez', le résultat est 'Ana Lopez'. Le littéral du milieu fournit l’espace ; la concaténation n’en insère pas automatiquement.

SQLite, PostgreSQL et Oracle utilisent ||. Dans MySQL, || signifie OR par défaut : utilise donc CONCAT. SQL Server utilise + et prend aussi en charge || à partir de 2025. Pour concaténer un identifiant numérique de commande, SQLite et PostgreSQL le convertissent en texte dans 'Order #' || order_id. Avec le + de SQL Server, convertis d’abord le nombre : 'Order #' + CAST(order_id AS VARCHAR(10)).

Choisir comment concaténer une partie manquante

Dans SQLite et PostgreSQL, un opérande NULL rend le résultat de || NULL. Oracle traite cette partie manquante comme une chaîne vide. CONCAT diffère aussi selon la base de données :

CONCAT dans Oracle prend exactement deux arguments. CONCAT_WS(' ', first_name, middle_name, last_name) place des espaces entre les parties qui ne sont pas NULL, donc un deuxième prénom manquant donne 'Ana Lopez'. SQLite a ajouté CONCAT et CONCAT_WS dans la version 3.44. Ignorer NULL ne supprime toutefois pas une chaîne vide.

Changer la casse et supprimer les espaces aux extrémités

UPPER('boston') donne 'BOSTON' ; LOWER produit des minuscules. INITCAP('ana LOPEZ') dans PostgreSQL donne 'Ana Lopez'. SQLite n’a pas INITCAP, et son changement de casse intégré ne modifie que les lettres de A à Z : UPPER('é') reste 'é' sans l’extension ICU. Le changement de casse dans PostgreSQL suit ses paramètres régionaux.

TRIM supprime les espaces aux deux extrémités ; LTRIM les supprime seulement au début et RTRIM seulement à la fin. TRIM(' New York ') donne 'New York', en conservant les deux espaces intérieurs. Pour supprimer x aux deux extrémités, SQLite écrit TRIM('xxBostonxx', 'x') ; PostgreSQL écrit TRIM(BOTH 'x' FROM 'xxBostonxx').

Remplacer des caractères et imbriquer les étapes de nettoyage

REPLACE('555-0100', '-', '') donne '5550100'. La fonction remplace chaque occurrence exacte. La casse compte : remplacer 'st' dans 'Main St' ne change rien, car 'St' comporte un S majuscule.

SELECT LOWER(TRIM(email)) AS email_clean
FROM customers;

Pour ' Ana@Example.COM ', TRIM produit d’abord 'Ana@Example.COM', puis LOWER produit 'ana@example.com'. L’imbrication signifie que la fonction la plus intérieure fournit l’entrée de la suivante.

La comparaison ordinaire de textes par égalité distingue par défaut les majuscules des minuscules dans SQLite et PostgreSQL. WHERE LOWER(TRIM(email)) = 'ana@example.com' peut trouver une correspondance avec une entrée dont la casse diffère et qui comporte des espaces aux extrémités. La collation par défaut de MySQL ignore la casse, et LIKE dans SQLite ignore la casse des lettres de A à Z, même si sa comparaison = par défaut ne l’ignore pas.

Les fonctions de texte repèrent et découpent des parties selon la position des caractères. En SQL, les positions commencent à 1 : le premier caractère est donc à la position 1. Une position trouvée et un nombre de caractères sont deux entrées différentes.

Mesurer et découper une chaîne

LENGTH('Boston') donne 6. Les espaces comptent : LENGTH(' Bo ') donne 4. Une chaîne vide a une longueur de 0 ; LENGTH(NULL) vaut NULL. Dans MySQL, LENGTH compte les octets : utilise donc CHAR_LENGTH pour compter les caractères. SQL Server utilise LEN, qui ignore les espaces en fin de chaîne.

SELECT SUBSTR('Boston', 1, 3) AS beginning,
       SUBSTR('Boston', 4) AS ending;

Les résultats sont 'Bos' et 'ton'. Le premier appel prend trois caractères à partir de la position 1. Le second omet le nombre de caractères et prend tout à partir de la position 4. PostgreSQL écrit aussi SUBSTRING('Boston' FROM 4 FOR 3). SQLite accepte le nom SUBSTRING à partir de la version 3.34. SQL Server utilise SUBSTRING(text, start, count) ; le nombre de caractères est obligatoire jusqu’à la version 2022 et facultatif à partir de 2025.

SQLite peut compter depuis la droite : SUBSTR('Boston', -3) donne 'ton'. Dans PostgreSQL, une position de départ négative ne compte pas à rebours depuis la fin. Utilise RIGHT('Boston', 3) ; LEFT prend les caractères depuis le début. SQLite n’a ni LEFT ni RIGHT.

Trouver le point de séparation

INSTR(text, search) dans SQLite renvoie la position de la première correspondance. PostgreSQL utilise STRPOS ou POSITION(search IN text). Ces fonctions renvoient 0 si aucune correspondance n’est trouvée. La recherche distingue les majuscules des minuscules : INSTR('Ana', 'a') vaut 3.

Dans 'ana@example.com', le @ est à la position 4. Le nom d’utilisateur se termine juste avant ; le domaine commence juste après :

SUBSTR(email, 1, INSTR(email, '@') - 1) AS user_name,
SUBSTR(email, INSTR(email, '@') + 1) AS domain

La première expression devient SUBSTR(email, 1, 3) et produit 'ana'. La seconde commence à 5 et produit 'example.com'. Omettre - 1 inclut @ dans le nom d’utilisateur. Omettre + 1 l’inclut au début du domaine. Dans PostgreSQL, remplace INSTR par STRPOS ou utilise SPLIT_PART(email, '@', 2) pour le domaine.

Gérer un séparateur manquant

Pour 'anaexample.com', INSTR renvoie 0. La formule du domaine commence à 1 et renvoie toute la chaîne, ce qui est trompeur. La formule du nom d’utilisateur dans SQLite donne une chaîne vide ; PostgreSQL refuse la longueur négative de la sous-chaîne. Protège l’extraction :

CASE
  WHEN INSTR(email, '@') > 0
  THEN SUBSTR(email, INSTR(email, '@') + 1)
END

Cette expression renvoie NULL lorsque le séparateur est absent. La vérification porte sur la présence d’un séparateur, pas sur toutes les règles de validité d’une adresse e-mail.

Construire des libellés courts et des codes de largeur fixe

SUBSTR(first_name, 1, 1) || '. ' || last_name donne 'A. Lopez' pour Ana Lopez. WHERE LENGTH(zip_code) <> 5 trouve les chaînes enregistrées comme codes postaux américains dont la longueur est incorrecte ; les longueurs NULL produisent UNKNOWN et sont exclues.

LPAD('42', 5, '0') dans PostgreSQL donne '00042' ; RPAD ajoute des caractères à droite. SQLite peut construire ce code avec SUBSTR('00000' || 42, -5). Ne garder que cinq caractères tronque aussi une valeur plus longue : cette expression à largeur fixe exige donc un code qui y tient.

Il est plus facile de calculer avec une date lorsque son stockage et son écriture sont clairs. Le texte '03/04/2026' pourrait signifier le 4 mars ou le 3 avril. Une date ISO, '2026-03-14', place l’année, le mois et le jour dans un ordre sans ambiguïté.

Associer la valeur à son type

PostgreSQL possède DATE pour un jour du calendrier, TIME pour une heure de la journée et TIMESTAMP pour une date et une heure. Son type TIMESTAMP WITH TIME ZONE, aussi appelé timestamptz, stocke un instant en UTC et l’affiche dans le fuseau horaire de la session. Un TIMESTAMP simple n’inclut pas cette information de fuseau.

SQLite stocke les dates et les heures sous forme de texte ISO ou de nombres, sans types dédiés aux dates et aux heures. Les formes numériques comprennent les jours juliens et le temps Unix. Les fonctions de date interprètent ces valeurs et renvoient une date, une heure ou une mesure numérique.

Utilise du texte ISO de largeur fixe comme '2026-03-14', avec les zéros initiaux. Ajoute une heure au format 24 heures : '2026-03-14 09:30:00'. Le littéral typé DATE '2026-03-14' de PostgreSQL crée explicitement une date.

Préserver l’ordre chronologique

Le texte ISO de largeur fixe se compare dans l’ordre des dates, car il commence par la plus grande composante. '2026-03-14' vient avant '2026-11-02'. Le texte au format d’affichage américain n’a pas cette propriété : en tant que chaînes, '11/02/2025' est trié après '03/14/2026', même si sa date est antérieure.

L’analyseur de SQLite exige un format pris en charge. date('2026-3-14') renvoie NULL parce que le mois ne comporte pas deux chiffres. Il peut aussi normaliser une date impossible : date('2026-02-30') renvoie '2026-03-02'. Un résultat qui n’est pas NULL ne prouve pas à lui seul que l’entrée était un jour valide du calendrier. PostgreSQL rejette DATE '2026-02-30'.

Lire du texte dans un autre format

PostgreSQL et Oracle utilisent TO_DATE('03/14/2026', 'MM/DD/YYYY') pour lire un format américain. PostgreSQL lit aussi le texte ISO avec CAST('2026-03-14' AS DATE) ou '2026-03-14'::date.

SQLite n’a pas d’analyseur TO_DATE. Pour du texte de format fixe MM/DD/YYYY dans d, reconstruis le texte ISO :

SUBSTR(d, 7, 4) || '-' ||
SUBSTR(d, 1, 2) || '-' || SUBSTR(d, 4, 2)

Les parties sont l’année '2026', le mois '03' et le jour '14'. Le résultat est '2026-03-14', que les fonctions de date de SQLite peuvent lire. Ces positions supposent que l’entrée utilise effectivement le format fixe.

Obtenir la date et l’heure actuelles

CURRENT_DATE et CURRENT_TIMESTAMP fonctionnent dans SQLite et PostgreSQL. SQLite renvoie du texte en UTC ; date('now', 'localtime') donne la date locale. Près de minuit, la date UTC et la date locale peuvent différer.

PostgreSQL utilise le fuseau horaire de la session. CURRENT_TIMESTAMP et NOW() correspondent au début de la transaction en cours : attendre dans cette transaction ne fait donc pas avancer leur valeur. MySQL propose NOW() et CURDATE() ; SQL Server possède GETDATE() et SYSDATETIME() ; Oracle possède SYSDATE, qui comprend une heure, et CURRENT_DATE.

Pour vérifier le calcul d’une échéance, utilise d’abord une date de référence fixe comme DATE '2026-10-21' dans PostgreSQL ou '2026-10-21' dans SQLite. Tu peux alors comparer le résultat à un calendrier sans que la réponse change le lendemain.

Extrais une composante de date lorsque tu as besoin d’une année ou d’un jour de la semaine pour calculer. Mets une date en forme lorsque tu as besoin de texte pour un lecteur. Ces opérations peuvent produire des types différents, même si les caractères affichés se ressemblent.

Extraire une année, un mois ou un jour de la semaine

PostgreSQL utilise la forme standard EXTRACT(part FROM value). Pour DATE '2026-03-14', l’extraction de YEAR donne 2026 et celle de MONTH donne 3, tous deux sous forme de nombres. MySQL et Oracle prennent aussi en charge EXTRACT.

SQLite utilise strftime(format, value), qui renvoie du texte :

Le %w de SQLite et le DOW de PostgreSQL numérotent les jours de 0 pour le dimanche à 6 pour le samedi. Le 14 mars 2026 est un samedi. ISODOW dans PostgreSQL les numérote de 1 pour le lundi à 7 pour le dimanche. Un filtre de week-end dans SQLite est WHERE strftime('%w', order_date) IN ('0', '6').

Comparer les bons types

strftime('%m', order_date) = 3 est faux pour une date de mars dans SQLite : la fonction renvoie le texte '03', et non le nombre 3. Compare-le avec = '03' ou convertis-le d’abord :

CAST(strftime('%m', order_date) AS INTEGER) = 3

C’est aussi utile dans l’expression du trimestre (CAST(strftime('%m', d) AS INTEGER) + 2) / 3. La division entière donne le trimestre 1 pour mars et le trimestre 4 pour novembre. PostgreSQL possède directement EXTRACT(QUARTER FROM d).

Identifier un mois comme une période entière

strftime('%Y-%m', order_date) crée un libellé SQLite comme '2026-03'. date(order_date, 'start of month') crée '2026-03-01'. PostgreSQL peut utiliser TO_CHAR(order_date, 'YYYY-MM') pour un libellé ou DATE_TRUNC('month', order_date) pour le premier instant de ce mois.

Conserve l’année dans un libellé de période. La seule valeur de mois 3 réunirait les mois de mars de différentes années. La valeur correspondant au premier jour du mois peut rester la valeur utilisée pour les calculs et le tri, tandis qu’une expression distincte fournit l’affichage.

Mettre en forme à l’étape de sortie

Dans PostgreSQL, TO_CHAR(DATE '2026-03-14', 'MM/DD/YYYY') donne '03/14/2026', et le modèle 'Mon DD, YYYY' donne 'Mar 14, 2026'. Oracle utilise aussi ces modèles. Le format américain dans SQLite est strftime('%m/%d/%Y', '2026-03-14').

Les lettres des modèles propres à chaque base de données ont leur signification. Dans PostgreSQL, MM signifie mois et MI minute. Dans SQLite, %m signifie mois et %M minute ; changer cette majuscule change l’information demandée.

SELECT TO_CHAR(order_date, 'MM/DD/YYYY') AS order_day
FROM orders
ORDER BY order_date;

Ici, PostgreSQL affiche du texte lisible mais trie les dates réelles. Trier order_day trierait les chaînes d’affichage d’abord par mois et pourrait placer dans le désordre des dates d’années différentes.

Les calculs calendaires doivent tenir compte de la longueur des mois, des composantes horaires et du type de réponse recherché. Un nombre de jours, un mois calendaire et une année d’âge révolue sont des mesures différentes.

Ajouter des jours et des mois calendaires

PostgreSQL écrit DATE '2026-03-14' + 30 ; SQLite écrit date('2026-03-14', '+30 days'). Les deux atteignent le 13 avril : 17 jours terminent le mois de mars et 13 jours supplémentaires mènent au 13 avril. L’expression directe '2026-03-14' + 30 dans SQLite donne quant à elle 2056, car l’opération arithmétique lit l’année initiale comme un nombre.

Les fins de mois exigent une règle explicite. DATE '2026-01-31' + INTERVAL '1 month' dans PostgreSQL donne le 28 février à minuit, en utilisant le dernier jour disponible. date('2026-01-31', '+1 month') dans SQLite reporte les jours excédentaires sur mars et donne '2026-03-03'. À partir de SQLite 3.46, ajoute 'floor' après le décalage pour choisir '2026-02-28'. ADD_MONTHS dans Oracle passe aussi au dernier jour du mois plus court.

Mesurer le temps écoulé

Dans PostgreSQL, la soustraction de DATE renvoie des jours entiers : DATE '2026-04-13' - DATE '2026-03-14' vaut 30. SQLite soustrait des valeurs numériques de jours : julianday('2026-04-13') - julianday('2026-03-14') vaut 30.0. SQL Server utilise DATEDIFF(day, start, end) ; MySQL utilise DATEDIFF(end, start).

Pour des horodatages allant du 14 mars à 09 h 30 à 18 h 00, la soustraction dans PostgreSQL donne un intervalle de 08:30:00 ; EXTRACT(EPOCH FROM interval) / 3600 l’exprime sous la forme de 8,5 heures. À partir de SQLite 3.38, on peut écrire :

(unixepoch('2026-03-14 18:00') -
 unixepoch('2026-03-14 09:30')) / 3600.0

Cela donne 8.5. Une différence de jours juliens multipliée par 24 peut donner 8.49999999627471 à cause de l’approximation à virgule flottante ; arrondis pour l’affichage. Une durée fractionnaire entre horodatages ne doit pas être convertie en INTEGER avant que l’unité souhaitée ait été calculée.

Compter les anniversaires passés

Une personne née le 2 novembre 1990 a 35 ans le 21 octobre 2026. Les années diffèrent de 36, mais l’anniversaire n’est pas encore arrivé. PostgreSQL utilise EXTRACT(YEAR FROM AGE(DATE '2026-10-21', DATE '1990-11-02')), ce qui donne 35.

SQLite soustrait un si l’anniversaire de cette année n’est pas encore arrivé :

strftime('%Y', '2026-10-21') - strftime('%Y', birth_date)
- (strftime('%m-%d', '2026-10-21') < strftime('%m-%d', birth_date))

La comparaison donne 1 si l’anniversaire est encore à venir et 0 sinon. Diviser les jours écoulés par 365 n’est pas fiable : le 1er novembre 2026, cette personne a vécu 13 148 jours, ce qui, divisé par 365, donne 36,02 alors que son 36e anniversaire est demain. DATEDIFF dans SQL Server compte les frontières : passer du 31 décembre au 1er janvier compte comme une frontière d’année, et non comme une année d’âge révolue.

Inclure tous les horodatages d’une période

BETWEEN inclut les deux bornes. Pour les colonnes DATE, du 1er mars au 31 mars couvre le mois de mars. Pour les horodatages, une borne supérieure de '2026-03-31' signifie minuit au début du dernier jour et manque le reste de cette journée.

WHERE ordered_at >= '2026-03-01'
  AND ordered_at < '2026-04-01'

Cette plage semi-ouverte inclut le début et exclut le début de la période suivante, couvrant ainsi toutes les heures de mars sans choisir une dernière fraction de seconde.

Les dates échues satisfont due_date < CURRENT_DATE. La borne inférieure des 30 derniers jours est CURRENT_DATE - 30 dans PostgreSQL et date('now', '-30 days') dans SQLite. Le nombre de jours jusqu’à une échéance est donné par due_date - CURRENT_DATE pour les valeurs DATE dans PostgreSQL et par julianday(due_date) - julianday(date('now')) dans SQLite.

Une expression CASE choisit un résultat pour chaque ligne. Tu peux l’utiliser pour attribuer un libellé à une tranche de prix, calculer des frais ou marquer un enregistrement qui mérite une attention particulière.

Lire la forme recherchée

CASE
  WHEN total < 50 THEN 'small'
  WHEN total < 200 THEN 'medium'
  ELSE 'large'
END AS size_band

Chaque WHEN fournit une condition et THEN fournit la valeur à renvoyer si cette condition vaut TRUE. La base de données vérifie les conditions de haut en bas et retient la première condition vraie. END ferme l’expression ; un alias peut le suivre. Si aucune condition ne vaut TRUE et que ELSE est absent, le résultat est NULL.

Un total de 35 remplit la première condition et devient 'small'. Un total de 50 ne remplit pas la première mais remplit la deuxième et devient 'medium'. Il en va de même pour 199,99. Un total de 200 atteint ELSE et devient 'large'.

Faire correspondre l’ordre et les bornes à la règle

La deuxième branche n’a pas besoin de total >= 50 : une valeur inférieure à 50 a déjà donné lieu à un résultat dans la première branche. Les seuils croissants avec < fonctionnent parce que les branches précédentes ont déjà traité les valeurs plus petites. Des seuils décroissants avec >= peuvent fonctionner pour la même raison.

Placer WHEN total < 200 en premier rend inaccessible une branche < 50 ultérieure. Un total de 35 satisfait déjà < 200, donc il s’arrête là. CASE ne choisit pas la condition la plus précise et ne vérifie pas quel libellé semble le mieux convenir.

Compare la règle métier à chaque borne. < 50 place exactement 50 dans la tranche moyenne ; <= 50 le place dans la petite tranche. Vérifie 49,99, 50, 199,99 et 200 pour examiner les deux côtés et les bornes. Si les conditions sont écrites sous forme d’intervalles explicites, fais correspondre leurs bornes sans laisser de vide ni de chevauchement involontaire.

Traiter NULL avant les plages ordinaires

Un total NULL rend les deux comparaisons UNKNOWN. Dans la première expression, il atteint donc ELSE et devient 'large', ce qui est trompeur. Détecte d’abord les valeurs manquantes :

CASE
  WHEN total IS NULL THEN 'unknown'
  WHEN total < 50 THEN 'small'
  WHEN total < 200 THEN 'medium'
  ELSE 'large'
END

ELSE signifie « aucune condition n’était vraie » : il peut donc prendre en compte les entrées manquantes aussi bien que les valeurs connues situées hors des plages testées.

Renvoyer un type de valeur cohérent

PostgreSQL exige des types compatibles pour THEN et ELSE. CASE WHEN quantity > 0 THEN unit_price ELSE 'n/a' END échoue si unit_price est numérique, car 'n/a' ne peut pas devenir un nombre. Utilise NULL pour un résultat numérique indisponible, ou convertis le prix en texte si toute la sortie est un libellé d’affichage. SQLite autorise des types de résultats mélangés, ce qui peut compliquer les calculs ultérieurs.

Une condition peut combiner des colonnes :

CASE
  WHEN country = 'US' AND subtotal >= 100 THEN 0
  ELSE 7.99
END AS shipping_fee

Les frais renvoyés peuvent figurer dans subtotal + CASE ... END AS amount_due. Un CASE est une expression qui produit une valeur, pas une commande qui modifie plusieurs colonnes ; chaque colonne de résultat a besoin de sa propre expression.

CASE peut aussi protéger une division effectuée sur chaque ligne : CASE WHEN visits = 0 THEN NULL ELSE orders * 100.0 / visits END. Une ligne dont la valeur est nulle sélectionne la branche NULL. orders * 100.0 / NULLIF(visits, 0) exprime la même règle de taux manquant plus brièvement.

La forme CASE simple traduit une valeur en une autre. Le CASE recherché teste des conditions ; le CASE simple compare la même expression à une série de valeurs.

Traduire des codes avec le CASE simple

CASE status
  WHEN 'S' THEN 'Shipped'
  WHEN 'P' THEN 'Pending'
  WHEN 'C' THEN 'Canceled'
  ELSE 'Other'
END AS status_label

Les tests implicites sont status = 'S', puis status = 'P', puis status = 'C'. Un statut hors de ces codes reçoit 'Other'. La forme recherchée peut écrire explicitement les mêmes tests d’égalité. Choisis le CASE recherché pour les plages, les motifs LIKE, les tests NULL ou les conditions portant sur plusieurs colonnes.

CASE region WHEN NULL THEN 'missing' ELSE region END ne peut pas détecter une région NULL. Son test implicite region = NULL vaut UNKNOWN, donc la ligne atteint ELSE et reste NULL. Écris CASE WHEN region IS NULL THEN 'missing' ELSE region END.

Attribuer un rang de tri personnalisé

L’ordre alphabétique des priorités est high, low, medium. Pour trier selon l’urgence, traduis les libellés en nombres :

SELECT ticket_id, priority
FROM tickets
ORDER BY CASE priority
           WHEN 'high' THEN 1
           WHEN 'medium' THEN 2
           WHEN 'low' THEN 3
           ELSE 4
         END;

L’ordre croissant place maintenant high avant medium, puis low, avec les autres valeurs ou les valeurs manquantes en dernier. Le CASE est une expression de tri ; il n’a pas besoin d’être une colonne affichée. Utiliser DESC inverserait le classement numérique.

Créer un indicateur numérique

CASE WHEN discount_pct > 0 THEN 1 ELSE 0 END AS is_discounted

Un pourcentage positif reçoit 1 ; un zéro connu reçoit 0. Un pourcentage NULL reçoit aussi 0 parce que > 0 vaut UNKNOWN et que ELSE le prend en compte. Cet indicateur signifie donc « une remise positive est enregistrée ». Il ne conserve pas la distinction entre l’absence de remise et une remise inconnue. Si cette distinction est nécessaire, ajoute une branche IS NULL avec son propre résultat.

Les indicateurs peuvent être sélectionnés, triés, filtrés ou inclus dans des opérations arithmétiques. Dans une clause WHERE portable, répète l’expression ou utilise sa condition d’origine au lieu de faire référence à un alias de sortie nouvellement défini.

Reconnaître les fonctions conditionnelles associées

COALESCE(bonus, 0) est une forme abrégée de CASE WHEN bonus IS NOT NULL THEN bonus ELSE 0 END. NULLIF(a, b) correspond à CASE WHEN a = b THEN NULL ELSE a END. Leurs noms communiquent directement ces usages courants.

SQLite à partir de la version 3.32 et SQL Server possèdent IIF(condition, true_value, false_value) ; MySQL possède IF. DECODE dans Oracle traduit les correspondances par égalité, par exemple DECODE(status, 'S', 'Shipped', 'P', 'Pending', 'Other'). DECODE considère deux NULL comme égaux, contrairement aux comparaisons d’égalité du CASE simple. Le CASE standard fonctionne dans ces systèmes.

Un CASE peut contenir un autre CASE, mais des conditions combinées rendent souvent une expression sans imbrication plus claire. Une règle d’expédition peut dire directement WHEN country = 'US' AND subtotal >= 100 THEN 0, en gardant ensemble les tests sur le pays et le sous-total.

Un rapport peut nettoyer des noms, calculer des totaux nets, identifier des mois et attribuer un libellé à la rapidité d’expédition dans un seul SELECT. Construis chaque expression à partir de ses entrées et vérifie le résultat avant de la combiner aux autres.

Partir de lignes connues

Une table de commandes contient ces enregistrements. Les dates sont du texte ISO pour la requête SQLite ci-dessous ; quantity est un entier et unit_price est numérique.

Ici, une remise manquante signifie qu’il n’y a pas de remise. Commence par extraire les champs bruts. Ajoute une colonne calculée à la fois et utilise LIMIT 5 pour vérifier un petit échantillon.

Suivre un calcul imbriqué

ROUND(quantity * unit_price *
      (1 - COALESCE(discount_pct, 0) / 100.0), 2)

COALESCE fournit d’abord une remise connue. La division par 100.0 conserve sa partie fractionnaire. La soustraction à 1 donne la fraction payée, la multiplication donne le montant net et ROUND fixe la précision finale. Le montant d’Ana est 33.75, celui de Ben est 240.0 et celui de Chen est 51.0. La remise NULL de Chen devient zéro ; une date d’expédition manquante, en revanche, doit rester manquante dans le nombre de jours.

Assembler le rapport SQLite

SELECT order_id,
       first_name || ' ' || last_name AS customer,
       ROUND(quantity * unit_price *
         (1 - COALESCE(discount_pct, 0) / 100.0), 2) AS net_total,
       strftime('%Y-%m', order_date) AS order_month,
       CAST(julianday(shipped_date) - julianday(order_date)
         AS INTEGER) AS days_to_ship,
       CASE
         WHEN shipped_date IS NULL THEN 'not shipped'
         -- fast = shipped within 2 days
         WHEN julianday(shipped_date) - julianday(order_date) <= 2
           THEN 'fast'
         ELSE 'standard'
       END AS shipping
FROM orders
ORDER BY order_id;

Chaque expression possède un alias de sortie explicite. La différence numérique en jours est convertie en INTEGER car ces valeurs ne contiennent que des dates. La branche IS NULL vient avant la comparaison de rapidité, de sorte qu’une commande non expédiée reçoit un libellé utile.

Le résultat est :

Adapter les types et les clauses de la requête

Pour des colonnes DATE dans PostgreSQL, utilise TO_CHAR(order_date, 'YYYY-MM') pour le mois et shipped_date - order_date pour le nombre de jours et la comparaison dans CASE. Garde unit_price dans le type exact numeric pour ROUND à deux arguments. La concaténation, la valeur de repli pour la remise et la règle de classement gardent le même sens.

Pour filtrer les totaux arrondis supérieurs à 50 dans les deux bases, répète l’expression complète du montant net dans WHERE. Les commandes 1002 et 1003 restent. SQLite autorise aussi WHERE net_total > 50, mais PostgreSQL ne peut pas voir ce nouvel alias à l’étape du filtrage. ORDER BY net_total DESC fonctionne dans les deux.

Lorsque tu transfères un rapport, vérifie les types et les noms de fonctions dans chaque expression. Une fonction de texte doit recevoir du texte, un calcul de date doit recevoir une valeur de date prise en charge et une valeur de repli numérique doit exprimer la règle voulue. Place chaque résultat sur sa propre ligne ; un court commentaire peut définir un seuil métier sans rendre le SQL difficile à lire.