Семантика значений NULL

Статья
07/05/2024

Область применения: флажок Databricks SQL Databricks Runtime

Таблица состоит из набора строк, и каждая строка содержит набор столбцов. Столбец связан с типом данных и представляет собой конкретный атрибут сущности (например, age — это столбец сущности с именем person). Иногда значение столбца, относящегося к строке, неизвестно в момент создания строки. В SQL эти значения обозначаются как NULL. В этом разделе подробно описывается семантика обработки значений NULL в различных операторах, выражениях и других конструкциях SQL.

Ниже показан макет схемы и данные таблицы с именем person. Данные содержат значения NULL в столбце age, а сама таблица используется в разных примерах в следующих разделах.

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

Операторы сравнения

Azure Databricks поддерживает стандартные операторы сравнения, такие как >, >=и <==< . Результат этих операторов неизвестен или равен NULL, если один из операндов или оба операнда неизвестны или равен NULL. Для сравнения NULL значений равенства Azure Databricks предоставляет оператор равенства с значением NULL,<=> который возвращаетFalse, когда один из операндов и возвращаетсяTrue, когда оба операнда NULL являютсяNULL. В следующей таблице показано поведение операторов сравнения, когда один или оба операнда равны NULL:

Левый операнд	Правый операнд	`>`	`>=`	`=`	`<`	`<=`	`<=>`
NULL	Любое значение	NULL	NULL	NULL	NULL	NULL	False
Любое значение	NULL	NULL	NULL	NULL	NULL	NULL	False
NULL	NULL	NULL	NULL	NULL	NULL	NULL	Истина

Примеры

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

Логические операторы

Azure Databricks поддерживает стандартные логические операторы, такие как AND, OR и NOT. Эти операторы принимают выражения Boolean в качестве аргументов и возвращают значение Boolean.

В следующих таблицах показано поведение логических операторов, если один или оба операнда равны NULL.

Левый операнд	Правый операнд	ИЛИ	И
Истина	NULL	Истина	NULL
False	NULL	NULL	False
NULL	Истина	Истина	NULL
NULL	False	NULL	False
NULL	NULL	NULL	NULL

Операнд	Логическое НЕ
NULL	NULL

Примеры

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

Выражения

Операторы сравнения и логические операторы рассматриваются как выражения в Azure Databricks. Azure Databricks также поддерживает другие формы выражений, которые можно классифицировать как:

Выражения, не учитывающие нулевые значения
Выражения, которые могут обрабатывать NULL операнды значений
- Результат этих выражений зависит от самого выражения.

Выражения, не учитывающие нулевые значения

Выражения, не учитывающие значения NULL, возвращают NULL, если один или несколько аргументов выражения равны NULL, и большинство выражений попадают в эту категорию.

Примеры

> 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

Выражения, которые могут обрабатывать операнды со значением NULL

Этот класс выражений предназначен для обработки значений NULL. Результат этих выражений зависит от самого выражения. Например, выражение функции isnull возвращает значение true при вводе значения NULL и false при вводе значений, отличных от значения NULL, где функция coalesce возвращает первое значение, отличное от NULL, в списке операндов. Но coalesce возвращает NULL, когда все операнды равны NULL. Ниже приведен неполный список выражений этой категории.

COALESCE
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
В

Примеры

> 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

Встроенные статистические выражения

Агрегатные функции вычисляют один результат путем обработки набора входных строк. Ниже приведены правила обработки значений NULL агрегатными функциями.

Значения NULL не учитываются при обработке всеми агрегатными функциями.
- Единственное исключение из этого правила — функция COUNT(*).
Некоторые агрегатные функции возвращают NULL, если все входные значения равны NULL или набор входных данных пустой. Список этих функций:
- MAX
- MIN
- SUM
- AVG
- EVERY
- ANY
- SOME

Примеры

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

Выражения условий в предложениях `WHERE`, `HAVING` и `JOIN`

Операторы WHERE, HAVING фильтруют строки на основе заданного пользователем условия. Оператор JOIN используется для объединения строк из двух таблиц на основе условия соединения. Для всех трех операторов выражение условия является логическим выражением и может возвращать True, False или Unknown (NULL). Они считаются выполненными, если результат условия равен True.

Примеры

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

Статистические операторы (`GROUP BY`, `DISTINCT`)

Как описано в разделе Операторы сравнения, два значения NULL не равны. Но в целях группирования и отдельной обработки два или более значений NULL data группируются в один контейнер. Эта методика соответствует стандартам SQL и другим системам управления базами данных предприятия.

Примеры

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

Оператор Sort (предложение `ORDER BY`)

