Compartir a través de

Semántica de NULL

  • Artículo

Se aplica a:casilla marcada como Sí Databricks SQL casilla marcada como Sí Databricks Runtime

Una tabla consta de un conjunto de filas, y cada fila contiene un conjunto de columnas. Una columna está asociada a un tipo de datos y representa un atributo específico de una entidad (por ejemplo, age es una columna de una entidad denominada person). A veces, el valor de una columna específica de una fila no se conoce en el momento en que la fila empieza a existir. En SQL, estos valores se representan como NULL. En esta sección se detalla la semántica de los valores NULL que se usan en distintos operadores, expresiones y otras construcciones de SQL.

A continuación se muestra el diseño de esquema y los datos de una tabla de nombre person. Los datos contienen valores NULL en la columna age y esta tabla se usa en varios ejemplos de las secciones siguientes.

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

Operadores de comparación

Azure Databricks admite los operadores de comparación estándar como >, >=, =, < y <=. El resultado de estos operadores es desconocido o NULL si uno de los operandos o ambos son desconocidos o NULL. Para comparar la igualdad de los valores NULL, Azure Databricks proporciona un operador de igual para NULL (<=>), que devuelve False cuando uno de los operandos es NULL y True cuando ambos operandos son NULL. En la tabla siguiente se muestra el comportamiento de los operadores de comparación cuando uno o ambos operandos son NULL:

Operando izquierdo Operando derecho > >= = < <= <=>
NULL Cualquier valor NULL NULL NULL NULL NULL False
Cualquier valor NULL NULL NULL NULL NULL NULL False
NULL NULL NULL NULL NULL NULL NULL True

Ejemplos

-- 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
 -----------------

Operadores lógicos

Azure Databricks admite operadores lógicos estándar como AND, OR y NOT. Estos operadores toman expresiones Boolean como argumentos y devuelven un valor Boolean.

En las tablas siguientes se muestra el comportamiento de los operadores lógicos cuando uno o ambos operandos son NULL.

Operando izquierdo Operando derecho O BIEN y
True NULL True NULL
False NULL NULL False
NULL True True NULL
NULL False NULL False
NULL NULL NULL NULL
operando NOT
NULL NULL

Ejemplos

-- 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

Expresiones

Los operadores de comparación y los operadores lógicos se tratan como expresiones en Azure Databricks. Azure Databricks también admite otras formas de expresiones, que se pueden clasificar ampliamente como:

  • Expresiones intolerantes a NULL
  • Expresiones que pueden procesar operandos de valor NULL
    • El resultado de estas expresiones depende de la propia expresión.

Expresiones intolerantes a NULL

Las expresiones intolerantes a NULL devuelven NULL cuando uno o varios argumentos de la expresión son NULL; la mayoría de las expresiones se encuadran en esta categoría.

Ejemplos

> 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

Expresiones que pueden procesar operandos de valor NULL

Esta clase de expresiones está diseñada para controlar valores NULL. El resultado de las expresiones depende de la propia expresión. Como ejemplo, la expresión de función isnull devuelve true con una entrada NULL y false con una que no es NULL, donde como función, coalesce devuelve el primer valor distinto de NULL en su lista de operandos. Pero coalesce devuelve NULL cuando todos sus operandos son NULL. A continuación se muestra una lista incompleta de expresiones de esta categoría.

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

Ejemplos

> 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

Expresiones de agregado integradas

Las funciones de agregado calculan un único resultado mediante el procesamiento de un conjunto de filas de entrada. A continuación se muestran las reglas de cómo las funciones de agregado controlan los valores NULL.

  • Las funciones de agregado no procesan los valores NULL.
    • La única excepción a esta regla es la función COUNT(*).
  • Algunas funciones de agregado devuelven NULL cuando todos los valores de entrada son NULL o el conjunto de datos de entrada está vacío. La lista de estas funciones es:
    • MAX
    • MIN
    • SUM
    • AVG
    • EVERY
    • ANY
    • SOME

