Thomas Wiedmann https://twiedmann.de
• Sie befinden sich hier: Startseite > SQL-Backstube > NULL or NOT NULL

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

25.07.2010: NULL oder NOT NULL

Einleitung

NULL oder NOT NULL ist nicht nur eine "feine" Anlehnung an das ähnliche Zitat von Shakespeare's "To be, or not to be", sondern auch eine wesentliche Eigenschaft von Tabellenspalten einer relationalen Datenbank. Speziell im deutschen Sprachraum wird NULL leider gerne mit der Ziffer Null übersetzt (oder verwechselt), was aber ziemlich am Thema vorbei geht. NULL steht in der Datenbankwelt für einen undefinierten Wert. Aber schauen wir uns anhand dieses schönen Beispiels an, was für Auswirkungen NULL oder NOT NULL haben können.

Problemstellung

Wir haben eine schöne Kundentabelle und möchten einen Serienbrief daraus erstellen. Wie üblich, soll der Kunden dabei mit einer individuellen Anrede angesprochen werden. Die Daten kommen aus verschiedenen Quellen und deshalb haben wir uns entschieden, auf das NOT NULL Constraint bei einigen Tabellenspalten zu verzichen, damit die Datensätze trotzdem eingefügt werden können, falls ein Wert nicht vorhanden ist.

Getestet mit folgenden Datenbanken:

* IBM DB2 9.1
* MySQL 5.1.30
* ORACLE 10g 10.2 EE

Datenbank-Tabellen

Die Datenbank-Tabellen hierfür sehen wie folgt aus:

Listing 1: (IBM DB2, MySQL)

  1. /**
  2. * Adressen
  3. */
  4. CREATE TABLE adressen_and_nulls (
  5. id INT NOT NULL,
  6. geschlecht INT NOT NULL, -- 1=männlich, 2=weiblich
  7. titel VARCHAR(30),
  8. name VARCHAR(30),
  9. vorname VARCHAR(30),
  10. PRIMARY KEY (id)
  11. );

Listing 2: (ORACLE)

  1. /**
  2. * Adressen
  3. */
  4. CREATE TABLE adressen_and_nulls (
  5. id INT NOT NULL,
  6. geschlecht INT NOT NULL, -- 1=männlich, 2=weiblich
  7. titel VARCHAR2(30),
  8. name VARCHAR2(30),
  9. vorname VARCHAR2(30),
  10. PRIMARY KEY (id)
  11. );

Testdaten

Anbei die Testdaten für unseren Serienbrief. Da ORACLE derzeit keinen Multi-Row-Insert unterstützt, füllen wir die Tabelle mit mehreren einzelnen INSERT Befehlen.

Listing 3:

  1. /**
  2. * Einfügen der Adressen
  3. */
  4. INSERT INTO adressen_and_nulls VALUES ( 1, 1,NULL, 'Name-1', 'Vorname-1' );
  5. INSERT INTO adressen_and_nulls VALUES ( 2, 2,'Dr. med.', 'Name-2', 'Vorname-2' );
  6. INSERT INTO adressen_and_nulls VALUES ( 3, 1,'Prof.', 'Name-3', NULL );
  7. INSERT INTO adressen_and_nulls VALUES ( 4, 2, NULL, 'Name-4', 'Vorname-4' );
  8. INSERT INTO adressen_and_nulls VALUES ( 5, 1, 'Dipl.Ing.', NULL , 'Vorname-5' );
  10. /**
  11. * So sehen die Daten nun aus
  12. */
  13. SELECT * FROM adressen_and_nulls
  14. ORDER BY id;
  15. +----+------------+-----------+--------+-----------+
  16. | id | geschlecht | titel | name | vorname |
  17. +----+------------+-----------+--------+-----------+
  18. | 1 | 1 | NULL | Name-1 | Vorname-1 |
  19. | 2 | 2 | Dr. med. | Name-2 | Vorname-2 |
  20. | 3 | 1 | Prof. | Name-3 | NULL |
  21. | 4 | 2 | NULL | Name-4 | Vorname-4 |
  22. | 5 | 1 | Dipl.Ing. | NULL | Vorname-5 |
  23. +----+------------+-----------+--------+-----------+
  24. 5 rows in set (0.03 sec)

SQL - Abfrage

Nun machen wir uns daran die Anreden mit Hilfe von SQL zu generieren.

Listing 4: (ORACLE, IBM DB2)

  1. /**
  2. * Anrede erzeugen
  3. */
  4. SELECT id,
  5. CASE geschlecht
  6. WHEN 1 THEN 'Sehr geehrter Herr ' || titel || ' ' || name || ', '
  7. WHEN 2 THEN 'Sehr geehrte Frau ' || titel || ' ' || name || ', '
  8. END AS anrede
  9. FROM adressen_and_nulls
  10. ORDER BY id;

Listing 5: (MySQL)

  1. /**
  2. * Anrede erzeugen
  3. */
  4. SELECT id,
  5. CASE geschlecht
  6. WHEN 1 THEN CONCAT('Sehr geehrter Herr ', titel, ' ', name, ', ' )
  7. WHEN 2 THEN CONCAT('Sehr geehrte Frau ', titel, ' ', name, ', ' )
  8. END AS anrede
  9. FROM adressen_and_nulls
  10. ORDER BY id;

