Aller au menu - Aller au contenu

Icône Traitement des "NULL"s

Par Avatar Shepard
Mise à jour : 29/06/2009
1 063 visites depuis 7 jours, dont 45 sur ce chapitre classé 122/786
Voici un autre chapitre intéressant : le traitement des valeurs nulles, autrement dit le traitement d'une absence de valeurs !

Encore une fois, peu de développeurs se servent de cette fonctionnalité ! Pourtant les valeurs nulles sont pratiquement indispensables dans une base de données. L'exemple le plus flagrant est un profil dans un espace membre : la plupart des codeurs mettent une chaîne vide ( '' ) pour indiquer qu'il n'y a pas de valeur, alors qu'il serait plus simple de mettre un simple NULL, qui peut être traité beaucoup plus facilement par la suite dans les requêtes SELECT grâce aux fonctions que nous verrons dans ce chapitre. :)

Je vous laisse lire tout ça, bonne lecture !
Retour en haut
Sommaire du chapitre :
Icône du chapitre
Chapitre précédent Sommaire Chapitre suivant
Retour en haut

Spécificités des valeurs nulles

La valeur NULL n'est pas une valeur comme les autres. En général, on la considère comme une valeur à problèmes, mais en fait elle est très pratique à partir du moment où on sait l'utiliser.

Notamment, ceux qui utilisent les jointures se plaignent qu'on ne peut récupérer les lignes où la réfèrence est nulle, c'est simplement parce qu'ils utilisent LEFT JOIN au lieu de RIGHT JOIN, mais ça c'est l'objet d'un autre chapitre. ^^


Les valeurs NULL, comme je vous le disais, sont spéciales. Par exemple, on ne peut pas les comparer. Pour comprendre ce principe, créons une table exemple :

Code : SQL
1
2
CREATE TABLE ex_null ( id INT AUTO_INCREMENT, points INT DEFAULT NULL, PRIMARY KEY ( id ) );
INSERT INTO ex_null ( points ) VALUES ( 135 ), ( 3484 ), ( DEFAULT ), ( DEFAULT ), ( 0 ), ( DEFAULT );


Dans cette table, les NULL représentent ceux qui n'ont jamais gagné ni perdu de points. Les 0 représentent ceux qui ont déjà gagné des points, mais les ont reperdus.

Un autre façon de faire cette table aurait été d'ajouter une colonne "a_deja_joue", mais elle est totalement inutile dans le sens où un NULL nous permet d'avoir cette information plus facilement sans colonne supplémentaire.

Essayons de récupérer les lignes de ceux qui n'ont jamais joué:

Code : SQL
1
SELECT id, points FROM ex_null WHERE points = NULL;

Image utilisateur


Euh... o_O MySQL est devenu bigleu ?? :waw:


Non, c'est juste que les NULL ne peuvent pas être comparés à l'aide du signe "=". Et encore, MySQL est gentil : tout autre SGBDR aurait renvoyé une erreur (ou le devrait, tout comme MySQL).

Pour savoir si une valeur est nulle, il faut utiliser l'opérateur IS NULL :

Code : SQL
1
SELECT id, points FROM ex_null WHERE points IS NULL;

Image utilisateur


C'est déjà mieux, non ? :p :)

Attention également aux opérations comprenant des valeurs nulles : 1 + NULL = NULL en SQL !
A noter les opérations avec OR : x OR NULL = x SAUF SI x = NULL ou x = 0 !
Retour en haut

Les fonctions de traitement des valeurs nulles

COALESCE



Fonction très pratique : COALESCE prend une liste d'arguments aussi longue que l'on veut (pas trop quand même :p ), et renvoie la première valeur non nulle passée en argument :

Code : SQL
1
SELECT COALESCE(NULL, 0);

Renvoie 0.

Donc dans notre table d'essai, pour renvoyer 0 au lieu de NULL, on pourrait faire comme ça :

Code : SQL
1
SELECT id, COALESCE(points, 0) FROM ex_null;

Image utilisateur


Un exemple de la praticité de la fonction COALESCE :

Code : SQL
1
SELECT id, COALESCE(points, 'N''a jamais joue') AS points FROM ex_null;

Image utilisateur


Essayez donc d'obtenir un tel résultat si facilement avec une colonne "a_deja_joue" supplémentaire. ;) :)

IFNULL



IFNULL est une fonction assez... spéciale et plutôt compliquée à comprendre au début, mais rassurez-vous : on s'y fait tous. :p :)

IFNULL prend deux paramètres.

Si le premier paramètre est NULL, alors le deuxième paramètre est renvoyé.
Si le premier paramètre n'est pas NULL, alors c'est lui-même qui est renvoyé.

Compris ? :p

