11. Januar 2008

SQL: "NULL = NULL" oder "NULL is NULL" oder was ...?

English title: NULL = NULL or NULL IS NULL or what ...?

Das Verhalten von SQL NULL (nicht nur) in der Oracle-Datenbank führt immer wieder zu Erstaunen und zu Überraschungen ... und dabei ist eigentlich alles ganz einfach. Im folgenden daher ein kurzer Abriß über das Verhalten SQL NULL und wie man damit umgeht.
Zunächst ist NULL definiert als der "unbekannte" (und nicht der "fehlende") Wert - NULL bedeutet also soviel wie "weiss nicht". So weit - so gut. Das Besondere ist nun, dass Oracle bei Vergleichen mit "dreiwertiger" Logik arbeitet - das Ergebnis eines Vergleichs kann also nicht nur wahr (true) oder falsch (false) sein, sondern auch weiss nicht, also NULL. Am besten nähert man sich dem mit einer Tabelle:
VergleichErgebnis
1 = 0false
1 = 1true
1 = NULLNULL
NULL = NULLNULL
Die ersten beiden Zeilen sind klar; interessant ist die dritte. Der Vergleich 1 = NULL meint übersetzt soviel wie "Ist 1 gleich dem unbekannten Wert?" Die Antwort kann nur sein: "Weiss nicht", also NULL. Dasselbe gilt für den Vergleich NULL = NULL; das meint soviel wie "Ist ein unbekannter Wert gleich einem anderen unbekannten Wert?" Antwort wiederum: "Weiss nicht", also NULL. Achtung: Es meint nicht: "Gleicht der "fehlende" Wert dem "fehlenden" Wert?" - die Antwort darauf wäre: wahr. Aber NULLrepräsentiert eben den unbekannten, nicht den fehlenden Wert.
Das ist übrigens bei Negationen nicht anders:
VergleichErgebnis
1 != 0true
1 != 1false
1 != NULLNULL
NULL != NULLNULL
Der Aussagewert der Unbekannten ändert sich nicht, auch wenn man die Frage negiert. So meint 1 != NULL soviel wie "Ist 1 ungleich der Unbekannten?. Die Antwort lautet wieder mal "weiss nicht", könnte ja auch gleich sein. Nun, die meisten Entwickler wissen, dass man NULL-Werte nicht mit dem Gleichheitszeichen (=), sondern mit IS NULL abfragen muss. Wir erweitern die Tabelle ...
VergleichErgebnis
1 IS NULLfalse
NULL IS NULLtrue
Der erste Vergleich meint übersetzt soviel wie "Ist 1 ein unbekannter Wert?" - das kann man klar beantworten: "1 ist bekannt", also "nein". Der Vergleich liefert also false zurück. Die nächste Zeile fragt, ob "der unbekannte Wert unbekannt ist" - klaro: Das ist wahr; als Ergebnis kommt also true heraus.
Was bedeutet das also für den Entwickler?
  1. Vergleiche mit NULL niemals mit dem Gleichheitszeichen (=), sondern immer mit IS NULL durchführen!
  2. Wenn mit dem Gleichheitszeichen gearbeitet wird und eine Variable hat den Wert NULL, kommt (wie oben gesehen) auch NULL als Ergebnis heraus. Das ist weder wahr noch falsch. In einem PL/SQL if-then-else-Block landet man daher immer im else-Zweig.
