Semantyka wartości null

Artykuł
07/05/2024

Dotyczy: zaznacz pole wyboru oznaczone jako tak Databricks SQL Databricks Runtime

Tabela składa się z zestawu wierszy, a każdy wiersz zawiera zestaw kolumn. Kolumna jest skojarzona z typem danych i reprezentuje określony atrybut jednostki (na przykład age jest kolumną jednostki o nazwie person). Czasami wartość kolumny specyficznej dla wiersza nie jest znana w momencie wystąpienia wiersza. W SQLpliku takie wartości są reprezentowane jako NULL. Ta sekcja zawiera szczegółowe informacje na temat semantyki wartości obsługujących NULL różne operatory, wyrażenia i inne SQL konstrukcje.

Poniżej przedstawiono układ schematu i dane tabeli o nazwie person. Dane zawierają NULL wartości w age kolumnie, a ta tabela jest używana w różnych przykładach w poniższych sekcjach.

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

Operatory porównania

Usługa Azure Databricks obsługuje standardowe operatory porównania, takie jak >, >=, =< i <=. Wynik tych operatorów jest nieznany lub NULL gdy jeden z operandów lub oba operandy są nieznane lub NULL. Aby porównać NULL wartości równości, usługa Azure Databricks udostępnia bezpieczny dla wartości null operator równości (<=>), który zwraca wartość, gdy jeden z operandów jest NULL i zwraca True False wartość , gdy oba operandy to NULL. W poniższej tabeli przedstawiono zachowanie operatorów porównania, gdy jeden lub oba operandy to NULL:

Lewy operand	Prawy operand	`>`	`>=`	`=`	`<`	`<=`	`<=>`
NULL	Dowolna wartość	NULL	NULL	NULL	NULL	NULL	Fałsz
Dowolna wartość	NULL	NULL	NULL	NULL	NULL	NULL	Fałsz
NULL	NULL	NULL	NULL	NULL	NULL	NULL	Prawda

Przykłady

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

Operatory logiczne

Usługa Azure Databricks obsługuje standardowe operatory logiczne, takie jak AND, OR i NOT. Te operatory przyjmują Boolean wyrażenia jako argumenty i zwracają Boolean wartość.

W poniższych tabelach przedstawiono zachowanie operatorów logicznych, gdy jeden lub oba operandy to NULL.

Lewy operand	Prawy operand	LUB	ORAZ
Prawda	NULL	Prawda	NULL
Fałsz	NULL	NULL	Fałsz
NULL	Prawda	Prawda	NULL
NULL	Fałsz	NULL	Fałsz
NULL	NULL	NULL	NULL

Operand	NIE
NULL	NULL

Przykłady

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

Wyrażenia

Operatory porównania i operatory logiczne są traktowane jako wyrażenia w usłudze Azure Databricks. Usługa Azure Databricks obsługuje również inne formy wyrażeń, które mogą być szeroko klasyfikowane jako:

Wyrażenia odporne na wartości null
Wyrażenia, które mogą przetwarzać NULL operandy wartości
- Wynik tych wyrażeń zależy od samego wyrażenia.

Wyrażenia odporne na wartości null

Wyrażenia odporne na wartości null są zwracane NULL , gdy co najmniej jeden argument wyrażenia jest NULL i większość wyrażeń należy do tej kategorii.

Przykłady

> 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

Wyrażenia, które mogą przetwarzać operandy wartości null

Ta klasa wyrażeń jest przeznaczona do obsługi NULL wartości. Wynik wyrażeń zależy od samego wyrażenia. Na przykład wyrażenie funkcji zwraca true wartość isnull dla danych wejściowych o wartości null i false dla danych wejściowych innych niż null, gdzie funkcja coalesce zwraca pierwszą wartość inną NULL niż na liście operandów. Jednak zwraca wartość NULL , coalesce gdy wszystkie jego operandy to NULL. Poniżej znajduje się niekompletna lista wyrażeń tej kategorii.

COALESCE
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
IN

Przykłady

> 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

Wbudowane wyrażenia agregacyjne

Funkcje agregujące obliczają pojedynczy wynik, przetwarzając zestaw wierszy wejściowych. Poniżej przedstawiono reguły obsługi NULL wartości przez funkcje agregujące.

NULL wartości są ignorowane z przetwarzania przez wszystkie funkcje agregujące.
- Jedynym wyjątkiem od tej reguły jest funkcja COUNT(*).
Niektóre funkcje agregujące zwracają NULL wartość, gdy wszystkie wartości wejściowe są NULL lub zestaw danych wejściowych jest pusty. Lista tych funkcji to:
- MAX
- MIN
- SUM
- AVG
- EVERY
- ANY
- SOME

Przykłady

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

Wyrażenia warunku w klauzulach `WHERE`, `HAVING`i `JOIN`

WHERE, HAVING operatory filtrować wiersze na podstawie określonego warunku użytkownika. Operator JOIN służy do łączenia wierszy z dwóch tabel na podstawie warunku sprzężenia. Dla wszystkich trzech operatorów wyrażenie warunku jest wyrażeniem logicznym i może zwrócić Truewartość , False lub Unknown (NULL). Są one "spełnione", jeśli wynikiem warunku jest True.

Przykłady

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

Operatory agregacji (`GROUP BY`, `DISTINCT`)

Jak opisano w temacie Operatory porównania, dwie NULL wartości nie są równe. Jednak w celu grupowania i odrębnego przetwarzania wartości z co najmniej dwiema wartościami NULL datasą grupowane razem w tym samym zasobniku. To zachowanie jest zgodne ze standardem SQL i innymi systemami zarządzania bazami danych przedsiębiorstwa.

