NULL in EXASolution
NULL Behandlung in EXASolution
Generell repräsentiert ein Nullwert einen nicht definierten Wert. In SQL ist NULL kein Wert, da die grundlegende Eigenschaft von Werten, nämlich sie mit anderen Werten des gleichen Datentyps vergleichen zu können, nicht gegeben ist.
Insbesondere ergibt der Vergleich "Spalte = NULL" in EXASolution immer NULL, auch wenn die Spalte einen Nullwert enthält. Daher liefern auch Joins in solchen Fällen keinen Match.
Will man einen Wert auf NULL testen, müssen anstatt des Vergleichsoperators die Prädikate IS NULL und IS NOT NULL verwendet werden.
Im Weiteren benutzen wir die nachfolgende Beispieltabelle, um die Behandlung von Nullwerten zu demonstrieren.
Erstellung der Beispieltabelle
-
- CREATE OR REPLACE TABLE testnull
- (
- num DECIMAL(18, 0),
- boo BOOLEAN,
- dat DATE,
- str VARCHAR(30)
- );
- INSERT INTO testnull VALUES
- (1, FALSE, '2010-02-03', 'first row'),
- (NULL, TRUE, '2010-02-04', 'second row'),
- (3, NULL, '2010-02-05', 'third row'),
- (4, TRUE, NULL, 'fourth row'),
- (5, FALSE, '2010-02-07', NULL),
- (6, TRUE, '2010-02-08', '');
-
- NUM BOO DAT STR
- ------------------- ----- ---------- ------------------------------
- 1 false 2010-02-03 first row
- 3 2010-02-05 third row
- 4 true fourth row
- 5 false 2010-02-07
- 6 true 2010-02-08
- true 2010-02-04 second row
Allgemeine Regeln
Es gelten folgende Grundregeln für Operationen mit Nullwerten:
- Ein Vergleich ('=') mit einem Nullwert liefert immer NULL
- Zum Testen auf Nullwerte müssen die IS (NOT) NULL Prädikate verwendet werden.
- Operationen mit einem Nullwert liefern einen Nullwert
Beispiel: Regeln
-
- SELECT
- num+1 num,
- CASE boo WHEN TRUE THEN 'TRUE'
- WHEN FALSE THEN 'FALSE'
- ELSE '-'
- END AS boo,
- CASE WHEN str IS NULL THEN '-' ELSE str END str,
- add_month(dat, 1) dat
- FROM testnull;
-
- NUM BOO STR DAT
- ----- ----- ------------------------------ ----------
- TRUE second row 2010-03-04
- 6 FALSE - 2010-03-07
- 4 - third row 2010-03-05
- 5 TRUE fourth row
- 2 FALSE first row 2010-03-03
- 7 TRUE - 2010-03-08
NULL und Zeichenketten
EXASolution macht keinen Unterschied zwischen NULL und einer leeren Zeichenkette. Für Zeichenketten gelten die gleichen Nullwertregeln wie für die anderen Datentypen, mit einer Ausnahme:
Verkettung eines Operanden mit einem Nullwert liefert kein NULL, sondern diesen Operand. Das heisst, bei einer Verkettungsoperation bekommt man NULL als Ergebnis nur, wenn beide Operanden NULL sind:
Beispiel: String und Nullwert
-
- SELECT 'str: '||str FROM testnull;
-
- A
- -----------------------------------
- str: second row
- str:
- str: third row
- str: fourth row
- str: first row
- str:
Funktionen zur Behandlung von Nullwerten
NVL (expr1, expr2)
Falls 'expr1' NULL ist, wird 'expr2' zurückgegeben, andernfalls 'expr1'. NVL steht für 'Null Value'.
Äquivalenter CASE-Ausdruck ist: CASE WHEN expr1 IS NULL THEN expr2 ELSE expr1 END
COALESCE (expr1, expr2, ...)
Liefert den ersten Wert aus der Argumentliste, der nicht NULL ist. Falls alle Werte NULL sind, so liefert die Funktion den Wert NULL.
Äquivalenter CASE-Ausdruck ist: CASE WHEN expr1 IS NOT NULL THEN expr1 WHEN expr2 IS NOT NULL THEN expr 2 ... ELSE NULL END
ZEROIFNULL(number)
Liefert den Wert 0, falls die Zahl number den Wert NULL hat. Andernfalls wird die Zahl number zurückgeliefert.
Äquivalenter CASE-Ausdruck ist:CASE WHEN number is NULL THEN 0
ELSE number END
NULLIFZERO(number)
Liefert den Wert NULL, falls die Zahl number den Wert 0 hat. Andernfalls wird die Zahl number zurückgeliefert. Diese Funktion lässt sich einsetzen, um 'division by zero' Fehler zu vermeiden (vgl. Beispiel).
Äquivalenter CASE-Ausdruck ist: CASE WHEN number=0 THEN NULL ELSE number END
DECODE(expr, val1, ret1, ..., default)
Diese Funktion ist nicht speziell zur Nullwertbehandlung gedacht, und liefert den ersten Wert retX zurück, für den expr=valX gilt. Sie nimmt aber insofern eine Sonderstellung ein, als dass das Ergebnis von expr hier auch gegen NULL verglichen werden kann.
Im äquivalenten CASE-Ausdruck dagegen muss das entsprechende NULL-Prädikat verwendet werden: CASE WHEN expr=val1 THEN ret1 WHEN expr is NULL then ... ELSE default END
Wird kein Vergleich mit NULL benötigt, könnte der Ausdruck auch so aussehen: CASE expr WHEN val1 THEN ret1 WHEN val2 THEN ... ELSE default END
Beispiel: NULL-Funktionen
-
- SELECT
- num,
- ZEROIFNULL(num) + 1 num1,
- NVL(CAST(boo AS VARCHAR(10)), 'unknown'),
- COALESCE(str, '-') str,
- 1/NULLIFZERO(num-3) num2
- FROM testnull;
-
- NUM NUM1 NVL STR NUM2
- ---- ----- ---------- ------------------------------ -----
- 1 True second row
- 5 6 False - 0.5
- 3 4 unknown third row
- 4 5 True fourth row 1
- 1 2 False first row -0.5
- 6 7 True - 0.33333
Beispiel: Anzahl von NULL-Werten berechnen
-
- SELECT COUNT(*)-COUNT(col) AS NULLCOUNT FROM tab;
