close

Nul (SQL)

Aller à la navigation Aller à la recherche

Null (null) est un marqueur spécial utilisé dans le langage de requête structuré ( SQL ) pour indiquer qu'une valeur n'existe pas dans une base de données. Introduit par le créateur du modèle de base de données relationnelle EF Codd , sa fonction est de résoudre l'exigence selon laquelle les véritables systèmes de gestion de bases de données relationnelles (SGBD) peuvent représenter des informations « inconnues » ou « non applicables ». De même, Codd a également introduit l'utilisation de la lettre grecque minuscule oméga (ω) pour représenter le Null dans la théorie des bases de données. NULL est également un mot réservé dans le langage SQL pour identifier le marqueur Null spécial.

Null a été une source de controverse et une source de débat en raison de son association avec la logique ternaire (en anglais : Three-Valued Logic , en abrégé 3VL), ses restrictions sur son utilisation en SQL et la difficulté de sa manipulation en SQL. Bien que des fonctions et des prédicats spéciaux servent à traiter efficacement les valeurs nulles, la concurrence estime que la résolution de ces types de problèmes ajoute des complexités et des contradictions inutiles au sein du modèle relationnel des bases de données.

Historique

Null a été introduit par EF Codd comme méthode de représentation des données manquantes dans le modèle relationnel. Codd a ensuite renforcé son appel pour que tous les SGBDR incluent la fonction null pour indiquer les données manquantes dans un double article publié dans le magazine ComputerWorld. Codd a également introduit la logique ternaire, consistant en la véracité des valeurs True (true), False (false) et Unknown (Unknown), qui sont étroitement liées au concept de Null. La vraie valeur Unknown est générée lorsque Null est comparé à n'importe quelle donnée ou à un autre Null.

Codd a déclaré dans son livre "Le modèle relationnel pour la gestion de base de données publié en 1990, version 2" qu'une seule commande Null via la norme SQL était inadéquate et devrait être remplacée par deux drapeaux Null distincts pour indiquer la raison pour laquelle les données manquent. Ces deux types de marqueurs nuls sont communément appelés « Valeurs A » et « Valeurs I », représentant respectivement les « données manquantes mais applicables » et les « données manquantes mais non applicables ». La recommandation de Codd aurait dû nécessiter une extension du système logique de SQL pour s'adapter au système logique quaternaire. En raison de sa complexité supplémentaire, l'idée de plusieurs valeurs nulles n'a pas été largement reconnue.

Logique ternaire (Logique à trois valeurs -3VL-)

Parce qu'il n'appartient à aucun domaine de données, Null n'est pas correctement considéré comme une "valeur", mais c'est en fait un marqueur indiquant l'absence de valeur. Pour cette raison, les comparaisons Null ne renvoient jamais vrai ou faux, mais comme résultat logique ternaire, inconnu. Le résultat logique de l'expression suivante, qui compare la valeur 10 à Null, est Unknown : 

10  =  NULL  -- Résultats en Inconnu (Inconnu)

Cependant, certaines opérations Null peuvent renvoyer des valeurs si la valeur Null n'est pas pertinente pour le résultat de l'opération. C'est le cas de l'exemple suivant, en considérant que l'instruction OR est évaluée sous la forme d'un tour court : 

VRAI  OU  NULL  – Résultats en Vrai

Dans ce cas, le fait que la valeur à droite de OR soit inconnue n'est pas pertinent, car le résultat de l'opération doit être True quelle que soit la valeur à droite. SQL applique des résultats logiques ternaires, de sorte que les applications SQL doivent fournir des valeurs logiques ternaires spécialisées. Les règles régissant les valeurs logiques ternaires en SQL sont présentées dans les tableaux suivants (p et q représentent des états logiques) :