Ejemplos

-- `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

Expresiones de condición de las cláusulas WHERE, HAVING, y JOIN

Los operadores WHERE y HAVING filtran las filas en función de la condición especificada por el usuario. Un operador JOIN se usa para combinar filas de dos tablas en función de una condición de combinación. En los tres operadores, una expresión de condición es una expresión booleana que puede devolver True, False o Unknown (NULL). La condición se "satisface" si el resultado es True.

Ejemplos

-- 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

Operadores de agregado (GROUP BY, DISTINCT)

Como se ha visto en Operadores de comparación, dos valores NULL no son iguales. Pero para fines de agrupación y procesamiento, los dos o más valores con NULL data se agrupan en el mismo cubo. Este comportamiento es compatible con SQL estándar y con otros sistemas de administración de bases de datos empresariales.

Ejemplos

-- `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

Operador de ordenación (cláusula ORDER BY)

Azure Databricks admite la especificación de ordenación NULL en la cláusula ORDER BY. Azure Databricks procesa la cláusula ORDER BY mediante la colocación de todos los valores NULL al principio o al final en función de la especificación de ordenación NULL. De manera predeterminada, todos los valores NULL se colocan al principio.

Ejemplos

-- `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

Operadores de conjunto (UNION, INTERSECT, EXCEPT)

La igualdad de los valores NULL se compara de forma segura para NULL en el contexto de las operaciones de conjunto. Esto significa que, al comparar filas, dos valores NULL se consideran iguales a diferencia del operador normal EqualTo(=).

Ejemplos

> 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

Subconsultas EXISTS y NOT EXISTS

En Azure Databricks se permiten las expresiones EXISTS y NOT EXISTS dentro de una cláusula WHERE. Son expresiones booleanas que devuelven TRUE o FALSE. Es decir, EXISTS es una condición de pertenencia y devuelve TRUE cuando la subconsulta a la que hace referencia devuelve una o varias filas. De forma similar, NOT EXISTS es una condición que no es de pertenencia y devuelve TRUE cuando la subconsulta no devuelve ninguna o cero filas.

Estas dos expresiones no se ven afectadas por la presencia de NULL en el resultado de la subconsulta. Normalmente son más rápidas porque se pueden convertir en semi-combinaciones y anti-semi-combinaciones sin aprovisionamientos especiales para el reconocimiento de NULL.

Ejemplos

-- 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

Subconsultas IN y NOT IN

En Azure Databricks se permiten las expresiones IN y NOT IN dentro de una cláusula WHERE de una consulta. A diferencia de la expresión EXISTS, IN puede devolver un valor TRUE, FALSE o UNKNOWN (NULL). Conceptualmente, una expresión IN es semánticamente equivalente a un conjunto de condiciones de igualdad separadas por un operador disyuntivo (OR). Por ejemplo, c1 IN (1, 2, 3) es semánticamente equivalente a (C1 = 1 OR c1 = 2 OR c1 = 3).

En lo que respecta al control de valores NULL, la semántica se puede deducir del valor NULL que se usa en operadores de comparación (=) y operadores lógicos (OR). Para resumir, a continuación se muestran las reglas para calcular el resultado de una expresión IN.

  • Se devuelve TRUE cuando el valor distinto de NULL en cuestión se encuentra en la lista
  • Se devuelve FALSE cuando no se encuentra el valor distinto de NULL en la lista y esta no contiene valores NULL
  • Se devuelve UNKNOWN cuando el valor es NULL o el valor distinto de NULL no se encuentra en la lista y esta contiene al menos un valor NULL

NOT IN siempre devuelve UNKNOWN cuando la lista contiene NULL, independientemente del valor de entrada. Esto se debe a que IN devuelve UNKNOWN si el valor no está en la lista que contiene NULL, y porque NOT UNKNOWN es de nuevo UNKNOWN.

Ejemplos

-- 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
 ---- ---