Semantische NULL-waarden

Artikel
07/05/2024

Van toepassing op: vinkje als ja aan Databricks SQL Databricks Runtime

Een tabel bestaat uit een set rijen en elke rij bevat een set kolommen. Een kolom is gekoppeld aan een gegevenstype en vertegenwoordigt een specifiek kenmerk van een entiteit (bijvoorbeeld age een kolom van een entiteit genaamd person). Soms is de waarde van een kolom die specifiek is voor een rij, niet bekend op het moment dat de rij bestaat. In SQL, dergelijke waarden worden weergegeven als NULL. In deze sectie worden de semantiek van de verwerking van NULL waarden in verschillende operators, expressies en andere SQL constructies beschreven.

Hieronder ziet u de schema-indeling en gegevens van een tabel met de naam person. De gegevens bevatten NULL waarden in de age kolom en deze tabel wordt gebruikt in verschillende voorbeelden in de onderstaande secties.

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

Vergelijkingsoperators

Azure Databricks ondersteunt de standaardvergelijkingsoperators zoals >, >=en <==< . Het resultaat van deze operatoren is onbekend of NULL wanneer een van de operanden of beide operanden onbekend NULLof . Om de NULL waarden voor gelijkheid te vergelijken, biedt Azure Databricks een null-veilige gelijkoperator (<=>), die retourneert False wanneer een van de operanden is NULL en retourneert True wanneer beide operanden zijn NULL. De volgende tabel illustreert het gedrag van vergelijkingsoperatoren wanneer een of beide operanden zijn NULL:

Linkeroperand	Rechteroperand	`>`	`>=`	`=`	`<`	`<=`	`<=>`
NULL	Alle waarden	NULL	NULL	NULL	NULL	NULL	Onwaar
Alle waarden	NULL	NULL	NULL	NULL	NULL	NULL	Onwaar
NULL	NULL	NULL	NULL	NULL	NULL	NULL	Waar

Voorbeelden

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

Logische operators

Azure Databricks ondersteunt standaard logische operators zoals AND, OR en NOT. Deze operators gebruiken Boolean expressies als argumenten en retourneren een Boolean waarde.

De volgende tabellen illustreren het gedrag van logische operators wanneer een of beide operanden zijn NULL.

Linkeroperand	Rechteroperand	OF	EN
Waar	NULL	Waar	NULL
Onwaar	NULL	NULL	Onwaar
NULL	Waar	Waar	NULL
NULL	Onwaar	NULL	Onwaar
NULL	NULL	NULL	NULL

Operand	NIET
NULL	NULL

Voorbeelden

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

Expressies

De vergelijkingsoperators en logische operators worden behandeld als expressies in Azure Databricks. Azure Databricks biedt ook ondersteuning voor andere vormen van expressies, die breed kunnen worden geclassificeerd als:

Null-intolerant-expressies
Expressies die waardeoperands kunnen verwerken NULL
- Het resultaat van deze expressies is afhankelijk van de expressie zelf.

Null-intolerant-expressies

Null-inlerant-expressies worden geretourneerd NULL wanneer een of meer expressieargumenten zijn NULL en de meeste expressies in deze categorie vallen.

Voorbeelden

> 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

Expressies die null-waardeoperands kunnen verwerken

Deze klasse expressies zijn ontworpen om waarden te verwerken NULL . Het resultaat van de expressies is afhankelijk van de expressie zelf. Als voorbeeld retourneert de functie-expressie isnull een true op null-invoer en false voor niet-null-invoer waarbij als functie coalesce de eerste niet-waarde NULL retourneert in de lijst met operanden. coalesce Retourneert NULL echter wanneer alle operanden zijnNULL. Hieronder ziet u een onvolledige lijst met expressies van deze categorie.

COALESCE
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
IN

Voorbeelden

> 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

Ingebouwde statistische expressies

Statistische functies berekenen één resultaat door een set invoerrijen te verwerken. Hieronder ziet u de regels voor hoe NULL waarden worden verwerkt door statistische functies.

NULL waarden worden genegeerd voor verwerking door alle statistische functies.
- Alleen uitzondering op deze regel is de functie COUNT(*).
Sommige statistische functies retourneren NULL wanneer alle invoerwaarden zijn NULL of als de invoergegevensset leeg is. De lijst met deze functies is:
- MAX
- MIN
- SUM
- AVG
- EVERY
- ANY
- SOME

Voorbeelden

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

Voorwaardeexpressies in `WHERE`, `HAVING`en `JOIN` componenten

WHEREoperators HAVING filteren rijen op basis van de door de gebruiker opgegeven voorwaarde. Een JOIN operator wordt gebruikt om rijen uit twee tabellen te combineren op basis van een joinvoorwaarde. Voor alle drie de operators is een voorwaardeexpressie een Booleaanse expressie en kan deze retourneren True, False of Unknown (NULL). Ze zijn "voldaan" als het resultaat van de voorwaarde is True.

Voorbeelden

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

Geaggregeerde operators (`GROUP BY`, `DISTINCT`)

Zoals besproken in vergelijkingsoperatoren, zijn twee NULL waarden niet gelijk. Voor groepering en afzonderlijke verwerking worden de twee of meer waarden met NULL dataelkaar echter gegroepeerd in dezelfde bucket. Dit gedrag voldoet aan de SQL-standaard en met andere databasebeheersystemen voor ondernemingen.

Voorbeelden

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

