Sémantique NULL

  • Article

S’applique à : case marquée oui Databricks SQL case marquée oui Databricks Runtime

Une table se compose d’un ensemble de lignes et chaque ligne contient un ensemble de colonnes. Une colonne est associée à un type de données et représente un attribut spécifique d’une entité (par exemple, age est une colonne d’une entité appelée person). Parfois, la valeur d’une colonne spécifique à une ligne n’est pas connue au moment de la création de la ligne. En SQL, de telles valeurs sont représentées par NULL. Cette section détaille la sémantique de la gestion des valeurs NULL dans divers opérateurs, expressions et autres constructions SQL.

Ce qui suit illustre la disposition du schéma et les données d’une table nommée person. Les données contiennent des valeurs NULL dans la colonne age, et cette table est utilisée dans divers exemples dans les sections ci-dessous.

 Id  Name   Age
 --- -------- ----
 100 Joe      30
 200 Marry    NULL
 300 Mike     18
 400 Fred     50
 500 Albert   NULL
 600 Michelle 30
 700 Dan      50

Opérateurs de comparaison

Azure Databricks prend en charge les opérateurs de comparaison standard tels que >, >=, =, < et <=. Le résultat de ces opérateurs est inconnu ou NULL lorsque l’un des opérandes ou les deux opérandes sont inconnus ou NULL. Afin de comparer les valeurs NULL pour l’égalité, Azure Databricks SQL fournit un opérateur Null-safe Equal (<=>), qui retourne False lorsque l’un des opérandes est NULL et True lorsque les deux opérandes sont NULL. Le tableau suivant illustre le comportement des opérateurs de comparaison lorsqu’un ou les deux opérandes sont NULL :

Opérande de gauche Opérande de droite > >= = < <= <=>
NULL Valeur quelconque NULL NULL NULL NULL NULL False
Valeur quelconque NULL NULL NULL NULL NULL NULL False
NULL NULL NULL NULL NULL NULL NULL True

Exemples

-- Normal comparison operators return `NULL` when one of the operand is `NULL`.
> SELECT 5 > null AS expression_output;
 expression_output
 -----------------
              null

-- Normal comparison operators return `NULL` when both the operands are `NULL`.
> SELECT null = null AS expression_output;
 expression_output
 -----------------
              null

-- Null-safe equal operator return `False` when one of the operand is `NULL`
> SELECT 5 <=> null AS expression_output;
 expression_output
 -----------------
             false

-- Null-safe equal operator return `True` when one of the operand is `NULL`
> SELECT NULL <=> NULL;
 expression_output
 -----------------
              true
 -----------------

Opérateurs logiques

Azure Databricks prend en charge les opérateurs logiques standard tels que AND, OR et NOT. Ces opérateurs acceptent des expressions Boolean comme arguments et renvoient une valeur Boolean.

Les tableaux suivants illustrent le comportement des opérateurs logiques lorsqu’un ou les deux opérandes sont NULL.

Opérande de gauche Opérande de droite OR AND
True NULL True NULL
False NULL NULL False
NULL True True NULL
NULL False NULL False
NULL NULL NULL NULL
opérande NOT
NULL NULL

Exemples

-- Normal comparison operators return `NULL` when one of the operands is `NULL`.
> SELECT (true OR null) AS expression_output;
 expression_output
 -----------------
              true

-- Normal comparison operators return `NULL` when both the operands are `NULL`.
> SELECT (null OR false) AS expression_output
 expression_output
 -----------------
              null

-- Null-safe equal operator returns `False` when one of the operands is `NULL`
> SELECT NOT(null) AS expression_output;
 expression_output
 -----------------
              null

Expressions

Les opérateurs de comparaison et les opérateurs logiques sont traités comme des expressions dans Azure Databricks. Azure Databricks prend également en charge d’autres formes d’expressions, qui peuvent être classifiées comme suit :

  • Expressions non compatibles avec les valeurs Null
  • Expressions capables de traiter des opérandes de valeur NULL
    • Le résultat de ces expressions dépend de l’expression elle-même.

Expressions non compatibles avec les valeurs Null

Les expressions non compatibles avec les valeurs Null renvoient NULL lorsqu'un ou plusieurs arguments de l'expression sont NULL, et la plupart des expressions entrent dans cette catégorie.

Exemples

