SQLite et NULL : IS NULL, COALESCE et IFNULL

NULL signifie « inconnu »

Dans SQLite, toutes les autres valeurs représentent quelque chose de précis : un nombre, une chaîne, un blob. NULL, lui, joue dans une autre catégorie. C'est un marqueur qui indique une valeur manquante ou inconnue. Cette simple idée explique à elle seule tout le comportement bizarre de NULL dans vos requêtes.

Créons une petite table pour expérimenter :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT * FROM users;

Deux colonnes acceptent les valeurs NULL. Boris n'a pas d'email. Cleo n'a pas d'âge. Dan n'a ni l'un ni l'autre. Le reste de la page explique comment interroger ce genre de lignes sans se faire piéger.

= et <> ne fonctionnent pas avec NULL

Le réflexe naturel, c'est d'écrire WHERE email = NULL. Ça paraît logique. Et pourtant, ça ne renvoie rien :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email = NULL;

Zéro ligne — alors que Boris et Dan ont visiblement un email à NULL. La raison ? Toute comparaison avec NULL renvoie NULL, et non vrai ou faux. La clause WHERE de SQLite ne conserve que les lignes pour lesquelles la condition est vraie, or NULL n'est pas vrai. Du coup, la ligne passe à la trappe.

Même piège avec <> :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email <> 'ada@example.com';

Vous vous attendez sans doute à récupérer tout le monde sauf Ada. Eh bien non, seul Cleo ressort. Boris et Dan, dont l'email vaut null, passent à la trappe — parce que NULL <> 'ada@example.com' renvoie lui aussi NULL, et pas vrai.

C'est LE piège classique de SQL. Dès qu'une requête « perd » des lignes que vous attendiez, le coupable est presque toujours une colonne qui contient des null.

Utiliser IS NULL et IS NOT NULL

Pour tester un null correctement, il faut passer par l'opérateur IS. Contrairement à =, il sait gérer les null et renvoie toujours vrai ou faux — jamais null :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name FROM users WHERE email IS NULL;

SELECT name FROM users WHERE email IS NOT NULL;

La première requête renvoie Boris et Dan, la seconde renvoie Ada et Cleo. IS NULL et IS NOT NULL sont les deux opérateurs conçus précisément pour répondre à la question « cette valeur est-elle absente ? ». Utilisez-les partout où vous seriez tenté d'écrire = NULL ou <> NULL.

Si vous voulez « tout sauf Ada, y compris les valeurs inconnues », il faut combiner les deux conditions explicitement :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email
FROM users
WHERE email <> 'ada@example.com' OR email IS NULL;

Et là, Boris, Cleo et Dan apparaissent tous les trois.

NULL se propage dans les calculs et la concaténation

La règle de l'« inconnu » ne se limite pas aux comparaisons. Toute opération qui touche un NULL renvoie un NULL :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    age,
    age + 1        AS next_year,
    age * 2        AS doubled,
    'Salut ' || name AS greeting,
    'Âge : ' || age AS labelled_age
FROM users;

next_year et doubled valent NULL pour Cleo et Dan. labelled_age aussi : concaténer une chaîne avec NULL donne NULL, pas 'Âge : '. Si une colonne peut être nulle et que vous voulez récupérer une valeur exploitable derrière, il faut gérer le cas. C'est précisément le rôle des deux fonctions qui suivent.

IFNULL : la valeur de repli à deux arguments

IFNULL(a, b) renvoie a, sauf si c'est NULL — auquel cas elle renvoie b. C'est la façon la plus directe de remplacer un NULL par une valeur par défaut :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    IFNULL(email, '(aucun e-mail)') AS email,
    IFNULL(age, 0)                  AS age
FROM users;

Boris et Dan reçoivent (aucun e-mail). Cleo et Dan obtiennent 0. Les données d'origine ne bougent pas — IFNULL se contente de réécrire ce qui sort de la requête.

IFNULL prend toujours exactement deux arguments. S'il vous faut plusieurs valeurs de repli, c'est COALESCE qu'il faut utiliser.

COALESCE : la première valeur non NULL l'emporte

