Семантика NULL
Область применения: 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
, если один из операндов равен NULL
, и возвращает, True
если оба операнда имеют значение NULL
. В следующей таблице показано поведение операторов сравнения, если один или оба операнда имеют значение NULL
:
Левый операнд | Правый операнд | > |
>= |
= |
< |
<= |
<=> |
---|---|---|---|---|---|---|---|
NULL | Любое значение | NULL | NULL | NULL | NULL | NULL | Ложных |
Любое значение | NULL | NULL | NULL | NULL | NULL | NULL | Ложных |
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 |
Ложных | NULL | NULL | Ложных |
NULL | Истинный | Истинный | NULL |
NULL | Ложных | NULL | Ложных |
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 возвращаются 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
значение в списке операндов. Однако возвращаетNULL
, coalesce
если все операнды имеют значение 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
Операторы set (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
эквивалентно набору условий равенства, разделенных оператором disjunctive (OR
).
Например, c1 IN (1, 2, 3) семантически эквивалентен (C1 = 1 OR c1 = 2 OR c1 = 3)
.
Что касается обработки NULL
значений, семантика может быть выведена из обработки значений NULL
в операторах сравнения(=
) и логических операторах(OR
).
Ниже приведены правила вычисления результата IN
выражения.
TRUE
возвращается при обнаружении в списке значения, отличного от NULL.FALSE
возвращается, если значение, отличное от NULL, не найдено в списке и список не содержит значений NULLUNKNOWN
возвращается, если значение равно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
---- ---