Compréhension approfondie de Null dans SQL

NULL représente l'inconnu et l'incertitude dans le monde de l'informatique et de la programmation. Bien que la traduction chinoise soit « vide », ce vide (nul) n’est pas si vide (vide). Null représente un état inconnu, un état futur. Par exemple, je ne sais pas combien d'argent Xiao Ming a en poche, mais je ne peux pas être sûr que ce soit 0. À l'heure actuelle, Null est utilisé dans les ordinateurs pour représenter. inconnu et incertain.

Même si les personnes maîtrisant SQL n'auront aucun doute sur Null, il est toujours difficile de trouver un article qui le résume de manière exhaustive. J'ai vu une version anglaise et ça m'a fait du bien.

Tony Hoare a inventé la référence nulle en 1965 et la considérait comme son « erreur d'un milliard de dollars ». Même aujourd'hui, 50 ans plus tard, les valeurs nulles en SQL sont toujours responsables de nombreuses erreurs courantes.

Jetons un coup d’œil à certaines des situations les plus choquantes.

Null ne prend pas en charge le jugement de taille/égalité

Pour les deux requêtes suivantes, quel que soit le nombre d'enregistrements présents dans la table des utilisateurs, les enregistrements renvoyés comportent 0 ligne :

select * from users where deleted_at = null;

– result: 0 rows

select * from users where deleted_at != null;

– result: 0 rows

Comment est-ce possible ? Tout cela parce que null représente un type "inconnu". Autrement dit, cela n’a aucun sens d’utiliser des opérateurs conditionnels normaux pour comparer null avec d’autres valeurs. Null n'est pas égal à Null (compréhension approximative : une valeur inconnue ne peut pas être égale à une valeur inconnue, et la relation entre les deux est également inconnue, sinon les mathématiques et la logique seront gâchées).

– Remarque : le SQL suivant convient à MySQL. S'il s'agit d'Oracle, vous devez ajouter … from dual;

select null > 0;

– result: null

select null < 0;

– result: null

select null = 0;

– result: null

select null = null;

– result: null

select null != null;

– result: null

. La bonne façon de comparer une valeur à null est d'utiliser le mot-clé is et l'opérateur is not :

select * from users

where deleted_at is null;

– result: 所有被标记为删除的 users

select * from users

where deleted_at is not null;

– result: 所有被标记为删除的 users

Si vous souhaitez déterminer si les valeurs de deux colonnes sont différentes, vous pouvez utiliser est distinct de :

select * from users

where has_address is distinct from has_photo

– result: 地址(address)或照片(photo)两者只有其一的用户

pas avec Null

La sous-requête (sous-sélection) est un moyen très pratique de filtrer les données. Par exemple, si vous souhaitez interroger des utilisateurs qui n'ont aucun package, vous pouvez écrire une requête comme celle-ci :

select * from users 

where id not in (select user_id from packages)

Mais à ce moment-là, si le user_id d'une ligne de la table packages est nul, un problème survient : le résultat renvoyé est vide ! Pour comprendre pourquoi cette chose étrange se produit, nous devons comprendre ce que le compilateur SQL a fait. exemple :

select * from users 

where id not in (1, 2, null)

Cette instruction SQL sera convertie en :

select * from users 

where id != 1 and id != 2 and id != null

Nous savons que le résultat de id != null est une valeur inconnue, null Et le résultat de l'opération AND entre n'importe quelle valeur et null est nul, il n'est donc équivalent à aucune autre condition. La raison de ce résultat est que la logique. la valeur de null n'est pas vraie.

Si les conditions sont inversées, il n'y aura aucun problème avec les résultats de la requête. Maintenant, nous interrogeons les utilisateurs avec des packages.

select * from users 

where id in (select user_id from packages)

​De même, nous pouvons utiliser un exemple simple :

select * from users

where id in (1, 2, null)

Ce SQL est converti en :

select * from users 

where id = 1 or id = 2 or id = null

Étant donné que la clause Where est une série de conditions ou, peu importe si l'une d'elles aboutit à la valeur null. Les valeurs non vraies n'affectent pas les résultats de calcul des autres parties de la clause et équivalent à être ignorées.

​Null et tri

Lors du tri, la valeur nulle est considérée comme la plus grande. Cela vous causera beaucoup de maux de tête lors du tri par ordre décroissant (décroissant), car la valeur nulle vient en premier.

La requête suivante consiste à afficher les classements des utilisateurs en fonction des scores, mais elle classe les utilisateurs sans scores au sommet

select name, points

from users

order by 2 desc;

– points 为 null 的记录排在所有记录之前!

 ! Il existe deux manières d’envisager la résolution de ce type de problème. Le plus simple est d'utiliser coalesce pour éliminer l'effet de null :

– 在输出时将 null 转换为 0 :

select name, coalesce(points, 0)

from users

order by 2 desc;

– 输出时保留 null, 但排序时转换为 0 :

select name, points

from users

order by coalesce(points, 0) desc;

Il existe une autre méthode qui nécessite la prise en charge de la base de données, qui consiste à spécifier s'il faut mettre la valeur nulle en premier ou en dernier lors du tri :

select name, coalesce(points, 0)

from users

order by 2 desc nulls last;

Bien entendu, null peut également être utilisé pour éviter que des erreurs ne se produisent, telles que la gestion des erreurs d'opérations mathématiques lorsque le diviseur est nul.

Divisé par 0

Diviser par zéro est une erreur très pénible. SQL qui fonctionnait correctement hier s'est soudainement détérioré lorsqu'il a été divisé par 0. Une solution courante consiste à utiliser d’abord une instruction case pour déterminer si le dénominateur est 0, puis à effectuer l’opération de division.

select case when num_users = 0 then 0 

else total_sales/num_users end;

La manière de faire l'instruction ase est en fait moche et le dénominateur est réutilisé. Si c'est une situation simple, ça va. Mais si le dénominateur est une expression très complexe, alors une tragédie surviendra : c'est difficile à lire, difficile à maintenir et à modifier, et il y aura beaucoup de bugs si vous n'y faites pas attention.

À ce stade, nous pouvons examiner les avantages de null. Utilisez nullif pour que le dénominateur devienne nul lorsqu'il est égal à 0. De cette façon, une erreur ne sera plus signalée lorsque num_users = 0, le résultat renvoyé devient nul.

select total_sales/nullif(num_users, 0);

nullif 是将其他值转为 null, 而Oracle的 nvl 是将 null 转换为其他值。

Si vous ne voulez pas de null, mais que vous souhaitez convertir en 0 ou en d'autres nombres, vous pouvez utiliser la fonction de fusion basée sur le SQL précédent :

select coalesce(total_sales/nullif(num_users, 0), 0);

null 再转换回0

Conclusion

Tony Hoare regrette peut-être son erreur, mais au moins le problème de null peut être facilement résolu. Alors allez pratiquer votre nouveau coup ultime et restez à l'écart de la fosse invalide (annulante) creusée par null !

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!