Azure Databricks поддерживает спецификацию упорядочения null в ORDER BY предложении. Azure Databricks обрабатывает ORDER BY предложение, помещая все NULL значения в первую очередь или в последнее время в зависимости от спецификации упорядочения null. По умолчанию все значения NULL помещаются в начало.

Примеры

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

Операторы множеств (`UNION`, `INTERSECT`, `EXCEPT`)

Значения NULL сравниваются с учетом значений NULL в контексте операций над множеством. Это означает, что при сравнении строк два значения NULL считаются равными, в отличие от обычного оператора EqualTo (=).

Примеры

> 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

Вложенные запросы `EXISTS` и `NOT EXISTS`

В Azure Databricks EXISTS и NOT EXISTS выражения разрешены в предложении WHERE . Это логические выражения, возвращающие TRUEили FALSE. Другими словами, EXISTS является условием членства и возвращает TRUE, когда вложенный запрос, на который он ссылается, возвращает одну или несколько строк. Точно так же NOT EXISTS является условием отсутствия членства и возвращает TRUE, если вложенный запрос не возвращает строки или возвращает нулевые строки.

На эти два выражения не влияет наличие значения NULL в результате вложенного запроса. Обычно они выполняются быстрее, поскольку их можно преобразовать в полуобъединения и антиполуобъединения без особых положений об учете нулевых значений.

Примеры

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

Вложенные запросы `IN` и `NOT IN`

В Azure Databricks IN и NOT IN выражения разрешены в WHERE предложении запроса. В отличие от выражения EXISTS выражение IN может возвращать значение TRUE, FALSE или UNKNOWN (NULL). По сути, выражение IN семантически эквивалентно набору условий равенства с разделительным оператором OR между ними. Например, c1 IN (1, 2, 3) семантически эквивалентно (C1 = 1 OR c1 = 2 OR c1 = 3).

Так как мы описываем обработку значений NULL, семантику можно рассматривать на примере обработки значения NULL в операторах сравнения (=) и логических операторах (OR). Подводя итог, ниже приведены правила вычисления результата выражения IN.

TRUE возвращается, когда в списке обнаружено значение, отличное от значения NULL.
FALSE возвращается, когда в списке не найдено значение, отличное от NULL, и список не содержит значений NULL.
UNKNOWN возвращается, когда значение равно NULL, или в списке не найдено значение, отличное от NULL, и список содержит хотя бы одно значение NULL.

NOT IN всегда возвращает UNKNOWN, если список содержит NULL, независимо от входного значения. Причина — IN возвращает UNKNOWN, если значение отсутствует в списке, содержащем NULL, и NOT UNKNOWN тоже является UNKNOWN.

Примеры

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

Поделиться через

Семантика значений NULL

Операторы сравнения

Примеры

Логические операторы

Примеры

Выражения

Выражения, не учитывающие нулевые значения

Примеры

Выражения, которые могут обрабатывать операнды со значением NULL

Примеры

Встроенные статистические выражения

Примеры

Выражения условий в предложениях `WHERE`, `HAVING` и `JOIN`

Примеры

Статистические операторы (`GROUP BY`, `DISTINCT`)

Примеры

Оператор Sort (предложение `ORDER BY`)

Примеры

Операторы множеств (`UNION`, `INTERSECT`, `EXCEPT`)

Примеры

Вложенные запросы `EXISTS` и `NOT EXISTS`

Примеры

Вложенные запросы `IN` и `NOT IN`

Примеры

Обратная связь

Дополнительные ресурсы

Поделиться через

Семантика значений NULL

Операторы сравнения

Примеры

Логические операторы

Примеры

Выражения

Выражения, не учитывающие нулевые значения

Примеры

Выражения, которые могут обрабатывать операнды со значением NULL

Примеры

Встроенные статистические выражения

Примеры

Выражения условий в предложениях WHERE, HAVING и JOIN

Примеры

Статистические операторы (GROUP BY, DISTINCT)

Примеры

Оператор Sort (предложение ORDER BY)

Примеры

Операторы множеств (UNION, INTERSECT, EXCEPT)

Примеры

Вложенные запросы EXISTS и NOT EXISTS

Примеры

Вложенные запросы IN и NOT IN

Примеры

Обратная связь

Дополнительные ресурсы

Выражения условий в предложениях `WHERE`, `HAVING` и `JOIN`

Статистические операторы (`GROUP BY`, `DISTINCT`)

Оператор Sort (предложение `ORDER BY`)

Операторы множеств (`UNION`, `INTERSECT`, `EXCEPT`)

Вложенные запросы `EXISTS` и `NOT EXISTS`

Вложенные запросы `IN` и `NOT IN`