Eine Abfrage wird fehlerfrei ausgeführt und gibt eine sinnvoll aussehende Zahl zurück, es fehlen jedoch Zeilen und der Durchschnitt ist falsch. SQL-NULL-Werte sind sehr oft die Ursache. NULL verhält sich nicht wie ein normaler Wert, und das fällt sowohl Anfängern als auch erfahrenen Entwicklern auf.

Diese Anleitung zeigt genau, wie sich NULL verhält. Wir haben jede Abfrage in SQLite 3.50 ausgeführt und die angezeigten Ergebnisse stammen aus diesem Lauf. Der größte Teil dieses Verhaltens ist auf Standard-SQL zurückzuführen, und wir stellen fest, wo sich Datenbanken unterscheiden, z. B. in der NULL-Sortierreihenfolge und in der Behandlung leerer Zeichenfolgen durch Oracle.

Was ist NULL?

NULL ist ein Marker, der bedeutet, dass ein Wert fehlt oder unbekannt ist. Es handelt sich nicht um einen Wert im üblichen Sinne, das sind also alles verschiedene Dinge:

Der Unterschied ist insbesondere bei Beträgen von Bedeutung. Ein Bonus von 0 bedeutet, dass die Person nichts erhalten hat. Ein NULL-Bonus bedeutet, dass es noch niemand weiß.

Die Beispieltabelle

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Beachten Sie, dass Sara eine leere Zeichenfolge für das Telefon hat, während Arjun und Ravi NULL haben. Die beiden werden sehr unterschiedlich behandelt.

Testen auf NULL

Hier ist der Fehler, den fast jeder zuerst macht:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

Die erste Abfrage ist gültiges SQL und gibt keinen Fehler aus. Es wird einfach nichts zurückgegeben, da ein Vergleich mit NULL niemals wahr ist. Sara erscheint unter IS NOT NULL, da eine leere Zeichenfolge ein echter Wert ist. In Oracle wird eine leere Zeichenfolge jedoch als NULL behandelt, was eine bemerkenswerte Ausnahme darstellt.

Dreiwertige Logik

In den meisten Programmiersprachen ist eine Bedingung wahr oder falsch. SQL fügt ein drittes Ergebnis hinzu: unbekannt. Wenn Sie einen Wert nicht kennen, können Sie nicht sagen, ob er gleich 5 ist. Und es ist nicht bekannt, dass zwei unbekannte Werte gleich sind, weshalb NULL = NULL nicht wahr ist. In unserem Test gaben alle diese Werte NULL zurück:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

Das Kombinieren von Bedingungen folgt sorgfältigen Regeln. Hier sind die Ergebnisse unseres Laufs, wobei 1 wahr und 0 falsch ist:

Zeilen, die von WHERE verschwinden

WHERE behält eine Zeile nur, wenn ihre Bedingung wahr ist. Zeilen, deren Ergebnis unbekannt ist, werden gelöscht, zusammen mit Zeilen, die falsch sind. Dies führt zu überraschenden Ergebnissen:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Man könnte erwarten, dass Arjun und Ravi auftauchen, da ihre Telefonnummer sicherlich nicht diese Nummer ist. Aber ihre Telefonnummer ist unbekannt, also ist das Ergebnis unbekannt und sie verschwinden. Um sie einzubeziehen, sagen Sie es ausdrücklich: WHERE phone <> '555-0101' OR phone IS NULL. Negation hilft auch nicht:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara und Ravi haben keinen Bonus, daher erscheinen sie weder in bonus > 400 noch im Gegenteil. Dies ist ein wichtiger Punkt, den Sie im Hinterkopf behalten sollten: Eine Bedingung und ihre Negierung decken nicht immer jede Zeile ab.

COALESCE: einen Rückfall bieten

COALESCE gibt den ersten Nicht-NULL-Wert in seiner Liste zurück und funktioniert in allen wichtigen Datenbanken:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara erscheint immer noch leer, da eine leere Zeichenfolge nicht NULL ist. Es gibt ähnliche Funktionen, etwa IFNULL in MySQL und SQLite, ISNULL in SQL Server und NVL in Oracle. COALESCE ist die tragbare Wahl. Auch hier gilt unser früherer Hinweis zu Berechnungen. bonus + 100 gibt NULL für jeden ohne Bonus, also schreiben Sie COALESCE(bonus, 0) + 100, wenn es für Ihr Unternehmen korrekt ist, einen fehlenden Bonus als Null zu behandeln.

NULLIF: Wandelt einen Wert in NULL um

NULLIF(a, b) gibt NULL zurück, wenn die beiden Werte gleich sind, andernfalls wird a zurückgegeben. Zwei Anwendungen sind besonders hilfreich. Es kann Platzhalterwerte bereinigen:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

Diese zweite Abfrage ist eine praktische Möglichkeit, Werte zu finden, die „fehlen oder leer“ sind. Es kann auch vor einer Division durch Null schützen, die in vielen Datenbanken einen Fehler auslöst:

