NULL szemantika

A következőre vonatkozik: Databricks SQL Databricks Runtime

A táblázatok sorokból állnak, és minden sor tartalmaz egy oszlopkészletet. Az oszlop egy adattípushoz van társítva, és egy entitás egy adott attribútumát jelöli (például age egy nevű entitás personoszlopa). Előfordulhat, hogy egy sorra jellemző oszlop értéke nem ismert a sor létrejöttének időpontjában. A -ben SQLaz ilyen értékek a következőként jelennek meg: NULL. Ez a szakasz a különböző operátorok, kifejezések és egyéb SQL szerkezetek értékeinek szemantikáját NULL ismerteti.

Az alábbi ábra egy nevű persontábla sémaelrendezését és adatait mutatja be. Az adatok az oszlop értékeit age tartalmazzákNULL, és ezt a táblázatot az alábbi szakaszok különböző példáiban használjuk.

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

Összehasonlító operátorok

Az Azure Databricks támogatja az olyan szabványos összehasonlító operátorokat, mint >a , >=, =és <<=. Ezeknek az operátoroknak az eredménye ismeretlen, vagy NULL ha az egyik operandus vagy mindkét operandus ismeretlen vagy NULL. Az egyenlőség értékeinek összehasonlítása érdekében az NULL Azure Databricks null értékű egyenlőségi operátort (<=>) biztosít, amely akkor ad vissza False értéket, ha az egyik operandus, NULL és akkor ad vissza, True ha mindkét operandus értéke NULL. Az alábbi táblázat az összehasonlító operátorok viselkedését mutatja be, ha az egyik vagy mindkét operandus:NULL

Bal operandus	Jobb oldali operandus	>	>=	=	<	<=	<=>
NULL	Bármely érték	NULL	NULL	NULL	NULL	NULL	False (Hamis)
Bármely érték	NULL	NULL	NULL	NULL	NULL	NULL	False (Hamis)
NULL	NULL	NULL	NULL	NULL	NULL	NULL	True (Igaz)

Példák

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

Logikai operátorok

Az Azure Databricks támogatja az olyan szabványos logikai operátorokat, mint a AND, OR és NOT. Ezek az operátorok argumentumként kifejezéseket használnak Boolean , és értéket adnak vissza Boolean .

Az alábbi táblázatok a logikai operátorok viselkedését szemléltetik, ha az egyik vagy mindkét operandus .NULL

Bal operandus	Jobb oldali operandus	VAGY	ÉS
True (Igaz)	NULL	True (Igaz)	NULL
False (Hamis)	NULL	NULL	False (Hamis)
NULL	True (Igaz)	True (Igaz)	NULL
NULL	False (Hamis)	NULL	False (Hamis)
NULL	NULL	NULL	NULL

Operandus	NEM
NULL	NULL

Példák

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

Kifejezések

Az összehasonlító operátorokat és a logikai operátorokat kifejezésként kezeli a rendszer az Azure Databricksben. Az Azure Databricks a kifejezések egyéb formáit is támogatja, amelyek széles körben besorolhatók a következőként:

• Null intoleráns kifejezések
• Értékoperndusokat feldolgozó NULL kifejezések
  • Ezeknek a kifejezéseknek az eredménye a kifejezéstől függ.

Null intoleráns kifejezések

A null intoleráns kifejezések akkor térnek vissza NULL , ha a kifejezés egy vagy több argumentuma van NULL , és a kifejezések többsége ebbe a kategóriába tartozik.

Példák

> 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 értékű operandusokat feldolgozó kifejezések

Ez a kifejezésosztály az értékek kezelésére NULL lett kialakítva. A kifejezések eredménye a kifejezéstől függ. A függvénykifejezés isnull például null true értékű és nem null értékű bemenetet false ad vissza, ahol a függvény coalesce az operandusok listájában szereplő első nem NULL értéket adja vissza. Azonban akkor ad NULL vissza, coalesce ha az összes operandusa .NULL Az alábbiakban a kategória kifejezéseinek hiányos listája látható.

• COALESCE
• NULLIF
• HANULL
• NVL
• NVL2
• ISAN
• NANVL
• ISNULL
• ISNOTNULL
• ATLEASTNNONNULLS
• IN

Példák

> 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

Beépített összesítő kifejezések

Az aggregátumfüggvények egyetlen eredményt számítanak ki bemeneti sorok feldolgozásával. Az alábbiakban az értékek aggregátumfüggvények általi kezelésének NULL szabályait találja.

• NULL az összesítő függvény figyelmen kívül hagyja az értékeket a feldolgozásból.
  • A szabály alól csak a DARAB(*) függvény kivételt képez.