p ET q p
Réel Faux Inconnue
Quel Réel Réel Faux Inconnue
Faux Faux Faux Faux
Inconnue Inconnue Faux Inconnue
p
Réel Faux Inconnue
Quel Réel Réel Réel Réel
Faux Réel Faux Inconnue
Inconnue Réel Inconnue Inconnue
p Pas p
Réel Faux
Faux Réel
Inconnue Inconnue
p = q p
Réel Faux Inconnue
Quel Réel Réel Faux Inconnue
Faux Faux Réel Inconnue
Inconnue Inconnue Inconnue Inconnue

Les comparaisons opérationnelles de base dans SQL donnent lieu à Inconnu comparant n'importe quoi à Null, de sorte que la norme SQL stipule des calculs de prédicats assemblés spéciaux pour les deux fonctions spécifiques à Null.

Le test IS NULL et IS NOT NULL détermine si les données sont ou non Null.

Types de données

Null n'est pas conçu comme un entier, un caractère ou un autre type de données spécifique. Pour cette raison, il est parfois souhaitable de convertir explicitement Null en un type de données spécifique. Par exemple, si des fonctions surchargées sont prises en charge par le SGBD (système de gestion de base de données), SQL peut ne pas être en mesure de décider de la fonction correcte sans connaître les types de données de tous les paramètres, y compris ceux pour lesquels Null a agi.

Langage de manipulation de données

Les valeurs logiques ternaires SQL se trouvent dans le langage de manipulation de données (DML) dans les comparaisons de prédicats des assertions DML et des FAQ. L'instruction WHERE oblige l'instruction DML à agir uniquement sur les lignes pour lesquelles le calcul du prédicat est évalué à True. Les lignes pour lesquelles ces calculs prennent la valeur False ou Unknown ne sont pas dérivées d'instructions DML telles que INSERT, UPDATE ou DELETE, et sont supprimées des questions SELECT. Interpréter Inconnu et Faux comme le même résultat logique est une erreur courante rencontrée lorsqu'il s'agit de Nulls. L'exemple suivant le démontre : 

SELECT  *  
FROM  t  
WHERE  i  =  NULL ;