> SELECT concat('John', null) AS expression_output;
 expression_output
 -----------------
              null

> SELECT positive(null) AS expression_output;
 expression_output
 -----------------
              null

> SELECT to_date(null) AS expression_output;
 expression_output
 -----------------
              null

Expressions capables de traiter des opérandes de valeur Null

Cette classe d’expressions est conçue pour traiter les valeurs NULL. Le résultat des expressions dépend de l’expression elle-même. Par exemple, l'expression de fonction isnull renvoie true en cas d'entrée Null et false en cas d'entrée non Null, tandis que la fonction coalesce renvoie la première valeur non NULL de sa liste d'opérandes. Mais coalesce renvoie NULL lorsque tous ses opérandes sont NULL. Voici une liste incomplète d’expressions de cette catégorie.

  • COALESCE
  • NULLIF
  • IFNULL
  • NVL
  • NVL2
  • ISNAN
  • NANVL
  • ISNULL
  • ISNOTNULL
  • ATLEASTNNONNULLS
  • IN

Exemples

> SELECT isnull(null) AS expression_output;
 expression_output
 -----------------
              true

-- Returns the first occurrence of non `NULL` value.
> SELECT coalesce(null, null, 3, null) AS expression_output;
 expression_output
 -----------------
                 3

-- Returns `NULL` as all its operands are `NULL`.
> SELECT coalesce(null, null, null, null) AS expression_output;
 expression_output
 -----------------
              null

> SELECT isnan(null) AS expression_output;
 expression_output
 -----------------
             false

Expressions d’agrégation intégrées

Les fonctions d’agrégation calculent un résultat unique en traitant un ensemble de lignes d’entrée. Vous trouverez ci-dessous les règles de traitement des valeurs NULL par les fonctions d’agrégation.

  • Toutes les fonctions d’agrégation ignorent les valeurs NULL lors du traitement.
    • La seule exception à cette règle est la fonction COUNT(*).
  • Certaines fonctions d’agrégation renvoient NULL lorsque toutes les valeurs d’entrée sont NULL ou que l’ensemble de données d’entrée est vide. La liste de ces fonctions est la suivante :
    • MAX
    • MIN
    • SUM
    • AVG
    • EVERY
    • ANY
    • SOME

Exemples

-- `count(*)` does not skip `NULL` values.
> SELECT count(*) FROM person;
 count(1)
 --------
        7

-- `NULL` values in column `age` are skipped from processing.
> SELECT count(age) FROM person;
 count(age)
 ----------
          5

-- `count(*)` on an empty input set returns 0. This is unlike the other
-- aggregate functions, such as `max`, which return `NULL`.
> SELECT count(*) FROM person where 1 = 0;
 count(1)
 --------
        0

-- `NULL` values are excluded from computation of maximum value.
> SELECT max(age) FROM person;
 max(age)
 --------
       50

-- `max` returns `NULL` on an empty input set.
> SELECT max(age) FROM person where 1 = 0;
 max(age)
 --------
     null

Expression de condition dans les clauses WHERE, HAVING et JOIN

Les opérateurs WHERE et HAVING filtrent les lignes en fonction de la condition spécifiée par l’utilisateur. Un opérateur JOIN permet de combiner les lignes de deux tables sur la base d’une condition de jointure. Pour les trois opérateurs, une expression de condition est une expression booléenne et peut renvoyer True, False ou Unknown (NULL). Ils sont « satisfaits » si le résultat de la condition est True.

Exemples

-- Persons whose age is unknown (`NULL`) are filtered out from the result set.
> SELECT * FROM person WHERE age > 0;
     name age
 -------- ---
 Michelle  30
     Fred  50
     Mike  18
      Dan  50
      Joe  30

-- `IS NULL` expression is used in disjunction to select the persons
-- with unknown (`NULL`) records.
> SELECT * FROM person WHERE age > 0 OR age IS NULL;
     name  age
 -------- ----
   Albert null
 Michelle   30
     Fred   50
     Mike   18
      Dan   50
    Marry null
      Joe   30

-- Person with unknown(`NULL`) ages are skipped from processing.
> SELECT * FROM person GROUP BY age HAVING max(age) > 18;
 age count(1)
 --- --------
  50        2
  30        2

