NULL-semantiek
Van toepassing op: Databricks 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 met de naam 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
worden dergelijke waarden weergegeven als NULL
. In deze sectie wordt 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
Vergelijkingsoperatoren
Azure Databricks ondersteunt de standaardvergelijkingsoperators, zoals >
, >=
, =
<
en <=
.
Het resultaat van deze operators is onbekend of NULL
wanneer een van de operanden of beide operanden onbekend is of NULL
. Om de NULL
waarden voor gelijkheid te vergelijken, biedt Azure Databricks een null-safe equal-operator (<=>
), die retourneert False
wanneer een van de operands is NULL
en retourneert True
wanneer beide operanden zijn NULL
. In de volgende tabel ziet u het gedrag van vergelijkingsoperatoren wanneer een of beide operanden zijn NULL
:
Operand links | Rechteroperand | > |
>= |
= |
< |
<= |
<=> |
---|---|---|---|---|---|---|---|
NULL | Elke waarde | NULL | NULL | NULL | NULL | NULL | Valse |
Elke waarde | NULL | NULL | NULL | NULL | NULL | NULL | Valse |
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
en OR
NOT
.
Deze operators nemen Boolean
expressies als argumenten en retourneren een Boolean
waarde.
In de volgende tabellen ziet u het gedrag van logische operators wanneer een of beide operanden zijn NULL
.
Operand links | Rechteroperand | OF | EN |
---|---|---|---|
Waar | NULL | Waar | NULL |
Valse | NULL | NULL | Valse |
NULL | Waar | Waar | NULL |
NULL | Valse | NULL | Valse |
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 vergelijkingsoperatoren en logische operators worden behandeld als expressies in Azure Databricks. Azure Databricks ondersteunt ook andere vormen van expressies, die breed kunnen worden geclassificeerd als:
- Intolerante expressies in null
- Expressies die waardeoperanden kunnen verwerken
NULL
- Het resultaat van deze expressies is afhankelijk van de expressie zelf.
Intolerante expressies in null
Null-intolerant-expressies worden geretourneerd NULL
wanneer een of meer argumenten van expressie 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 operanden van null-waarden kunnen verwerken
Deze klasse expressies is ontworpen voor het verwerken NULL
van waarden. Het resultaat van de expressies is afhankelijk van de expressie zelf. Functieexpressie isnull
retourneert bijvoorbeeld een true
op null-invoer en false
niet-null-invoer, waarbij as coalesce
de eerste niet-waarde NULL
in de lijst met operanden retourneert. Retourneert NULL
echter coalesce
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
- INCH
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 aggregatie-expressies
Statistische functies berekenen één resultaat door een set invoerrijen te verwerken. Hieronder ziet u de regels voor het NULL
verwerken van waarden door statistische functies.
NULL
-waarden worden genegeerd voor verwerking door alle statistische functies.- De enige uitzondering op deze regel is de functie AANTAL(*).
- Sommige statistische functies worden geretourneerd
NULL
wanneer alle invoerwaarden zijnNULL
of wanneer 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
Voorwaarde-expressies in WHERE
componenten , HAVING
en JOIN
WHERE
, HAVING
filteren operators 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 operatoren is een voorwaardeexpressie een Booleaanse expressie en kan , False
of Unknown (NULL)
worden geretourneerdTrue
. 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
Samenvoegingsoperatoren (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 data
echter gegroepeerd in dezelfde bucket. Dit gedrag is in overeenstemming met 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-volgorde in ORDER BY
component. Azure Databricks verwerkt de ORDER BY
component door alle NULL
waarden eerst of ten slotte afhankelijk van de specificatie van de null-volgorde te plaatsen. 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. Dit betekent dat bij het vergelijken van rijen twee NULL
waarden als gelijk worden beschouwd in tegenstelling tot de reguliere EqualTo
operator (=
).
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 en NOT EXISTS
toegestaan binnen een WHERE
-component.
Dit zijn Booleaanse expressies die of FALSE
retournerenTRUE
. 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 niet-lidmaatschapsvoorwaarde 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 doorgaans sneller omdat ze kunnen worden omgezet in semi-joins en anti-semi-joins zonder speciale bepalingen voor null awareness.
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 en NOT IN
toegestaan binnen een WHERE
component van een query. In tegenstelling tot de EXISTS
expressie kan IN
expressie een TRUE
waarde , FALSE
of UNKNOWN (NULL)
retourneren. Conceptueel gezien is een IN
expressie semantisch equivalent 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, kan de semantiek worden afgeleid uit de NULL
waardeafhandeling in vergelijkingsoperatoren(=
) en logische operatoren(OR
).
Kortom, 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 lijstFALSE
wordt geretourneerd wanneer de niet-NULL-waarde niet in de lijst wordt gevonden en de lijst geen NULL-waarden bevatUNKNOWN
wordt geretourneerd wanneer de waarde isNULL
, of de niet-NULL-waarde niet in de lijst wordt gevonden en de lijst ten minste éénNULL
waarde bevat
NOT IN
retourneert altijd ONBEKEND wanneer de lijst bevat NULL
, ongeacht de invoerwaarde.
Dit komt doordat IN
retourneert UNKNOWN
als de waarde niet voorkomt in de lijst met NULL
, en omdat NOT UNKNOWN
opnieuw UNKNOWN
is.
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
---- ---