COALESCE(a, b, c, ...) parcourt ses arguments dans l'ordre et renvoie le premier qui n'est pas null. C'est en quelque sorte la version généralisée d'IFNULL, capable d'enchaîner autant de valeurs de repli qu'on veut :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    COALESCE(email, name || '@inconnu.local', 'anonyme') AS contact
FROM users;

Pour Ada et Cleo, c'est l'email qui sort. Pour Boris et Dan, l'email est NULL, donc SQLite passe au deuxième argument : une adresse fabriquée à partir du nom. Et si celui-ci était également NULL, on retomberait sur 'anonyme'.

COALESCE reste le choix portable : toutes les grandes bases SQL le gèrent de la même façon. IFNULL, lui, est un raccourci propre à SQLite et MySQL, limité à deux arguments. Par défaut, partez sur COALESCE ; ne sortez IFNULL que si vous avez vraiment juste deux arguments et que vous tenez au nom plus court.

NULL n'est pas une chaîne vide en SQLite

Une confusion classique : croire que NULL et '' sont interchangeables. Ce n'est pas le cas.

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    '' IS NULL          AS empty_is_null,
    NULL IS NULL        AS null_is_null,
    length('')          AS empty_length,
    length(NULL)        AS null_length,
    '' = ''             AS empty_equals_empty,
    NULL = NULL         AS null_equals_null;

'' est une vraie chaîne de caractères, elle a juste zéro caractère. NULL, lui, représente l'absence de valeur. length('') renvoie 0 ; length(NULL) renvoie... NULL. Et NULL = NULL vaut NULL, pas 1 — c'est précisément pour ça que IS NULL existe.

Si une colonne peut contenir à la fois '' et NULL, choisissez lequel des deux signifie « valeur manquante » et tenez-vous à cette règle. Mélanger les deux oblige chaque requête à gérer deux cas, et vous allez forcément en oublier un.

NULL avec IN, NOT IN et DISTINCT

Encore quelques endroits où le NULL te tombe dessus sans prévenir.

IN avec une liste qui contient un NULL peut donner des résultats déroutants, surtout combiné avec NOT IN :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, age FROM users WHERE age NOT IN (25, NULL);

On pourrait croire que cette requête renvoie tout le monde dont l'âge est différent de 25. Eh bien non, on n'obtient rien. SQLite développe NOT IN (25, NULL) en quelque chose comme age <> 25 AND age <> NULL, or age <> NULL vaut toujours NULL — du coup, la condition complète n'est jamais vraie. La solution : exclure les valeurs NULL de la liste (ou de la colonne) avant la comparaison.

DISTINCT, en revanche, considère que deux NULL sont égaux entre eux lorsqu'il s'agit de dédupliquer :

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT DISTINCT email FROM users;

Vous obtenez... pardon, vous obtenez trois lignes : l'email d'Ada, celui de Cleo, et un seul NULL (qui regroupe ceux de Boris et Dan). Même histoire avec GROUP BY et UNION : ces opérations considèrent les valeurs nulles comme un groupe unique, soit l'inverse exact du comportement de =. SQL manque parfois de cohérence sur ce point ; mieux vaut savoir de quel côté se range chaque opérateur.

Mémo express

• Pour tester une valeur manquante, utilisez IS NULL / IS NOT NULL. Jamais = NULL.
• Toute opération arithmétique, concaténation ou comparaison impliquant NULL renvoie NULL.
• COALESCE(a, b, c, ...) remplace les nulls par une valeur de repli. IFNULL(a, b) en est la version courte à deux arguments.
• La chaîne vide '' n'est pas équivalente à NULL. Choisissez l'une ou l'autre pour représenter « valeur absente » dans chaque colonne, et tenez-vous-y.
• NOT IN (..., NULL) est presque toujours un bug. Retirez les nulls de la liste avant.

La suite : trier les résultats

Une fois que vous savez filtrer correctement vos lignes — y compris celles à NULL —, l'étape suivante consiste à les organiser dans un ordre utile. ORDER BY fait l'objet de la page suivante, et il a, lui aussi, son petit avis sur la place que prennent les nulls dans un tri.