Sorteeroperator (`ORDER BY` component)

Azure Databricks biedt ondersteuning voor specificatie van null-bestellingen in component ORDER BY . Azure Databricks verwerkt de ORDER BY component door alle NULL waarden eerst of ten slotte te plaatsen, afhankelijk van de specificatie van null-volgorde. Standaard worden alle NULL waarden eerst geplaatst.

Voorbeelden

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

Operators instellen (`UNION`, `INTERSECT`, `EXCEPT`)

NULL waarden worden vergeleken op een null-veilige manier voor gelijkheid in de context van setbewerkingen. Dat betekent dat bij het vergelijken van rijen twee NULL waarden worden beschouwd als gelijk aan de normale EqualTooperator (=).

Voorbeelden

> 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` en `NOT EXISTS` subquery's

In Azure Databricks EXISTS zijn expressies toegestaan NOT EXISTS binnen een WHERE component. Dit zijn Booleaanse expressies die ofwel TRUE ofwel FALSE. Met andere woorden, EXISTS is een lidmaatschapsvoorwaarde en retourneert TRUE wanneer de subquery waarnaar wordt verwezen een of meer rijen retourneert. Op dezelfde manier is NOT EXISTS een voorwaarde die geen lidmaatschap is en wordt geretourneerd TRUE wanneer er geen rijen of nul rijen worden geretourneerd uit de subquery.

Deze twee expressies worden niet beïnvloed door de aanwezigheid van NULL in het resultaat van de subquery. Ze zijn normaal gesproken sneller omdat ze kunnen worden omgezet in semi-joins en anti-semi-semi-joins zonder speciale bepalingen voor null-bewustzijn.

Voorbeelden

-- 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` en `NOT IN` subquery's

In Azure Databricks IN zijn expressies toegestaan NOT IN binnen een WHERE component van een query. In tegenstelling tot de EXISTS expressie IN kan een expressie een TRUEof UNKNOWN (NULL) FALSE waarde retourneren. Conceptueel gezien is een IN expressie semantisch gelijk aan een set gelijkheidsvoorwaarde, gescheiden door een disjunctive operator (OR). C1 IN (1, 2, 3) is bijvoorbeeld semantisch gelijk aan (C1 = 1 OR c1 = 2 OR c1 = 3).

Wat de verwerking NULL van waarden betreft, kunnen de semantiek worden afgeleid van de NULL waardeafhandeling in vergelijkingsoperators(=) en logische operators(OR). Hieronder vindt u de regels voor het berekenen van het resultaat van een IN expressie.

TRUE wordt geretourneerd wanneer de niet-NULL-waarde in kwestie wordt gevonden in de lijst
FALSE wordt geretourneerd wanneer de niet-NULL-waarde niet wordt gevonden in de lijst en de lijst geen NULL-waarden bevat
UNKNOWN wordt geretourneerd wanneer de waarde is NULL, of als de niet-NULL-waarde niet in de lijst wordt gevonden en de lijst ten minste één NULL waarde bevat

NOT IN retourneert altijd ONBEKEND wanneer de lijst bevat NULL, ongeacht de invoerwaarde. Dit komt omdat IN retourneert UNKNOWN als de waarde zich niet in de lijst bevindt die NULL, en omdat NOT UNKNOWN het opnieuw UNKNOWNis .

Voorbeelden

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

Delen via

Semantische NULL-waarden

Vergelijkingsoperators

Voorbeelden

Logische operators

Voorbeelden

Expressies

Null-intolerant-expressies

Voorbeelden

Expressies die null-waardeoperands kunnen verwerken

Voorbeelden

Ingebouwde statistische expressies

Voorbeelden

Voorwaardeexpressies in `WHERE`, `HAVING`en `JOIN` componenten

Voorbeelden

Geaggregeerde operators (`GROUP BY`, `DISTINCT`)

Voorbeelden

Sorteeroperator (`ORDER BY` component)

Voorbeelden

Operators instellen (`UNION`, `INTERSECT`, `EXCEPT`)

Voorbeelden

`EXISTS` en `NOT EXISTS` subquery's

Voorbeelden

`IN` en `NOT IN` subquery's

Voorbeelden

Feedback

Aanvullende resources

Delen via

Semantische NULL-waarden

Vergelijkingsoperators

Voorbeelden

Logische operators

Voorbeelden

Expressies

Null-intolerant-expressies

Voorbeelden

Expressies die null-waardeoperands kunnen verwerken

Voorbeelden

Ingebouwde statistische expressies

Voorbeelden

Voorwaardeexpressies in WHERE, HAVINGen JOIN componenten

Voorbeelden

Geaggregeerde operators (GROUP BY, DISTINCT)

Voorbeelden

Sorteeroperator (ORDER BY component)

Voorbeelden

Operators instellen (UNION, INTERSECT, EXCEPT)

Voorbeelden

EXISTS en NOT EXISTS subquery's

Voorbeelden

IN en NOT IN subquery's

Voorbeelden

Feedback

Aanvullende resources

Voorwaardeexpressies in `WHERE`, `HAVING`en `JOIN` componenten

Geaggregeerde operators (`GROUP BY`, `DISTINCT`)

Sorteeroperator (`ORDER BY` component)

Operators instellen (`UNION`, `INTERSECT`, `EXCEPT`)

`EXISTS` en `NOT EXISTS` subquery's

`IN` en `NOT IN` subquery's