-- A self join case with a join condition `p1.age = p2.age AND p1.name = p2.name`.
-- The persons with unknown age (`NULL`) are filtered out by the join operator.
> SELECT * FROM person p1, person p2
    WHERE p1.age = p2.age
    AND p1.name = p2.name;
     name age     name age
 -------- --- -------- ---
 Michelle  30 Michelle  30
     Fred  50     Fred  50
     Mike  18     Mike  18
      Dan  50      Dan  50
      Joe  30      Joe  30

-- The age column from both legs of join are compared using null-safe equal which
-- is why the persons with unknown age (`NULL`) are qualified by the join.
> SELECT * FROM person p1, person p2
    WHERE p1.age <=> p2.age
    AND p1.name = p2.name;
     name  age     name  age
 -------- ---- -------- ----
   Albert null   Albert null
 Michelle   30 Michelle   30
     Fred   50     Fred   50
     Mike   18     Mike   18
      Dan   50      Dan   50
    Marry null    Marry null
      Joe   30      Joe   30

Opérateurs d’agrégation (GROUP BY, DISTINCT)

Comme indiqué dans Opérateurs de comparaison, deux valeurs NULL ne sont pas égales. Cependant, à des fins de regroupement et de traitement distinct, deux valeurs ou plus avec NULL data sont regroupées dans le même compartiment. Ce comportement est conforme à la norme SQL et à d’autres systèmes de gestion de base de données d’entreprise.

Exemples

-- `NULL` values are put in one bucket in `GROUP BY` processing.
> SELECT age, count(*) FROM person GROUP BY age;
  age count(1)
 ---- --------
 null        2
   50        2
   30        2
   18        1

-- All `NULL` ages are considered one distinct value in `DISTINCT` processing.
> SELECT DISTINCT age FROM person;
  age
 ----
 null
   50
   30
   18

Opérateur de tri (clause ORDER BY)

Azure Databricks prend en charge la spécification de classement de valeurs Null dans la clause ORDER BY. Azure Databricks traite la clause ORDER BY en plaçant toutes les valeurs NULL en premier ou en dernier selon la spécification de classement des valeurs Null. Par défaut, toutes les valeurs NULL sont placées en premier.

Exemples

-- `NULL` values are shown at first and other values
-- are sorted in ascending way.
> SELECT age, name FROM person ORDER BY age;
  age     name
 ---- --------
 null    Marry
 null   Albert
   18     Mike
   30 Michelle
   30      Joe
   50     Fred
   50      Dan

-- Column values other than `NULL` are sorted in ascending
-- way and `NULL` values are shown at the last.
> SELECT age, name FROM person ORDER BY age NULLS LAST;
  age     name
 ---- --------
   18     Mike
   30 Michelle
   30      Joe
   50      Dan
   50     Fred
 null    Marry
 null   Albert

-- Columns other than `NULL` values are sorted in descending
-- and `NULL` values are shown at the last.
> SELECT age, name FROM person ORDER BY age DESC NULLS LAST;
  age     name
 ---- --------
   50     Fred
   50      Dan
   30 Michelle
   30      Joe
   18     Mike
 null    Marry
 null   Albert

Opérateurs de jeu (UNION, INTERSECT, EXCEPT)

Les valeurs NULL sont comparées en mode « null-safe » pour garantir l’égalité dans le contexte des opérations ensemblistes. Cela signifie que, lors de la comparaison de lignes, deux valeurs NULL sont considérées comme égales, contrairement à l’opérateur EqualTo(=) standard.

Exemples

> CREATE VIEW unknown_age AS SELECT * FROM person WHERE age IS NULL;

-- Only common rows between two legs of `INTERSECT` are in the
-- result set. The comparison between columns of the row are done
-- in a null-safe manner.
> SELECT name, age FROM person
    INTERSECT
    SELECT name, age from unknown_age;
   name  age
 ------ ----
 Albert null
  Marry null

-- `NULL` values from two legs of the `EXCEPT` are not in output.
-- This basically shows that the comparison happens in a null-safe manner.
> SELECT age, name FROM person
    EXCEPT
    SELECT age FROM unknown_age;
 age     name
 --- --------
  30      Joe
  50     Fred
  30 Michelle
  18     Mike
  50      Dan

-- Performs `UNION` operation between two sets of data.
-- The comparison between columns of the row ae done in
-- null-safe manner.
> SELECT name, age FROM person
    UNION
    SELECT name, age FROM unknown_age;
     name  age
 -------- ----
   Albert null
      Joe   30
 Michelle   30
    Marry null
     Fred   50
     Mike   18
      Dan   50