• Egyes aggregátumfüggvények akkor adnak vissza NULL , ha az összes bemeneti érték üres NULL , vagy a bemeneti adatkészlet üres. Ezeknek a függvényeknek a listája a következő:
  • MAX
  • MIN
  • SUM
  • AVG
  • EVERY
  • ANY
  • SOME

Példák

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

Feltételkifejezések a WHERE, HAVINGés JOIN záradékokban

WHERE, HAVING az operátorok a felhasználó által megadott feltétel alapján szűrik a sorokat. Az JOIN operátorokkal két tábla sorait egyesítheti egy illesztésfeltétel alapján. Mindhárom operátor esetében a feltételkifejezés egy logikai kifejezés, amely a vagy FalseUnknown (NULL)a értéket adja visszaTrue. Ezek "teljesülnek", ha a feltétel Trueeredménye .

Példák

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

Összesítő operátorok (GROUP BY, DISTINCT)

Az Összehasonlító operátorok című témakörben leírtak szerint két NULL érték nem egyenlő. Csoportosítás és eltérő feldolgozás céljából azonban a két vagy több érték NULL dataugyanabba a gyűjtőbe van csoportosítva. Ez a viselkedés megfelel az SQL szabványnak és más vállalati adatbázis-kezelő rendszereknek.

Példák

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

Rendezési operátor (ORDER BY záradék)

Az Azure Databricks támogatja a nullrendezési specifikációt a záradékban ORDER BY . Az Azure Databricks úgy dolgozza fel a ORDER BY záradékot, hogy az NULL összes értéket először vagy végre elhelyezi a nullrendezési specifikációtól függően. Alapértelmezés szerint az NULL összes érték elsőként lesz elhelyezve.

Példák

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

Operátorok beállítása (UNION, INTERSECT, EXCEPT)

NULL értékeket a rendszer null-biztonságos módon hasonlítja össze a halmazműveletek összefüggésében való egyenlőség érdekében. Ez azt jelenti, hogy a sorok összehasonlítása során két NULL érték egyenlőnek tekinthető a normál EqualTo(=) operátorral ellentétben.

Példák

> 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 és NOT EXISTS albekérdezések

Az Azure Databricksben EXISTS és NOT EXISTS a kifejezések egy WHERE záradékon belül engedélyezve vannak. Ezek logikai kifejezések, amelyek vagy vagy FALSEértéket adnak visszaTRUE. Más szavakkal: EXISTS a tagsági feltétel, és akkor ad vissza TRUE , amikor az al lekérdezés, amelyre hivatkozik, egy vagy több sort ad vissza. Hasonlóképpen, a NOT EXISTS nem tagsági feltétel, és akkor ad vissza TRUE , ha az al lekérdezésből nem ad vissza sorokat vagy nulla sort.

Ezt a két kifejezést nem befolyásolja a NULL jelenléte az alkérdezés eredményében. Ezek általában gyorsabbak, mert a null tudatosságra vonatkozó különleges rendelkezések nélkül félcsatlakozóvá és anti-félcsatlakozóvá alakíthatók.

Példák

-- 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 és NOT IN albekérdezések

Az Azure Databricksben IN és NOT IN a kifejezések egy lekérdezés záradékában WHERE engedélyezve vannak. A kifejezéstől eltérően a EXISTS kifejezés egy vagy FALSEUNKNOWN (NULL) értéket is visszaadhatTRUE. IN A kifejezés elméletileg IN szemantikailag egyenértékű a disjunctive operátorral (OR) elválasztott egyenlőségi feltételkészlettel. Például a c1 IN (1, 2, 3) szemantikailag egyenértékű a következőel (C1 = 1 OR c1 = 2 OR c1 = 3): .

Ami a kezelési NULL értékeket illeti, a szemantika az összehasonlító operátorok() és a NULL logikai operátorok(=OR) értékkezeléséből következtethető. Összefoglalva, az alábbi szabályok egy kifejezés eredményének IN kiszámítására vonatkoznak.

• TRUE akkor lesz visszaadva, ha a kérdéses nem NULL érték megtalálható a listában
• FALSE akkor lesz visszaadva, ha a nem NULL érték nem található a listában, és a lista nem tartalmaz NULL értékeket
• UNKNOWN akkor lesz visszaadva, ha az érték NULL, vagy a nem NULL érték nem található a listában, és a lista legalább egy NULL értéket tartalmaz

NOT IN mindig ISMERETLEN értéket ad vissza, ha a lista tartalmazza NULLa értéket, függetlenül a bemeneti értéktől. Ennek az az oka, hogy IN visszaadjaUNKNOWN, ha az érték nem szerepel a listábanNULL, és mert ismét .UNKNOWNNOT UNKNOWN

Példák

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