Semântica de NULL

  • Artigo

Aplica-se a: marca de seleção positiva SQL do Databricks marca de seleção positiva Azure Databricks Runtime

Uma tabela consiste em um conjunto de linhas e cada linha contém um conjunto de colunas. Uma coluna é associada a um tipo de dados e representa um atributo específico de uma entidade (por exemplo, age é uma coluna de uma entidade chamada person). Às vezes, o valor de uma coluna específica a uma linha não é conhecido no momento em que a linha passa a existir. Em SQL, esses valores são representados como NULL. Esta seção detalha a semântica de manipulação dos valores NULL em vários operadores, expressões e outros constructos SQL.

O exemplo a seguir ilustra o layout do esquema e os dados de uma tabela chamada person. Os dados contêm valores NULL na coluna age e essa tabela é usada em vários exemplos nas seções abaixo.

 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 comparação

O Azure Databricks dá suporte a operadores de comparação padrão, como >, >=, =, < e <=. O resultado desses operadores é desconhecido ou NULL quando um dos operandos ou ambos são desconhecidos ou NULL. Para comparar os valores NULL quanto à igualdade, o Azure Databricks fornece um operador de “igual diferente de nulo” (<=>), que retorna False quando um dos operandos é NULL e retorna True quando ambos os operandos são NULL. A tabela a seguir ilustra o comportamento dos operadores de comparação quando um ou ambos os operandos são NULL:

Operando à Esquerda Operando à Direita > >= = < <= <=>
NULO Qualquer valor NULO NULO NULO NULO NULO Falso
Qualquer valor NULO NULO NULO NULO NULO NULO Falso
NULO NULO NULO NULO NULO NULO NULO True

Exemplos

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

O Azure Databricks dá suporte aos operadores lógicos padrão, como AND, OR e NOT. Esses operadores recebem expressões Boolean como argumentos e retornam um valor Boolean.

As tabelas a seguir ilustram o comportamento de operadores lógicos quando um ou ambos os operandos são NULL.

Operando à Esquerda Operando à Direita OU AND
True NULO True NULO
Falso NULO NULO Falso
NULO True True NULO
NULO Falso NULO Falso
NULO NULO NULO NULO
operando NOT
NULO NULO

Exemplos

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

Expressões

Os operadores de comparação e os operadores lógicos são tratados como expressões no Azure Databricks. O Azure Databricks também dá suporte a outras formas de expressões, que podem ser amplamente classificadas como:

  • Expressões intolerantes a nulos
  • Expressões que podem processar os operandos de valor NULL
    • O resultado dessas expressões depende da própria expressão.

Expressões intolerantes a nulos

As expressões intolerantes a nulos retornam NULL quando um ou mais argumentos de expressão são NULL e a maioria das expressões se enquadra nessa categoria.

Exemplos

> 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

Expressões que podem processar operandos de valor nulo

Essa classe de expressões foi projetada para lidar com valores NULL. O resultado das expressões depende da própria expressão. Por exemplo, a expressão de função isnull retorna um true na entrada nula e false na entrada não nula, enquanto a função coalesce retorna o primeiro valor não NULL em sua lista de operadores. No entanto, coalesce retorna NULL quando todos os seus operandos são NULL. A seguir, uma lista incompleta de expressões dessa categoria.

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

Exemplos

> 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

Expressões de agregação internas

As funções de agregação calculam um único resultado processando um conjunto de linhas de entrada. Abaixo estão as regras de como os valores NULL são tratados pelas funções de agregação.

  • Os valores NULL são ignorados do processamento por todas as funções de agregação.
    • A exceção a essa regra é somente a função COUNT(*).
  • Algumas funções de agregação retornam NULL quando todos os valores de entrada são NULL ou o conjunto de dados de entrada for vazio. A lista dessas funções é:
    • MAX
    • MIN
    • SUM
    • AVG
    • EVERY
    • ANY
    • SOME

Exemplos

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

Expressões de condição nas cláusulas WHERE, HAVING e JOIN

Os operadores WHERE e HAVING filtram linhas com base na condição especificada pelo usuário. Um operador JOIN é usado para combinar linhas de duas tabelas com base em uma condição de junção. Para todos os três operadores, uma expressão de condição é uma expressão booliana e pode retornar True, False ou Unknown (NULL). Eles serão "satisfeitos" se o resultado da condição for True.

Exemplos

-- 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 agregação (GROUP BY, DISTINCT)

Conforme discutido em Operadores de comparação, dois valores NULL não são iguais. No entanto, para fins de agrupamento e processamento distinto, os dois ou mais valores com NULL data são agrupados juntos no mesmo bucket. Esse comportamento é compatível com o padrão SQL e com outros sistemas de gerenciamento de banco de dados corporativo.

Exemplos

-- `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 classificação (cláusula ORDER BY)

O Azure Databricks dá suporte à especificação de ordenação nula na cláusula ORDER BY. O Azure Databricks processa a cláusula ORDER BY colocando todos os valores NULL primeiro ou por último, dependendo da especificação de ordenação nula. Por padrão, todos os valores NULL são colocados primeiro.

Exemplos

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

Os valores NULL são comparados de forma “igual diferente de nulo” no contexto de operações de conjunto. Isso significa que, ao comparar linhas, dois valores NULL são considerados iguais, diferentemente do operador EqualTo(=) regular.

Exemplos

> 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 e NOT EXISTS

No Azure Databricks, as expressões EXISTS e NOT EXISTS são permitidas dentro de uma cláusula WHERE. Essas são expressões boolianas que retornam TRUE ou FALSE. Em outras palavras, EXISTS é uma condição de associação e retorna TRUE quando a subconsulta a qual se refere retorna uma ou mais linhas. Da mesma forma, NOT EXISTS é uma condição de não associação e retorna TRUE quando nenhuma linha ou zero linhas é retornada da subconsulta.

Essas duas expressões não são afetadas pela presença de NULL no resultado da subconsulta. Elas são normalmente mais rápidas porque podem ser convertidas em SEMINJOINs e anti-SEMIJOINs sem disposições especiais para o conhecimento de nulos.

Exemplos

-- 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 e NOT IN

No Azure Databricks, as expressões IN e NOT IN são permitidas dentro de uma cláusula WHERE de uma consulta. Ao contrário da expressão EXISTS, a expressão IN pode retornar um valor TRUE, FALSE ou UNKNOWN (NULL). Conceitualmente, uma expressão IN é semanticamente equivalente a um conjunto de condições de igualdade separadas por um operador de disjunção (OR). Por exemplo, c1 IN (1, 2, 3) é semanticamente equivalente a (C1 = 1 OR c1 = 2 OR c1 = 3).

No que diz respeito à manipulação de valores NULL, a semântica pode ser deduzida do tratamento do valor NULL em operadores de comparação (=) e operadores lógicos (OR). Para resumir, abaixo estão as regras para calcular o resultado de uma expressão IN.

  • TRUE é retornado quando o valor não NULL em questão é encontrado na lista
  • FALSE é retornado quando o valor não NULL não é encontrado na lista e a lista não contém valores NULL
  • UNKNOWN é retornado quando o valor é NULL, ou o valor não NULL não é encontrado na lista e a lista contém ao menos um valor NULL

NOT IN sempre retorna UNKNOWN quando a lista contém NULL, independentemente do valor de entrada. Isso ocorre porque IN retorna UNKNOWN se o valor não está na lista que contém NULL e porque NOT UNKNOWN é novamente UNKNOWN.

Exemplos

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