So, das ist es eigentlich auch schon: Nur eines noch (ein Punkt bleibt immer): Seit Urzeiten setzt die Oracle-Datenbank den Leerstring ('') mit NULL gleich. Das ist nun vom Konzept her nicht so ganz sauber, denn der Leerstring ist keine Unbekannte, sondern klar definiert eben als Leerstring. Aber hier kann man nur sagen: isso - und da tausende Kunden mit abertausend Anwendungen sich u.U. genau hierauf verlassen, wird sich daran wohl auch so schnell nix ändern!
The behaviour of SQL NULL (not only) in the Oracle database leads to frequent questions and sometimes irritations ... but basically it's very easy. This post therefore contains a brief overview of SQL NULL and how to deal with it.
NULL is defined as the unknown value (and not as the "missing value") - NULL therefore means as much as "don't know". The trick is now that databases (and so Oracle) works with the "three-value-logic" - the result of an expression can be not only true or false but also "don't know (NULL). The best approach to understand this behaviour is to create a value table:
ComparisonResult
1 = 0false
1 = 1true
1 = NULLNULL
NULL = NULLNULL
The first two lines need no explanation; focus is on the following ones. The comparison 1 = NULL translated into "human language" means as much as "Equals '1' the unknown value?" The answer can only be: "don't know", say NULL. The same applies to the comparison NULL = NULL; this can be translated to"Equals on unknown value another unknown value?" Answer again: "don't know" (NULL).
Heads Up: It does not mean "Does missing value equal missing value? - Such a question would be answered with true; but NULL represents the unknown value. This also applies for negations:
ComparisonResult
1 != 0true
1 != 1false
1 != NULLNULL
NULL != NULLNULL
The negation of unkown is still unknown. 1 != NULL does therefore translate to "Does '1' not equal the unknown value?. The answer is again "don't know".
Most developers know that one cannot use the equal-sign (=) in a comparison with NULL - IS NULL has to be used. So we extend our table ...
ComparisonResult
1 IS NULLfalse
NULL IS NULLtrue
The first line translates to "Is '1' an unknown value?" - this leads to a clear answer: "no". The result is therefore false. The next line asks whether "the unknown value unknown is" - this is clear: true.
What does this mean to the developer?
  1. Never compare NULL values using the equal-sign (=); always use IS NULL!
  2. When equal-comparisons are performed and one value is NULL then the result is always NULL (as seen above) - neither true nor false. In a PL/SQL if-then-else-Block this always evaluates in the else branch.
So ... that's it! There is (as always) only one weak point: The oracle database sets the empty string ('') equal to NULL (since the annals). Following the concept strictly this is not correct: The empty string is a known value: empty. Here we can only say: That's life! - and since thousands of customers with again thousands of applications rely on this: this is not likely to change.

Kommentare:

Patrick Wolf hat gesagt…

Carsten, super erklärt!

Patrick

mfischer hat gesagt…

Tolle Erklärung, schön dargestellt.

Fabian hat gesagt…

Danke für den Artikel.
Da ich zwei Variablen vergleichen wollte, die evtl. null sein könnten habe ich diesen Weg genommen, welche bei mir einwandfrei funktioniert.

DECLARE @A varchar(10);
DECLARE @B varchar(10);
SET @A = null;
SET @B = null;

IF(isnull(@A, 0) = isnull(@B, 0))
PRINT 'gleich'

Gruß Fabian

Carsten Czarski hat gesagt…

Hallo Fabian,

das ist SQL Server, oder ...?

Grüße

-Carsten

Lacko hat gesagt…

Die Erklärung finde ich auch super.

Allerdings muss ich meine Skepsis bzgl. Fabians Vergleich anmelden:
IF(isnull(@A, 0) = isnull(@B, 0))
PRINT 'gleich'

Wenn Diese Funktion isnull() True oder False abliefert, dann können A und B unterschiedliche Werte, die aber nicht NULL sind, haben, und der Vergleich liefert für False=False => True !

Anonym hat gesagt…

Böse auf die Nase fallen kann man mit "ungleich"-Vergleichen gegen Null Spalten:

SELECT * FROM kunden WHERE name <> 'testkunde'

Steht in der Spalte "name" ein Null Wert, so ist der Vergleich falsch, denn:

null <> 'testkunde' = unkown/falsch

...und prompt übersieht man alle Kunden ohne Namen...

Beliebte Postings