COALESCE, NULLIF et ISNULL
Remplacer les valeurs manquantes par des valeurs par défaut et distinguer COALESCE des fonctions propres à chaque fournisseur
COALESCE, NULLIF et ISNULL est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.
Remplacer les valeurs NULL
Maintenant que vous savez détecter NULL, la compétence suivante à maîtriser en entretien consiste à le remplacer par une valeur par défaut pertinente. L’outil standard et portable pour cela est COALESCE.
Vous découvrirez également NULLIF, qui agit en sens inverse en transformant une valeur précise en NULL, ainsi que les fonctions propres à certains fournisseurs, ISNULL (SQL Server) et IFNULL (MySQL), que les candidats confondent souvent avec COALESCE.
Connaître précisément leurs différences, notamment le nombre d’arguments et le type de retour, est une question fréquente lors des sélections.
Principes de COALESCE
COALESCE accepte un nombre quelconque d’arguments et renvoie le premier argument qui n’est pas NULL, en les parcourant de gauche à droite. Si tous les arguments sont NULL, la fonction renvoie NULL.
Cette fonction est conforme au standard ANSI et fonctionne avec toutes les bases de données majeures, raison pour laquelle elle doit être votre réponse par défaut. Utilisez-la pour fournir des solutions de repli à l’affichage, aux calculs ou au regroupement.
-- Show 0 instead of NULL for missing bonuses
SELECT name, COALESCE(bonus, 0) AS bonus
FROM employees;
-- Multiple fallbacks, first non-NULL wins
SELECT COALESCE(mobile_phone, home_phone, 'no phone') AS contact
FROM customers;COALESCE utilise l’évaluation en court-circuit
Voici un point subtil que les recruteurs aiment vérifier : conceptuellement, COALESCE évalue les arguments de gauche à droite et s’arrête au premier qui n’est pas NULL. Ainsi, une expression coûteuse située plus loin n’est pas nécessaire dès qu’une expression précédente produit un résultat.
En pratique, les optimiseurs peuvent tout de même effectuer une évaluation anticipée avec certains moteurs : ne comptez donc pas sur ce comportement pour éviter des erreurs comme une division par zéro. En revanche, la priorité de gauche à droite qui détermine la valeur retenue est garantie.
-- Prefer the manual override, else the computed value,
-- else a constant default
SELECT COALESCE(manual_price, list_price * 1.1, 9.99) AS price
FROM products;COALESCE et le type de données du résultat
Voici un piège subtil : le type de données du résultat de COALESCE est déterminé par la priorité combinée des types de tous ses arguments, et pas uniquement par le premier. Mélanger des types incompatibles peut provoquer des erreurs ou une troncature inattendue.
Par exemple, appliquer COALESCE à une colonne entière et à une valeur par défaut textuelle peut échouer ou entraîner une conversion implicite, selon le moteur. Les recruteurs utilisent ce cas pour vérifier que vous tenez compte des types.
-- Risky: integer column with a string fallback
-- may error or force a cast depending on dialect
SELECT COALESCE(score, 'N/A') FROM tests;
-- Safer: keep the fallback type-compatible, or cast explicitly
SELECT COALESCE(CAST(score AS VARCHAR), 'N/A') FROM tests;ISNULL (SQL Server) et COALESCE
SQL Server propose ISNULL(expr, replacement). Cette fonction ressemble à COALESCE, mais elle s’en distingue par plusieurs points importants que les recruteurs aiment comparer :
- Nombre d’arguments : ISNULL en accepte exactement deux ; COALESCE peut en accepter plusieurs.
- Type de retour : ISNULL utilise le type du premier argument, ce qui peut tronquer la valeur de remplacement. COALESCE utilise la priorité combinée des types.
- Portabilité : ISNULL est propre à SQL Server ; COALESCE est conforme au standard ANSI.
Recommandation à formuler à voix haute : privilégiez COALESCE pour sa portabilité et son typage prévisible.
-- SQL Server: ISNULL may truncate the replacement to
-- the first argument's type (e.g. CHAR(1))
SELECT ISNULL(code, 'UNKNOWN') FROM items;
-- If code is CHAR(1), 'UNKNOWN' becomes 'U'
-- COALESCE picks the wider type and keeps 'UNKNOWN'
SELECT COALESCE(code, 'UNKNOWN') FROM items;IFNULL et NVL
Les autres dialectes possèdent leurs propres raccourcis à deux arguments :
- MySQL / SQLite :
IFNULL(expr, replacement) - Oracle :
NVL(expr, replacement), ainsi queNVL2pour une variante avec une valeur selon le cas
Ces trois fonctions se comportent comme un COALESCE à deux arguments. Si l’on vous demande spécifiquement la syntaxe idiomatique de MySQL ou d’Oracle, citez ces fonctions ; sinon, choisissez COALESCE.
-- MySQL
SELECT IFNULL(bonus, 0) FROM employees;
-- Oracle
SELECT NVL(bonus, 0) FROM employees;
-- NVL2(bonus, 'has bonus', 'no bonus') -> if/else on NULLNULLIF : le sens inverse
NULLIF(a, b) renvoie NULL lorsque a = b ; sinon, elle renvoie a. Cette fonction crée volontairement un NULL, ce qui est l’inverse de COALESCE.
Son utilisation la plus connue consiste à éviter la division par zéro. Entourez le dénominateur de NULLIF(denominator, 0) : s’il vaut zéro, le diviseur devient NULL et toute la division renvoie NULL au lieu de déclencher une erreur.
-- Avoid divide-by-zero: returns NULL instead of erroring
SELECT revenue / NULLIF(orders, 0) AS avg_order_value
FROM daily_stats;
-- NULLIF(5, 5) -> NULL
-- NULLIF(5, 3) -> 5Combiner NULLIF et COALESCE
Ces deux fonctions se complètent parfaitement. Une formulation classique en entretien est la suivante : « effectuer une division sûre qui affiche 0 lorsqu’il n’y a aucune commande ». Utilisez NULLIF pour éviter l’erreur, puis COALESCE pour remplacer le NULL obtenu.
Cette syntaxe concise témoigne de votre maîtrise : vous gérez le cas limite et la présentation dans une seule expression.
SELECT
COALESCE(revenue / NULLIF(orders, 0), 0) AS avg_order_value
FROM daily_stats;
-- orders = 0 -> NULLIF gives NULL -> division gives NULL
-- -> COALESCE turns it into 0Traiter les chaînes vides comme NULL
NULLIF est également très utile pour convertir les chaînes vides en NULL afin de pouvoir les traiter uniformément avec COALESCE. Les données incorrectes mélangent souvent NULL et '' ; cette technique uniformise les deux cas.
Interprétez ce modèle ainsi : « si la valeur est vide, transformez-la en NULL, puis utilisez une valeur par défaut ». C’est une réponse claire et portable à la question : « Comment traiter les chaînes vides et les valeurs manquantes de la même manière ? »
-- Treat both '' and NULL as missing, default to 'Anonymous'
SELECT COALESCE(NULLIF(TRIM(username), ''), 'Anonymous')
FROM users;Exemple approfondi : utiliser COALESCE après des JOIN
Après un LEFT JOIN, les lignes sans correspondance produisent des NULL du côté droit. COALESCE les transforme en valeurs par défaut pertinentes dans le résultat, ce qui répond très souvent à un besoin des rapports.
Ici, les clients sans commande apparaissent tout de même (grâce à LEFT JOIN) et leur total vaut 0 plutôt que NULL. Préciser que COALESCE s’applique après la jointure, et non à l’intérieur de celle-ci, montre que vous comprenez l’ordre d’évaluation.
SELECT
c.name,
COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
-- Customers with no orders get 0 instead of NULLPoints à aborder en entretien
Résumé de l’outillage de substitution :
- COALESCE(a, b, ...) : premier argument non-NULL, plusieurs arguments possibles, standard ANSI, type déterminé par priorité. Choix par défaut.
- ISNULL / IFNULL / NVL : raccourcis propres aux fournisseurs, à deux arguments ; ISNULL peut tronquer le résultat selon le type du premier argument.
- NULLIF(a, b) : renvoie NULL lorsque les valeurs sont égales ; très utile pour éviter les divisions par zéro et uniformiser les chaînes vides.
- Combinez
COALESCE(x / NULLIF(y, 0), 0)pour obtenir une division sûre et présentable.
Commencez par COALESCE et ne mentionnez les variantes propres aux fournisseurs que lorsque le dialecte est imposé.
Vérification rapide
Choisissez l’expression de division sûre.
Récapitulatif
Vous savez maintenant remplacer et créer des valeurs NULL :
- COALESCE renvoie le premier argument non-NULL parmi plusieurs ; c’est le choix portable par défaut.
- ISNULL (SQL Server), IFNULL (MySQL) et NVL (Oracle) sont des raccourcis à deux arguments ; ISNULL peut tronquer le résultat selon le type du premier argument.
- NULLIF(a, b) renvoie NULL lorsque les deux valeurs sont égales : cette fonction est idéale pour éviter les divisions par zéro et uniformiser les chaînes vides.
- Combinez-les pour obtenir des expressions sûres et présentables, ainsi que pour remplacer par défaut les NULL issus d’un LEFT JOIN.
Dernière leçon : le comportement de NULL dans les agrégats, les jointures et DISTINCT.
Questions Fréquemment Posées
La leçon « COALESCE, NULLIF et ISNULL » est-elle gratuite ?
Oui — le texte complet de « COALESCE, NULLIF et ISNULL » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « COALESCE, NULLIF et ISNULL » ?
Remplacer les valeurs manquantes par des valeurs par défaut et distinguer COALESCE des fonctions propres à chaque fournisseur Tu pratiques SQL Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer SQL Interview Prep ?
Aucune expérience préalable n'est requise. SQL Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.
Combien de temps prend la leçon « COALESCE, NULLIF et ISNULL » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon SQL Interview Prep ?
Oui. Chaque leçon SQL Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Logique à trois valeurs et UNKNOWN
- IS NULL, IS NOT NULL et égalité compatible avec NULL
- COALESCE, NULLIF et ISNULL
- NULL dans les agrégats, les jointures et DISTINCT