MySQL/NULL

Requêtes

Opérateurs

Requêtes

Opérateurs

Description

De nombreux langages de programmation ont deux valeurs logiques : True et False. SQL en possède une troisième pour les valeurs inconnues : NULL.

NULL étant une absence de valeur, il peut être assigné à des colonnes TEXT, INTEGER ou autres. Toutefois une colonne déclarée NOT NULL ne pourra pas en contenir.

 INSERT into Singer
        (F_Name, L_Name, Birth_place, Language) 
        values 
        ("", "Homer", NULL, "Greek"),
        ("", "Sting", NULL, "English"),
        ("Jonny", "Five", NULL, "Binary");

NULL ne doit pas être entouré d'apostrophes ou de guillemets, ou bien il désignera une chaine de caractères contenant son nom.

NULL n'apparait pas dans les colonnes Varchar sous Windows XP mais sous Fedora oui.

L'exemple ci-dessous peut sélectionner des chanteurs avec prénom de taille zéro (""), par exemple pour Sting et Homer. Il vérifie si la date de naissance est nulle :

 SELECT * from Singer WHERE Birth_place IS NULL;
 
 SELECT * from Singer WHERE Birth_place IS NOT NULL;
 
 SELECT * from Singer WHERE isNull(Birth_place)

Les enregistrements X à NULL ne sont pas renvoyés par un WHERE X != 'Y'

COUNT ne tient pas compte des NULL :

 select count(Birth_place) from Singer;
 0

Par ailleurs, SUM(NULL) renvoie NULL.

Les opérations normales (comparaisons, expressions...) renvoient NULL si au moins un des éléments comparés est NULL :

 SELECT (NULL=NULL) OR (NULL<>NULL) OR (NOT NULL) OR (1<NULL) OR (1>NULL) OR (1 + NULL) OR (1 LIKE NULL)

Deux valeurs inconnues ne sont donc pas égales (NULL=NULL renvoie NULL).

Gérer NULL

La fonction COALESCE peut simplifier le travail avec NULL.

Par exemple, pour éviter de montrer les valeurs nulles en les traitant comme des zéros :

 SELECT COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

Dans un champ date, les traiter comme celle actuelle :

 ORDER BY (COALESCE(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))

 EXP(SUM(LOG(COALESCE(''*the field you want to multiply*'',1)))

La fonction coalesce() prévient des problèmes de calcul logarithmique d'une valeur nulle, et peut être optionnelle selon les circonstances.

 SELECT t4.gene_name, COALESCE(g2d.score,0), 
 COALESCE(dgp.score,0), COALESCE(pocus.score,0) 
 FROM t4 
 LEFT JOIN g2d ON t4.gene_name=g2d.gene_name 
 LEFT JOIN dgp ON t4.gene_name=dgp.gene_name 
 LEFT JOIN pocus ON t4.gene_name=pocus.gene_name;

IFNULL() dans un SELECT fait de NULL n'importe quelle valeur désirée.

 IFNULL(expr1,expr2)

Si expr1 n'est pas nulle, IFNULL() renvoie expr1, sinon expr2.

IFNULL() renvoie une chaine ou un nombre, selon le contexte :

 mysql> SELECT IFNULL(1,0);
 -> 1
 mysql> SELECT IFNULL(NULL,10);
 -> 10
 mysql> SELECT IFNULL(1/0,10);
 -> 10
 mysql> SELECT IFNULL(1/0,'yes');
 -> 'yes'

Attention aux résultats peu prévisibles, par exemple la requête suivante efface toutes les entrées :

 DELETE FROM ma_table1 WHERE field > NULL   -- fonctionne aussi avec une fonction renvoyant NULL

Pour obtenir les NULL en dernier lors d'un ORDER BY :

 SELECT * FROM ma_table1 ORDER BY ISNULL(field), field [ ASC | DESC ]

Enfin, pour déterminer les champs d'une table qui ne peuvent pas être nuls :

SELECT *
FROM  `information_schema`.`COLUMNS`
WHERE IS_NULLABLE = 'NO' AND TABLE_NAME = 'ma_table1'