NULL in SQL ist seltsam

• In SQL werden NULL-Werte auf besondere Weise behandelt. Eine Spalte mit einer UNIQUE-Einschränkung kann mehrere NULL-Werte enthalten.

  • Das liegt daran, dass jeder NULL-Wert als ein eigenständiger Wert betrachtet wird, der sich von jedem anderen NULL unterscheidet.
  • SQLite, Postgres und MySQL verhalten sich dabei alle gleich.

• Einen Maßstab festlegen

  select '' = '';    -- Returns 1 (true) leere Zeichenketten sind gleich   
  select 1 = 1;      -- Returns 1 (true) Zahlen sind gleich   
  select 1 = 0;      -- Returns 0 (false) Zahlen sind verschieden   
  select null = null; -- Returns NULL (null) Hä?  
  

  • Das liegt daran, dass NULL ein Platzhalter für einen „unbekannten Wert“ ist und deshalb zwei unbekannte Werte nicht als gleich betrachtet werden.
  • Mit dem IS-Operator kann man die Identität von NULL prüfen. Zum Beispiel gibt null is null TRUE zurück.

• Zur Eindeutigkeit

  • Wenn eine Spalte mit einer UNIQUE-Einschränkung NULL-Werte enthält, werden diese NULL-Werte als verschieden behandelt und verletzen die Eindeutigkeitsbedingung daher nicht.
  • Zum Beispiel werden ('[e-mail]', NULL) und ('[e-mail]', NULL) als unterschiedliche Zeilen betrachtet.

• Warum NULL so behandelt wird

  • SQLite und andere SQL-kompatible Datenbanken sind so implementiert, um NULL konsistent mit anderen Datenbanken zu behandeln. Das SQL-Standarddokument legt nahe, dass NULL überall eindeutig sein sollte, aber in der Praxis behandeln die meisten SQL-Engines NULL bei SELECT DISTINCT oder UNION nicht als eindeutig.

• Wie sich Eindeutigkeit sicherstellen lässt

  • Generierte Spalten verwenden

    • Man kann das Problem abmildern, indem man eine Spalte erzeugt, die immer einen nicht-NULL-deterministischen Wert hat. Zum Beispiel kann man mit COALESCE(deleted_at, '1970-01-01') NULL-Werte ersetzen.
    • Diese Methode kann zusätzlichen Platz belegen, weil sie der Tabelle ein weiteres Feld hinzufügt.

  • Partielle Indizes verwenden

    • Man kann einen partiellen Index auf email erzeugen, der nur gilt, wenn deleted_at NULL ist, um Eindeutigkeit sicherzustellen.
    • Partielle Indizes verbreitern die Tabelle nicht, benötigen weniger Speicherplatz und verursachen keine Fehler, wenn dasselbe Datensatzpaar wiederholt gelöscht wird.

• Update

  • Oracle behandelt leere Zeichenketten als NULL.

• Fazit

  • Bei der Verwendung eines ORM fällt es nicht auf, aber die besondere Behandlung von SQL-NULL kann zu Verwirrung führen. Das SQL-Standarddokument ist nicht frei verfügbar und nur gegen Bezahlung erhältlich.

[iolothebard]

Alle null-Werte sind seltsam.
Deshalb wirkt das völlig normale null in SQL eher so, als wäre es seltsam …
Im Land der Einäugigen gilt der mit zwei Augen als unnormal …

[GN⁺]

Hacker-News-Kommentare

• Das NULL in SQL basiert auf der TRUE-FALSE-UNKNOWN-Logik von Kleene. Wenn man NULL als UNKNOWN liest, werden viele Operationen intuitiver verständlich

  • TRUE OR UNKNOWN = TRUE, TRUE AND UNKNOWN = UNKNOWN, UNKNOWN XOR UNKNOWN = UNKNOWN usw.
  • NULL ist ein Platzhalter, der UNKNOWN darstellt, und man kann nicht sagen, dass zwei NULL gleich sind
  • Seit PostgreSQL 15 kann man mit NULLS NOT DISTINCT eindeutige Indizes erzeugen

• Als das NULL-Konzept in den 1970er-Jahren eingeführt wurde, dachte man, es würde in Zukunft viel Verwirrung stiften. Auch nach 45 Jahren wird noch immer darüber diskutiert

• Intuitives Verständnis von NULL: Der NULL-Wert in einer bestimmten Tabellenzelle ist eine Methode, „kein Wert“ auszudrücken. Wenn man eindeutige Werte möchte, sollte man Fälle ohne Wert nicht berücksichtigen

• Skepsis gegenüber der Nutzung von ORMs: ORMs sind praktisch, aber es ist eine Generation entstanden, die nicht gelernt hat, wie relationale Datenbanken tatsächlich funktionieren. Das Verhalten von SQL-NULL stimmt mit der grundlegenden relationalen Algebra überein, problematisch ist eher NULL im C-Stil

• Es erinnert an den Humor rund um NULL-Vergleiche in einem Dialog aus einer Blackadder-Episode

• In Oracle wirkt es seltsam, dass NULL mit einer leeren Zeichenkette gleichgesetzt wird

• Im objektorientierten Kontext ist "null" nützlich, um auszudrücken, dass für eine bestimmte Eigenschaft kein Wert vorhanden ist. In JavaScript gibt es null und undefined; man kann annehmen, dass undefined bedeutet, den Wert nicht zu kennen, während null bedeutet, dass kein Wert vorhanden ist

• NULL ist nicht seltsam im Sinne von Duplikatfreiheit. Weil NULL einander nicht gleich sind, können sie keine Duplikate sein. Wenn einem die NULL-Semantik nicht gefällt, kann man Sentinel-Werte verwenden

• SQL-NULL ist nicht seltsam, wenn man bedenkt, wie relationale Logik bei Datensätzen mit nicht existierenden Werten funktionieren soll