Analyser Null en SQL

NULL dans le monde de l'informatique et de la programmation représente l'inconnu et l'incertitude. 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 je me sens bien. il.

Tony Hoare a inventé null Quote en 1965, et l'a considéré comme une "erreur d'un milliard de dollars" qu'il a commise. Même aujourd'hui, 50 ans plus tard, dans SQL, les valeurs Null sont également responsables de nombreuses choses. erreurs courantes.

Jetons un coup d'œil à certains des cas les plus choquants.

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

Les deux requêtes suivantes, quel que soit le nombre d'enregistrements 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 de comparer null avec d'autres valeurs en utilisant les opérateurs conditionnels normaux. 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;.

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

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

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

select * from users
 
where deleted_at is null;
 
– result: 所有被标记为删除的 users

select * from users
 
where has_address is distinct from has_photo
 
– result: 地址(address)或照片(photo)两者只有其一的用户

Not in et 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 la requête suivante :

Mais à ce moment, si l'user_id d'une ligne de la table packages est nul, un problème survient : les résultats renvoyés sont vides ! Pour comprendre pourquoi cette chose étrange se produit, nous devons comprendre ce que fait le compilateur SQL. Voici un exemple plus simple :

select * from users 
 
where id not in (select user_id from packages)

Cette instruction SQL sera convertie comme suit :

select * from users 
 
where id not in (1, 2, null)

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

select * from users 
 
where id != 1 and id != 2 and id != null

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

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

select * from users 
 
where id in (select user_id from packages)

Ce SQL est converti en :

select * from users
 
where id in (1, 2, null)

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

select * from users 
 
where id = 1 or id = 2 or id = null

Null et tri

Lors du tri, la valeur nulle est considérée comme la plus grande. Cela vous donnera mal à la tête lors du tri par ordre décroissant (décroissant), car. null La valeur est classée 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

Il existe deux façons de résoudre ce type de problème ! Le plus simple est d'utiliser coalesce pour éliminer l'impact de null :

select name, points
 
from users
 
order by 2 desc;
 
– points 为 null 的记录排在所有记录之前!

Il existe également un moyen qui nécessite la prise en charge de la base de données et spécifie s'il faut mettre la valeur null en premier ou en dernier lors du tri :

– 在输出时将 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;

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

select name, coalesce(points, 0)
 
from users
 
order by 2 desc nulls last;

Diviser par 0

Diviser par 0 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.

La façon dont fonctionne l'instruction ase est en fait moche, et le dénominateur est réutilisé. Ce n'est pas grave si c'est une situation simple, mais si le dénominateur est une

select case when num_users = 0 then 0 
 
else total_sales/num_users end;

expression

très complexe, alors la tragédie viendra : c'est difficile à lire, difficile à maintenir et à modifier, et il y aura beaucoup de bugs si vous ne faites pas attention. À ce stade, nous pouvons examiner les avantages de null. Utilisez nullif pour que le dénominateur devienne nul lorsqu'il est 0. Cela ne signalera plus d'erreur. Lorsque num_users = 0, le le résultat renvoyé devient nul.

Si Si vous ne voulez pas de valeur nulle, 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 total_sales/nullif(num_users, 0);
 
nullif 是将其他值转为 null, 而Oracle的 nvl 是将 null 转换为其他值。

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 inefficace (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!