Die SQL-Abfragen aus Listing 4 bzw. Listing 5 erzeugen überraschenderweise nur bei der ID 2 und 3 eine Anredezeile. Warum? Mit Hilfe des || Operators ( bzw. CONCAT bei MySQL ) werden gültige Werte mit NULL ("..ich weiß nicht was es ist..") Werten aneinander gehängt. Das Ergebnis kann dann nicht verarbeitet werden und ist dann ebenfalls undefiniert also NULL. Diese Reaktion der Datenbank ist überraschend, aber vollkommen korrekt!.

Listing 6:

  1. +----+--------------------------------------+
  2. | id | anrede |
  3. +----+--------------------------------------+
  4. | 1 | NULL |
  5. | 2 | Sehr geehrte Frau Dr. med. Name-2, |
  6. | 3 | Sehr geehrter Herr Prof. Name-3, |
  7. | 4 | NULL |
  8. | 5 | NULL |
  9. +----+--------------------------------------+
  10. 5 rows in set (0.01 sec)

Lösung(en)

Mit dem IS NOT NULL sicherstellen, dass nur vollständige Adressen verarbeitet werden.

Listing 7:

  1. [...]
  2. FROM adressen_and_nulls
  3. WHERE titel IS NOT NULL
  4. AND name IS NOT NULL
  5. ORDER BY id;

Oder mit einer Konvertierung wie COALESCE() aus NULL einen gültigen Wert wie beispielweise '' machen.

Listing 8: (IBM-DB2, ORACLE)

  1. SELECT id,
  2. CASE geschlecht
  3. WHEN 1 THEN 'Sehr geehrter Herr ' || COALESCE(titel,'') || ' ' || COALESCE(name,'') || ', '
  4. WHEN 2 THEN 'Sehr geehrte Frau ' || COALESCE(titel,'') || ' ' || COALESCE(name,'') || ', '
  5. END AS anrede
  6. FROM adressen_and_nulls
  7. ORDER BY id;

Listing 9: (MySQL)

  1. SELECT id,
  2. CASE geschlecht
  3. WHEN 1 THEN CONCAT('Sehr geehrter Herr ', COALESCE(titel,''), ' ', COALESCE(name,''), ', ' )
  4. WHEN 2 THEN CONCAT('Sehr geehrte Frau ', COALESCE(titel,''), ' ', COALESCE(name,''), ', ' )
  5. END AS anrede
  6. FROM adressen_and_nulls
  7. ORDER BY id;

Listing 10: (Ausgabe von Listing 8: bzw. 9:)

  1. +----+--------------------------------------+
  2. | id | anrede |
  3. +----+--------------------------------------+
  4. | 1 | Sehr geehrter Herr Name-1, |
  5. | 2 | Sehr geehrte Frau Dr. med. Name-2, |
  6. | 3 | Sehr geehrter Herr Prof. Name-3, |
  7. | 4 | Sehr geehrte Frau Name-4, |
  8. | 5 | Sehr geehrter Herr Dipl.Ing. , |
  9. +----+--------------------------------------+
  10. 5 rows in set (0.00 sec)

Dies ist immerhin schon mal eine Teillösung, aber trotzdem werden nicht immer sinnvolle Anredezeilen erzeugt. Siehe Id = 5 ("Sehr geehrter Herr Dipl.Ing. , "). Ohne Namen ist die Anrede nicht sehr sinnvoll. Lesen wir also nur Datensätz mit einem Namen der nicht NULL ist.

Listing 11:

  1. [...]
  2. FROM adressen_and_nulls
  3. WHERE name IS NOT NULL
  4. ORDER BY id;

Listing 12: (Ausgabe von Listing 11:)

  1. +----+--------------------------------------+
  2. | id | anrede |
  3. +----+--------------------------------------+
  4. | 1 | Sehr geehrter Herr Name-1, |
  5. | 2 | Sehr geehrte Frau Dr. med. Name-2, |
  6. | 3 | Sehr geehrter Herr Prof. Name-3, |
  7. | 4 | Sehr geehrte Frau Name-4, |
  8. +----+--------------------------------------+
  9. 4 rows in set (0.00 sec)

Sieht schon besser aus, obwohl an einigen Stellen noch zu viele Leerzeichen enthalten sind. Diese müssen eigentlich auch noch entfernt werden. Dann wird die SQL-Abfrage allerdings ziemlich komplex.

Resumee:

Zwei Dinge fallen auf. Zum einen ist es gar nicht so einfach, korrekte Anreden zu generieren, wenn die Daten nicht eine gewisse Qualität erreichen, zum anderen ist es von Vorteil, diese Datenqualität mit dem NOT NULL Constraint zu erzwingen. Dann ist zwar das Zusammenstellen (einfügen in die Tabelle) der Daten schwieriger, das Ergebnis - unsere generierten Anredezeilen - ist aber deutlich einfacher zu erreichen und qualitativ hochwertiger.


https://twiedmann.de


Sitemap - Inhaltsverzeichnis

© 2002-2024 by Thomas Wiedmann : (Stand : 11.01.2015).