Przykłady

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

Operator sortowania (`ORDER BY` klauzula)

Usługa Azure Databricks obsługuje specyfikację porządkowania o wartości null w ORDER BY klauzuli . Usługa Azure Databricks przetwarza klauzulę ORDER BY , umieszczając wszystkie NULL wartości na początku lub w końcu w zależności od specyfikacji porządkowania wartości null. Domyślnie wszystkie NULL wartości są umieszczane na początku.

Przykłady

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

Ustawianie operatorów (`UNION`, `INTERSECT`, `EXCEPT`)

NULL wartości są porównywane w bezpieczny dla wartości null sposób równości w kontekście operacji zestawu. Oznacza to, że podczas porównywania wierszy dwie NULL wartości są traktowane jako równe w przeciwieństwie do operatora zwykłego EqualTo(=).

Przykłady

> 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` i `NOT EXISTS` podzapytania

W usłudze Azure Databricks EXISTS wyrażenia są NOT EXISTS dozwolone wewnątrz klauzuli WHERE . Są to wyrażenia logiczne, które zwracają wartość TRUE lub FALSE. Innymi słowy, EXISTS jest warunkiem członkostwa i zwraca, TRUE gdy podzapytywanie odnosi się do zwraca co najmniej jednego wiersza. Podobnie NIE ISTNIEJE jest warunkiem braku członkostwa i zwraca TRUE , gdy nie są zwracane żadne wiersze lub zero wierszy z podzapytania.

Te dwa wyrażenia nie mają wpływu na obecność wartości NULL w wyniku podzapytania. Są one zwykle szybsze, ponieważ mogą być konwertowane na półjoinki i antysemiowe bez specjalnych przepisów dotyczących świadomości wartości null.

Przykłady

-- 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` i `NOT IN` podzapytania

W usłudze Azure Databricks IN wyrażenia są NOT IN dozwolone wewnątrz WHERE klauzuli zapytania. EXISTS W przeciwieństwie do wyrażenia wyrażenie IN może zwracać FALSE TRUEwartość lub UNKNOWN (NULL) . Koncepcyjnie wyrażenie jest semantycznie IN równoważne zestawowi warunku równości oddzielonego operatorem rozłącznym (OR). Na przykład c1 IN (1, 2, 3) jest semantycznie równoważne .(C1 = 1 OR c1 = 2 OR c1 = 3)

Jeśli chodzi o obsługę NULL wartości, semantyka może zostać wywołana z NULL obsługi wartości w operatorach porównania(=) i operatorach logicznych(OR). Poniżej przedstawiono reguły obliczania wyniku IN wyrażenia.

TRUE jest zwracany po znalezieniu żądanej wartości innej niż NULL na liście
FALSE jest zwracany, gdy wartość innej niż NULL nie zostanie znaleziona na liście, a lista nie zawiera wartości NULL
UNKNOWN jest zwracany, gdy wartość to NULL, lub nie można odnaleźć wartości innej niż NULL na liście, a lista zawiera co najmniej jedną NULL wartość

NOT IN zawsze zwraca wartość UNKNOWN, gdy lista zawiera NULLwartość , niezależnie od wartości wejściowej. Jest to spowodowane tym, że IN zwraca UNKNOWN wartość, jeśli wartość nie znajduje się na liście zawierającej NULLelement , a ponieważ NOT UNKNOWN jest ponownie UNKNOWN.

Przykłady

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

Udostępnij za pośrednictwem

Semantyka wartości null

Operatory porównania

Przykłady

Operatory logiczne

Przykłady

Wyrażenia

Wyrażenia odporne na wartości null

Przykłady

Wyrażenia, które mogą przetwarzać operandy wartości null

Przykłady

Wbudowane wyrażenia agregacyjne

Przykłady

Wyrażenia warunku w klauzulach `WHERE`, `HAVING`i `JOIN`

Przykłady

Operatory agregacji (`GROUP BY`, `DISTINCT`)

Przykłady

Operator sortowania (`ORDER BY` klauzula)

Przykłady

Ustawianie operatorów (`UNION`, `INTERSECT`, `EXCEPT`)

Przykłady

`EXISTS` i `NOT EXISTS` podzapytania

Przykłady

`IN` i `NOT IN` podzapytania

Przykłady

Opinia

Dodatkowe zasoby

Udostępnij za pośrednictwem

Semantyka wartości null

Operatory porównania

Przykłady

Operatory logiczne

Przykłady

Wyrażenia

Wyrażenia odporne na wartości null

Przykłady

Wyrażenia, które mogą przetwarzać operandy wartości null

Przykłady

Wbudowane wyrażenia agregacyjne

Przykłady

Wyrażenia warunku w klauzulach WHERE, HAVINGi JOIN

Przykłady

Operatory agregacji (GROUP BY, DISTINCT)

Przykłady

Operator sortowania (ORDER BY klauzula)

Przykłady

Ustawianie operatorów (UNION, INTERSECT, EXCEPT)

Przykłady

EXISTS i NOT EXISTS podzapytania

Przykłady

IN i NOT IN podzapytania

Przykłady

Opinia

Dodatkowe zasoby

Wyrażenia warunku w klauzulach `WHERE`, `HAVING`i `JOIN`

Operatory agregacji (`GROUP BY`, `DISTINCT`)

Operator sortowania (`ORDER BY` klauzula)

Ustawianie operatorów (`UNION`, `INTERSECT`, `EXCEPT`)

`EXISTS` i `NOT EXISTS` podzapytania

`IN` i `NOT IN` podzapytania