Relisez ça une paire de fois, et passez à la suite une fois que vous serez sûr d'avoir compris. :)

On va simuler une colonne a_deja_joue qui vaudra 0 si les points sont égaux à NULL, sinon 1.

Pour cela, on va commencer par utiliser IFNULL(points, 0)

Code : SQL
1
SELECT id, IFNULL(points, 0) AS a_deja_joue FROM ex_null;

Image utilisateur


Ainsi, si points est NULL, on aura 0, sinon on aura [points].

Pour le 0, c'est bon, mais nous, on veut 1, on ne veut pas points, car si points vaut 0 (comme l'enregistrement ayant pour id 5), notre système ne fonctionnera pas. On va donc diviser le résultat du IFNULL par COALESCE(points, 1). Ainsi on aura soit 1 (points <> 0 => points/points = 1), soit 0 (points = NULL => 0 / 1 = 0), soit NULL (points = 0 => 0 / 0 = NULL en SQL).

Code : SQL
1
SELECT id, IFNULL(points, 0) / COALESCE(points, 1) AS a_deja_joue FROM ex_null;

Image utilisateur


Ici, on peut améliorer un truc : comme vous le voyez, on obtient 1.0000 et 0.0000, pas super joli, pour obtenir 1 et 0 il suffit d'utiliser l'opérateur DIV vu au second chapitre. :)

Code : SQL
1
SELECT id, IFNULL(points, 0) DIV COALESCE(points, 1) AS a_deja_joue FROM ex_null;

Image utilisateur


Il reste un tout petit problème : les NULL (points = 0) doivent être transformés en 1. Pour cela, évidemment, rien de plus simple, il suffit d'utiliser, une fois de plus, la fonction COALESCE qui englobera cette fois toute la division :

Code : SQL
1
SELECT id, points, COALESCE(IFNULL(points, 0) DIV COALESCE(points, 1), 1) AS a_deja_joue FROM ex_null;

Image utilisateur


Assez sympa non ? Bon j'admets que ce n'était peut-être pas super super simple à comprendre, mais en y allant étape par étape, ça a du bien se passer, enfin j'espère. :)

En fait, il existe une méthode beaucoup plus simple pour obtenir cela, l'inconvénient, c'est qu'elle n'est valable qu'avec MySQL. :( Je vous déconseille vraiment de l'utiliser, surtout si vous faites des calculs à base du résultat, voici cette autre requête :

Code : SQL
1
SELECT id, points IS NULL AS a_deja_joue FROM ex_null;


Dans le prochain chapitre, nous verrons une méthode plus "standard" pour obtenir le même résultat, ne vous inquiétez pas : vous n'êtes pas condamné à utiliser IFNULL qui en fait n'est que très rarement utilisé et assez peu pratique... ^^
Retour en haut

Le système de classement en une seule requête !

Vous vous souvenez du système de classement vu dans le chapitre précédent ? (j'espère que oui sinon mon enseignement ne serait pas d'une grande utilité :D ). Voici les requêtes que je vous avais données :

Code : SQL
1
2
SET @place = 0;
SELECT @place := @place + 1 AS place, pseudo, points FROM joueurs ORDER BY points DESC;


Et bien grâce aux fonctions que nous venons de voir, nous pouvons maintenant tout rassembler sur une seule ligne !

En fait, ça ne sert à strictement rien et ça ralentit un peu la requête, mais c'est toujours intéressant de le savoir car peut-être que vous serez confrontés à des cas où vous serez obligés d'utiliser la technique suivante :

Code : SQL
1
SELECT @place := COALESCE(@place, @place := 0) + 1 AS place, pseudo, points FROM joueurs ORDER BY points DESC;

Image utilisateur


Pratique non ? On peut également vérifier facilement si une variable a déjà été déclarée grâce à la fonction IFNULL. Comme vous le constatez, les fonctions de traitement des valeurs NULL sont relativement pratiques couplées aux variables utilisateur. Elles permettent aussi de compacter les requêtes et d'éviter un CASE (on verra ça dans le prochain chapitre) ou un IF (prochain chapitre également :p ), bien que parfois, ces fonctions (particulièrement IFNULL) font tout sauf compacter la requête. :D :p
Retour en haut

Q.C.M.

Une requête de cette sorte est-elle permise ?
Code : SQL
1
SELECT [...] WHERE champ = NULL;
Que renvoie la fonction COALESCE ?
Que renvoie la requête suivante ?

Code : SQL
1
SELECT IFNULL(NULL, 'bonjour');

Statistiques de réponses au QCM

Retour en haut
Voilà, j'espère que ça vous a plu et que vous vous rendez compte de l'erreur que font les développeurs qui préfèrent travailler avec des chaînes vides plutôt qu'avec des valeurs nulles puis qui doivent se farcir des IF au lieu d'un simple COALESCE ou IFNULL. :D
Chapitre précédent Sommaire Chapitre suivant
Retour en haut