SELECT total / NULLIF(quantity, 0) FROM sales;

Wenn quantity 0 ist, ergibt dies NULL und schlägt nicht fehl. In unserem Test hat SELECT 100 / NULLIF(0, 0) NULL zurückgegeben.

Aggregate überspringen NULL

Hier richtet NULL bei Berichten den größten Schaden an:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Wie hoch ist also der durchschnittliche Bonus, 400 oder 200? Es hängt davon ab, was NULL in Ihren Daten bedeutet. Wenn NULL bedeutet „es wurde kein Bonus ausgezahlt“, dann ist 200 richtig. Wenn es „noch nicht entschieden“ bedeutet, dann könnte 400 richtig sein. Die Datenbank kann Ihnen diese Entscheidung nicht abnehmen, daher muss die Frage an jemanden gestellt werden, der sich mit dem Geschäft auskennt.

Eine verwandte Falle: SUM ohne Werte ist NULL statt 0. Wenn wir nur Zeilen ohne Bonus summierten, erhielten wir NULL, während COUNT 0 zurückgab. Anwendungscode, der eine Zahl erwartet, kann dabei abstürzen, also schließen Sie ihn ein: COALESCE(SUM(bonus), 0).

GROUP BY, Sortieren und DISTINCT

Obwohl NULL-Werte bei Vergleichen „ungleich“ sind, werden sie von diesen Operationen als eine Gruppe behandelt:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Weitere Informationen zum Gruppieren finden Sie unter SQL GROUP BY und HAVING. In der Sortierreihenfolge unterscheiden sich Datenbanken wirklich. SQLite und MySQL behandeln NULL als den kleinsten Wert, während PostgreSQL und Oracle ihn als den größten behandeln. Dasselbe ORDER BY bonus DESC kann also NULL-Werte in einem an letzter Stelle und in einem anderen an erster Stelle setzen. Mit PostgreSQL, Oracle und modernem SQLite können Sie dies direkt angeben, indem Sie NULLS FIRST oder NULLS LAST verwenden.

Tritt bei

Eine Join-Bedingung mit = stimmt nie mit NULL überein, daher fallen Zeilen mit einem NULL-Schlüssel aus einem inneren Join heraus:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera hat keinen Manager, daher löscht ein Inner Join diese Zeile stillschweigend. A LEFT JOIN behält es und erstellt auch eigene NULL-Werte für die fehlende Seite. Weitere Informationen finden Sie unter Erklärte SQL-Joins.

Die NICHT IN-Falle

Ein einzelner NULL-Wert in einer NOT IN-Liste löscht jedes Ergebnis:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

Die zweite Form bedeutet manager_id <> 1 AND manager_id <> NULL, und dieser letzte Teil kann niemals wahr sein. Dies ist besonders gefährlich bei Unterabfragen, bei denen ein NULL-Wert unbemerkt eintreffen kann. Verwenden Sie stattdessen NOT EXISTS, wie in unserem Tutorial zu SQL-Unterabfragen erläutert. Meera fehlt auch im ersten Ergebnis, da manager_id dieser Zeile NULL ist.

Entwerfen mit NULL

• Verwenden Sie standardmäßig NOT NULL. Erlauben Sie NULL nur, wenn ein Wert wirklich fehlen kann.
• Fügen Sie sinnvolle Standardeinstellungen hinzu. Zum Beispiel quantity INT NOT NULL DEFAULT 0.
• Verwenden Sie keine magischen Werte. Ein Datum von 1900-01-01 oder ein Alter von -1 anstelle von „unbekannt“ wird Ihre Berechnungen verfälschen.
• Kennen Sie das EINZIGARTIGE Verhalten. Die meisten Datenbanken erlauben mehrere NULL-Werte in einer Spalte UNIQUE und unser Test hat zwei ohne Fehler eingefügt. Eine Ausnahme bildet SQL Server, da er dies normalerweise zulässt.
• Denken Sie daran, dass ein Primärschlüssel nicht NULL sein kann. Siehe Primärschlüssel vs. Fremdschlüssel.
• Testen mit NULL-Werten. Testdaten ohne NULL-Werte verbergen diese Fehler. Fügen Sie sie absichtlich ein, wie Sie es auch in anderen Randfällen tun würden.

Fazit

Behandeln Sie NULL als „unbekannt“, und die meisten Verhaltensweisen beginnen einen Sinn zu ergeben: unbekannt plus eins ist unbekannt, und unbekannt ist gleich unbekannt, und es ist nicht bekannt, dass es wahr ist. Prüfen Sie diese Woche einen Ihrer eigenen Berichte. Überprüfen Sie nullfähige Spalten mithilfe von COUNT(*) gegenüber COUNT(column), suchen Sie nach NOT IN und entscheiden Sie, was NULL in jedem Durchschnitt bedeuten soll.

Häufige Fragen

Lerne weiter