L'exemple ci-dessus renvoie toujours logiquement des zéros pour les lignes car la comparaison de la colonne i avec Null renvoie toujours une valeur Unknown, même pour les lignes où i est Null. Le résultat Unknown fait que l'instruction SELECT supprime chaque ligne (cependant, dans la pratique, les outils SQL récupèrent les lignes à l'aide de la comparaison Null).

Expressions CASE

Les expressions SQL CASE fonctionnent selon les mêmes règles que le DMS WHERE pour Null. Comme elle peut être évaluée comme une série de conditions de comparaison d'égalité, une simple expression CASE ne peut pas tester directement Null. Une vérification de l'existence de Null dans une expression CASE simple renvoie toujours Unknown, comme suit : 

SELECT  CASE  i  WHEN  NULL  THEN  'Is Null'  -- Ceci ne sera jamais retourné. 
              WHEN     0  THEN  'Is Zero'   -- Ceci sera retourné quand i = 0 
              WHEN     1  THEN  'Is one'    -- Ceci sera retourné quand i = 1 
              END 
FROM  t ;

Étant donné que l'expression i = NULL prend la valeur Unknown, quelle que soit la valeur de la colonne i (même si elle contient Null), le résultat ' IS NULL ' ne sera jamais renvoyé. Une expression CASE recherchée renvoie également la première valeur pour laquelle le résultat de la comparaison de prédicat est évalué à True, y compris les comparaisons de prédicat qui utilisent « IS NULL » AND « IS NOT NULL » . L'exemple suivant montre comment utiliser une expression CASE recherchée pour rechercher Null : 

SELECT  CASE  WHEN  i  IS  NULL  THEN  'Is Null'  -- Ceci sera retourné quand i est NULL 
            WHEN      i  =  0  THEN  'Zero'          -- Ceci retournera i = 0 
            WHEN      i  =  1  THEN  'One'           -- Ceci retournera je = 1 
            FIN 
DE  t ;

Dans l'expression CASE recherchée, ' Result Null ' est renvoyé pour toutes les lignes dans lesquelles i est Null.

Examen des limitations

L'endroit principal où la logique ternaire SQL croise le langage de définition de données SQL (DDL) est sous la forme de la vérification des contraintes. Une vérification ou une vérification de contrainte placée sur une colonne fonctionne selon un ensemble de règles légèrement différent de celui de la clause DML WHERE. Alors qu'une clause DML WHERE doit être évaluée à True pour une ligne, une vérification ne doit pas être évaluée à False. Cela signifie qu'une vérification réussira si le résultat de la vérification est Vrai ou Inconnu. L'exemple suivant montre une table avec une vérification qui interdira l'insertion de toute valeur entière dans la colonne i, mais autorisera l'insertion de Null puisque le résultat de la vérification sera toujours évalué à Inconnu pour Null. 

CREATE  TABLE  t  ( 
     i  INTEGER , 
     CONTRAINTE  ck_i  CHECK  (  i  <  0  AND  i  =  0  AND  i  >  0  )  );

Pour qu'une colonne rejette les valeurs NULL, la contrainte NOT NULL peut être appliquée, comme illustré dans l'exemple ci-dessous. La contrainte NOT NULL est sémantiquement équivalente à une vérification de contrainte avec un prédicat IS NOT NULL. 

CREATE  TABLE  t  (  i  INTEGER  NOT  NULL  );

Extensions de procédure

SQL/PSM (SQL Stored Persistent Modules) définit les extensions procédurales de SQL, telles que l'instruction IF. Cependant, les principales versions de SQL ont historiquement inclus leurs propres extensions procédurales. Les extensions procédurales pour le bouclage et la comparaison fonctionnent sous des règles de comparaison Null, similaires aux instructions et aux requêtes DML. L'extrait de code suivant, au format ISO SQL standard, illustre l'utilisation de Null 3VL dans une instruction IF. 

IF  i  =  NULL  THEN 
      SELECT  'Le résultat est vrai' 
ELSEIF  NOT ( i  =  NULL )  THEN 
      SELECT  'Le résultat est faux' 
ELSE 
      SELECT  'Le résultat est inconnu' ;

L'instruction IF exécute des actions uniquement pour les comparaisons évaluées à True. Pour les instructions évaluées comme False ou Unknown, l'instruction IF passe le contrôle à la clause ELSEIF, et enfin à la clause ELSE. Le résultat de ce code sera toujours le message "Le résultat est inconnu" car les comparaisons Null sont toujours évaluées à Inconnu.

Instructions JOIN

L' instruction JOIN dans SQL permet la combinaison d'enregistrements de deux tables ou plus dans la même base de données relationnelle. Il existe trois types de JOIN en SQL : interne, externe et croisé. Les jointures externes, y compris les instructions de table de gauche, de table de droite et de jointure complète, produisent automatiquement des valeurs nulles comme espaces réservés pour les valeurs manquantes dans les tables associées. Pour les jointures externes de table de gauche, par exemple, des valeurs nulles sont produites à la place des lignes de table manquantes apparaissant sur le côté droit de l'opérateur JOIN externe de table de gauche. L'exemple suivant utilise deux tables pour illustrer la production du marqueur Null dans la jointure externe gauche. La première table (Employé) contient les numéros d'identification et les noms des employés, tandis que la deuxième table (Numéro de téléphone) contient les numéros de téléphone liés à l'identification de l'employé, comme indiqué ci-dessous.

Employé
IDENTIFIANT Le nom nom
1 La gauche robert
deux Ambroise Albert
3 Alphonse Katia
4 Barbadillo William
Numéro de téléphone
IDENTIFIANT Le numéro
1 555-2323
3 555-9876

L'exemple SQL suivant effectue une jointure externe de table gauche entre les deux tables ci-dessus. 

SÉLECTIONNER  e . pièce d'identité ,  par ex . Nom de famille ,  e . Nom ,  pn . Numéro 
FROM  Employé  e 
LEFT  OUTER  JOIN  Numéro de  téléphone pn ON e . _  identifiant = pn . identifiant ;

La sortie générée dans cette série de questions montre comment SQL utilise Null comme espace réservé pour les valeurs manquantes sur le côté droit du tableau (numéros de téléphone), comme indiqué ci-dessous :

Résultat de l'enquête
IDENTIFIANT Le nom nom Le numéro
1 La gauche robert 555-2323
deux Ambroise Albert NUL
3 Alphonse Katia 555-9876
4 Barbadillo William NUL

Les jointures internes et les jointures croisées sont également disponibles dans le standard SQL et ne génèrent pas d'indicateurs Null pour les valeurs manquantes dans les tables associées.

Des précautions doivent être prises lors de l'utilisation de colonnes nullables dans les critères de jointure SQL. Étant donné qu'un Null n'est égal à aucun autre Null, les Nulls trouvés dans une colonne d'une table ne seront pas joints aux Nulls dans les colonnes associées d'une autre table qui utilise les opérateurs de comparaison d'égalité standard. La fonction SQL COALESCE ou les expressions CASE peuvent être utilisées pour "simuler" l'égalité Null dans les critères de jointure, et les prédicats "IS NULL" et "IS NOT NULL" peuvent également être utilisés dans les critères de jointure.

Le prédicat suivant teste l'égalité des valeurs A et B et traite Null comme égal. L'opérateur IFNULL est obligatoire puisque A = B renvoie une valeur Null si au moins une des valeurs (A ou B) est Null et NULL OR FALSE est lui-même Null. 

IFNULL (  A  =  B ,  FALSE  )  OU  (  A  EST  NULL  ET  B  EST  NULL  )

Concaténation mathématique et de caractères

Étant donné que Null n'est pas une valeur de données, mais un marqueur pour une valeur inconnue, l'utilisation d'opérateurs mathématiques sur Null donne une valeur inconnue, qui est représentée par Null. Dans l'exemple suivant, la multiplication de 10 par Null donne Null : 

10  *  NULL  -- Le résultat est NULL

Cela peut conduire à des résultats inattendus. Par exemple, lorsqu'une tentative est faite pour diviser Null par zéro, les plates-formes peuvent renvoyer Null au lieu de lancer une "exception de données - division par zéro" attendue. Bien que ce comportement ne soit pas défini par la norme SQL ISO, de nombreux produits SGBD commerciaux traitent cette opération de la même manière. Par exemple, les plates-formes Oracle, PostgreSQL, MySQL Server et Microsoft SQL Server renvoient Null pour l'opération : 

NULL  /  0

La série d'opérations chaînées, qui sont courantes en SQL, renvoie également Null lorsque l'un des opérandes est Null. L'exemple suivant illustre le résultat Null renvoyé en utilisant Null avec l'opérateur SQL sur une chaîne concaténée. 

'Omelette'  ||  NUL  ||  ' Pommes de terre '  - Le  résultat  est  NULL

Ce n'est pas vrai pour toutes les implémentations de base de données. Dans Oracle RDBMS, par exemple, NULL et une chaîne vide sont considérés comme la même chose et donc ' Tortilla ' le NULL 'Potato' donne 'Tortilla Potato'.

Fonctions ajoutées

SQL définit des fonctions d'agrégation pour simplifier les calculs agrégés des données du serveur. Presque toutes les fonctions d'agrégation effectuent une étape d'élimination Null, de sorte que les valeurs Null ne sont pas incluses dans le résultat final du calcul. Cependant, cette suppression implicite de Null peut influencer les résultats des fonctions d'agrégation. Le tableau illustré ci-dessous dérive différents résultats renvoyés à chaque colonne lorsque la fonction d'agrégation SQL AVG (moyenne) est appliquée :

Table
Yo j
150 150
200 200
350 350
NUL 0

La fonction d'agrégation AVG SQL renvoie 233 lorsqu'elle est appliquée à la colonne i, mais renvoie 175 lorsqu'elle est appliquée à la colonne j. L'étape de suppression de la fonction d'agrégation Null explique la différence de ces résultats. La seule fonction d'agrégation qui ne supprime pas implicitement le Null est la fonction COUNT.

Regroupement et classification

Étant donné que SQL:2003 définit tous les marqueurs Null comme inégaux les uns aux autres, ils nécessitaient une définition spéciale pour regrouper les Nulls afin d'effectuer certaines opérations. SQL définit "toutes deux valeurs égales l'une à l'autre, ou toute paire de Nulls", comme "non distinctes". Cette définition de not distinct permet à SQL de regrouper et de trier les Nulls lorsque la clause GROUP BY (et d'autres mots clés qui effectuent le regroupement) est utilisée.