Partager

Retour en haut
11 commentaires pour "Traitement des "NULL"s"
Note moyenne : 3.87 / 4 (31 votes)
Pseudo Commentaire
Hors ligne Zimm i48 # Posté le 09/12/2006 à 13:33:55
A quand la retraite ?...
Avatar

Ville : Paris
Pays : France métropolitaine
Études : ENS Paris

 Super tout ça, lumineux ce chapitre :)
Dommage pour les quelques fautes d'orthographe / de frappe mais je vais pas toutes les lister là.
Moi non plus, je n'ai pas bien compris l'intérêt de IFNULL par rapport à COALESCE, une histoire de rapidité ?

EDIT: Ah d'accord, avec IFNULL, si la première expression est nulle, ça renverra la deuxième même si elle est nulle aussi.
Et avec COALESCE, ça renvoie quoi s'il n'y a que des expressions nulles ? NULL ? Dans ce ce cas on est revenu au point de départ :lol:
EDIT de l'EDIT: J'en reviens pas... J'ai fait le test et la seule différence entre les deux, c'est que si IFNULL renvoie NULL alors dans le tableau il est aligné à droite, quand avec COALESCE il est aligné à gauche :waw: C'est à s'arracher les cheveux !!!

Vraiment bon tuto.
Avec Mateo ça faisait plutôt peur la SQL... :-°
17/20

EDIT: Ah oui et aussi, les QCMs sont trop courts c'est bête.
Rendez-vous sur Test-Pedago.fr, le site d'un projet libre de logiciels pour s'entraîner sur les tables de multiplication et les conjugaisons.


Ubuntu

Wikipédia

Framasoft
Jamendo

Wikiversité
 
Hors ligne bismuth # Posté le 18/03/2009 à 09:20:50
Avatar

 Bonjour,

chapitre vraiment intéressant mais moi j'ai un pb avec :
Code : SQL
1
SELECT @place := COALESCE(@place, @place := 0) + 1 AS place, pseudo, points FROM joueurs ORDER BY points DESC;


toutes les places sont égale à 1 :euh:
Il y a un bug ou c'est moi qui ait raté quelque chose :-°
Hors ligne christophetd # Posté le 29/06/2009 à 12:50:54
Regardez-moi !
Avatar

Avis : Très bon
Flux RSS
close

Ville : Gap
Pays : France métropolitaine

 Encore un super chapitre.
Je ne peux que dire bravo.
Mes tutoriels :
Empêcher le téléchargement direct de fichiers multimédia
Utilisation d'un ORM : les bases de Doctrine
Un système de news sans base de données
Petit guide d'utilisation de Twitter
 
Hors ligne louf404 # Posté le 29/06/2009 à 15:48:49
Avatar

 Très bon tutos, à part qu'il faut relire plein de fois avant de comprendre certaine chose :-°
Sinon, comme je ne comprenais pas ça :
Code : SQL
1
SELECT id, points, COALESCE(IFNULL(points, 0) DIV COALESCE(points, 1), 1) AS a_deja_joue FROM ex_null;

(du fait que dans les exemple d'avant c'était une COALESCE(points, 0), et que du coup ça ne marchait pas).
Bref j'ai trouver une façon bien plus simple :
Code : SQL
1
SELECT id,points, IFNULL((points+1) DIV (points+1),0) AS a_deja_joue FROM ex_null;

Explication :
  • Si points > 0, ben ça affiche points+1/points+1 = 1
  • Si points = 0, ben ça affiche points+1/points+1 = 1/1 = 1
  • Si points = NULL, ben ça affiche NULL+1/NULL+1 = NULL/NULL = NULL

Et après ça transforme les NULL en 0 :)
Hors ligne valpokbest # Posté le 21/08/2009 à 22:36:34
Voltaire a dit un jour:
Avatar

Ville : Houlbec-cocherel
Pays : France métropolitaine

 Et pis on peut simplement utiliser que des IFNULL ou que des COALESCE, non?

EDIT: compris en lisant les com's du dessus.
*surpris d'avoir compris xD*
http://pokestyle.com | RPG ONLINE enfin disponible! Fans de pokémon? Venez
Ubuntu 9.04 The Jaunty Jackalope (la jackalope enjouée). Mais savez-vous ce qu'est une jackalope? Et bien comme le dit http://doc.ubuntu-fr.org/ , "la jackalope est un animal imaginaire entre la chèvre et l'antilope"! Les programmeurs ont encore du absorber une trop grosse dose de 0 et de 1...
 

Voir tous les commentaires