Sous-requêtes EXISTS et NOT EXISTS

Dans Azure Databricks, les expressions EXISTS et NOT EXISTS sont autorisées dans une clause WHERE. Ce sont des expressions booléennes qui renvoient TRUE ou FALSE. En d’autres termes, EXISTS est une condition d’appartenance et renvoie TRUE lorsque la sous-requête à laquelle elle se réfère renvoie une ou plusieurs lignes. De même, NOT EXISTS est une condition de non-appartenance et renvoie TRUE lorsqu’aucune ligne ou des lignes zéro sont renvoyées par la sous-requête.

Ces deux expressions ne sont pas affectées par la présence de valeurs NULL dans le résultat de la sous-requête. Elles sont normalement plus rapides, car elles peuvent être converties en semi-jointures et anti-semi-jointures sans approvisionnement spécial pour la prise en compte des valeurs Null.

Exemples

-- Even if subquery produces rows with `NULL` values, the `EXISTS` expression
-- evaluates to `TRUE` as the subquery produces 1 row.
> SELECT * FROM person WHERE EXISTS (SELECT null);
     name  age
 -------- ----
   Albert null
 Michelle   30
     Fred   50
     Mike   18
      Dan   50
    Marry null
      Joe   30

-- `NOT EXISTS` expression returns `FALSE`. It returns `TRUE` only when
-- subquery produces no rows. In this case, it returns 1 row.
> SELECT * FROM person WHERE NOT EXISTS (SELECT null);
 name age
 ---- ---

-- `NOT EXISTS` expression returns `TRUE`.
> SELECT * FROM person WHERE NOT EXISTS (SELECT 1 WHERE 1 = 0);
     name  age
 -------- ----
   Albert null
 Michelle   30
     Fred   50
     Mike   18
      Dan   50
    Marry null
      Joe   30

Sous-requêtes IN et NOT IN

Dans Azure Databricks, les expressions IN et NOT IN sont autorisées dans la clause WHERE d’une requête. Contrairement à l’expression EXISTS, l’expression IN peut renvoyer une valeur TRUE, FALSE ou UNKNOWN (NULL). Conceptuellement, une expression IN est sémantiquement équivalente à un ensemble de conditions d’égalité séparées par un opérateur disjonctif (OR). Par exemple, c1 IN (1, 2, 3) équivaut sémantiquement à (C1 = 1 OR c1 = 2 OR c1 = 3).

En ce qui concerne le traitement des valeurs NULL, la sémantique peut être déduite du traitement des valeurs NULL dans les opérateurs de comparaison (=) et les opérateurs logiques (OR). En résumé, voici les règles pour calculer le résultat d’une expression IN.

  • TRUE est retourné lorsque la valeur non NULL en question figure dans la liste
  • FALSE est retourné lorsque la valeur non Null ne figure pas trouvée dans la liste et que la liste ne contient pas de valeurs NULL
  • UNKNOWN est retourné si la valeur est NULL, ou si la valeur non NULL ne figure pas dans la liste et que la liste contient au moins une valeur NULL

NOT IN renvoie toujours UNKNOWN lorsque la liste contient NULL, quelle que soit la valeur d’entrée. Cela vient du fait que IN renvoie UNKNOWN si la valeur ne figure pas dans la liste contenant NULL et que NOT UNKNOWN est à nouveau UNKNOWN.

Exemples

-- The subquery has only `NULL` value in its result set. Therefore,
-- the result of `IN` predicate is UNKNOWN.
> SELECT * FROM person WHERE age IN (SELECT null);
 name age
 ---- ---

-- The subquery has `NULL` value in the result set as well as a valid
-- value `50`. Rows with age = 50 are returned.
> SELECT * FROM person
    WHERE age IN (SELECT age FROM VALUES (50), (null) sub(age));
 name age
 ---- ---
 Fred  50
  Dan  50

-- Since subquery has `NULL` value in the result set, the `NOT IN`
-- predicate would return UNKNOWN. Hence, no rows are
-- qualified for this query.
> SELECT * FROM person
    WHERE age NOT IN (SELECT age FROM VALUES (50), (null) sub(age));
 name age
 ---- ---