D'autres opérations, clauses et mots-clés SQL utilisent "not distinct" dans leur traitement des valeurs Null. Il s'agit notamment des éléments suivants :

  • La clause de classification PARTITION BY et les fonctions de fenêtrage telles que ROW_NUMBER.
  • UNION, INTERSECT et l'opérateur EXCEPT, qui traitent les valeurs NULL de la même manière pour les déterminations de comparaison/suppression de lignes.
  • Le mot clé DISTINCT est utilisé dans les questions SELECT.
  • Contraintes uniques sur les colonnes dans lesquelles la fonction Null peut être trouvée, qui autorisent le stockage d'une seule valeur Null dans la colonne.

Le standard SQL ne définit pas explicitement la classe de tri par défaut pour les Nulls. Au lieu de cela, sur les systèmes conformes, les valeurs Null peuvent être triées avant ou après toutes les valeurs de données en utilisant respectivement les clauses NULLS FIRST ou NULLS LAST de la liste ORDER BY. Cependant, tous les fournisseurs de SGBD n'implémentent pas cette fonctionnalité. Les fournisseurs qui n'implémentent pas cette fonctionnalité peuvent spécifier un traitement différent pour la classification Null dans le SGBD.

Fonctions de gestion des valeurs nulles

SQL définit deux fonctions pour gérer explicitement Null : COALESCE et NULLIF. Ces deux fonctions sont des raccourcis pour les expressions CASE recherchées.

