NULL szemantika

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

A táblázatok sorokból állnak, és minden sor oszlopkészletet tartalmaz. Az oszlop egy adattípushoz van társítva, és egy entitás adott attribútumát jelöli (például age egy personnevű entitás oszlopa). Előfordulhat, hogy egy sorra jellemző oszlop értéke nem ismert a sor létrehozásakor. A SQL-ban ezek az értékek NULL-ként vannak jelölve. Ez a szakasz a különböző operátorok, kifejezések és egyéb NULL szerkezetek SQL értékek kezelésének szemantikáját ismerteti.

Az alábbiakban egy personnevű tábla sémaelrendezését és adatait mutatjuk be. Az adatok NULL értékeket tartalmaznak a age oszlopban, és ezt a táblázatot az alábbi szakaszokban található különböző példákban 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 NULL értékeinek összehasonlítása érdekében az Azure Databricks null értékű egyenlő operátort (<=>) biztosít, amely False ad vissza, ha az egyik operandus NULL, és True ad vissza, ha mindkét operandus NULL. Az alábbi táblázat az összehasonlító operátorok viselkedését mutatja be, ha egy vagy mindkét operandus NULL:

Bal operandus	Jobb operandus	>	>=	=	<	<=	<=>
NULL	Bármilyen érték	NULL	NULL	NULL	NULL	NULL	Hamis
Bármilyen érték	NULL	NULL	NULL	NULL	NULL	NULL	Hamis
NULL	NULL	NULL	NULL	NULL	NULL	NULL	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 operands is `NULL`
> SELECT 5 <=> null AS expression_output;
 expression_output
 -----------------
             false

-- Null-safe equal operator return `True` when both of the operands 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. ORNOT 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 egy vagy mindkét operandus NULL.

Bal operandus	Jobb operandus	VAGY	ÉS
Igaz	NULL	Igaz	NULL
Hamis	NULL	NULL	Hamis
NULL	Igaz	Igaz	NULL
NULL	Hamis	NULL	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átorok és a logikai operátorok kifejezésként vannak kezelve 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 magától a kifejezéstől függ.

Null intoleráns kifejezések

A null értékű intoleráns kifejezések akkor jelennek meg NULL , ha egy vagy több kifejezés 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 NULL értékek kezelésére lett kialakítva. A kifejezések eredménye magától a kifejezéstől függ. Példaként a függvénykifejezés isnull null bemeneten true-t ad vissza, a nem null bemeneten pedig false-t, ahol a coalesce függvény az operandusok listájából az első nem NULL értéket adja vissza. Azonban a coalesceNULL-t ad vissza, ha az összes operandusa NULL. Az alábbiakban a kategória kifejezéseinek hiányos listája látható.

• Egyesít
• NULLIF
• IFNULL
• NVL
• NVL2
• ISNAN
• 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 összesítő függvények egyetlen eredményt számítanak ki bemeneti sorok feldolgozásával. Az alábbiakban a NULL értékek összesítő függvények általi kezelésére vonatkozó szabályokat talál.

• NULL értékek figyelmen kívül maradnak a feldolgozásból minden összesítő függvény által.
  • A szabály alól csak a DARAB(*) függvény kivételt képez.
• Egyes összesítő függvények NULL adnak vissza, ha az összes bemeneti érték NULL, vagy a bemeneti adatkészlet üres. A függvények 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. A JOIN operátor két táblából származó sorokat egyesít egy illesztési feltétel alapján. Mind a három operátor esetében a feltételkifejezés logikai kifejezés, és visszaadható True, False vagy Unknown (NULL). Ők elégedettek, ha a feltétel eredménye True.

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)

A Összehasonlító operátorokcímű témakörben leírtak szerint két NULL érték nem egyenlő. A csoportosítás és a különböző feldolgozás céljából azonban a két vagy több NULL dataértékkel rendelkező értékek ugyanabba a gyűjtőbe vannak 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 null értékek rendezésének specifikációját a ORDER BY záradékban. Az Azure Databricks úgy dolgozza fel a ORDER BY záradékot, hogy az összes NULL értéket először vagy utoljára a nullrendezési specifikációtól függően helyezi el. Alapértelmezés szerint az összes NULL é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 null-biztos módon hasonlítjuk össze a halmazműveletek során az egyenlőség érdekében. Ez azt jelenti, hogy a sorok összehasonlítása során két NULL értéket egyenlőnek tekintenek, ellentétben a hagyományos EqualTo(=) operátorral.

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 a EXISTS és NOT EXISTS kifejezések egy WHERE záradékon belül engedélyezettek. Ezek logikai kifejezések, amelyek eredménye vagy TRUE, vagy FALSE. Más szóval, a EXISTS egy tagsági feltétel, amely TRUE értéket ad vissza, ha az allekérdezés egy vagy több sort ad vissza. Hasonlóképpen, a NOT EXISTS egy tagsági viszonyt kizáró feltétel, és TRUE ad vissza, ha nincs visszakapott sor vagy nulla sor az al lekérdezésből.

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él-, illetve anti-féljoinokká alakíthatók át.

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 az IN és NOT IN kifejezések egy lekérdezés WHERE záradékán belül engedélyezettek. A EXISTS kifejezéstől eltérően a IN kifejezés visszaadhat egy TRUE, FALSE vagy UNKNOWN (NULL) értéket. A IN kifejezések elméletileg szemantikailag egyenértékűek 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ű.(C1 = 1 OR c1 = 2 OR c1 = 3)

Ami az NULL értékeket illeti, a szemantika az összehasonlító operátorok (NULL) és a 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 kerül visszaadásra, 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 akkor ad UNKNOWN vissza, ha a lista tartalmazza NULL , és nem tartalmazza a bemeneti értéket. Ennek az az oka, hogy IN akkor adja vissza UNKNOWN , ha az érték nem szerepel a listában NULL, és NOT UNKNOWN ismét UNKNOWNszerepel.

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