COALESCE

La fonction COALESCE accepte une liste de paramètres, renvoyant la première valeur non nulle de la liste : 

COALESCE ( valeur1 ,  valeur2 ,  valeur3 ,  ...)

COALESCEest défini comme l'expression SQL suivante CASE: 

CAS  QUAND  valeur1  N'EST  PAS  NULL  ALORS  valeur1 
     QUAND  valeur2  N'EST  PAS  NULL  ALORS  valeur2 
     QUAND  valeur3  N'EST  PAS  NULL  ALORS  valeur3 
     ... 
     FIN

Certains SGBD SQL implémentent des fonctions similaires spécifiques au fournisseur pour COALESCE. Certains systèmes implémentent une fonction ISNULL ou d'autres fonctions similaires fonctionnellement similaires à COALESCE.

NULLIF

La fonction NULLIF accepte deux paramètres. Si le premier paramètre est égal au second, NULLIF renvoie Null. Sinon, la valeur du premier paramètre est renvoyée. 

NULLIF ( valeur1 ,  valeur2 )

Par conséquent, NULLIF est un raccourci pour l'expression CASE suivante : 

CAS  QUAND  valeur1  =  valeur2  ALORS  NULL  SINON  valeur1  FIN

Voir aussi

Liens externes

Obtenu depuis « https://es.wikipedia.org/w/index.php?title=Null_(SQL)&oldid=141830544 »