Verwendung von Vergleichsoperatoren mit NULL-Werten in SQL

Der SQL-NULL-Wert erfüllt einen besonderen Zweck. Er bringt auch kontraintuitive Verhaltensweisen mit sich, die SQL-Anfängern oder sogar erfahrenen Programmierern Schwierigkeiten bereiten können. Erfahren Sie, wie Sie diese Probleme bei der Verwendung von NULL mit Vergleichsoperatoren vermeiden können.

Dieser Artikel wird Ihnen helfen, die besten Praktiken für die Erstellung von SQL-Abfragen zu beherrschen, die mit NULL-Werten arbeiten und Vergleichsoperatoren ( =, <>, <, > ) verwenden - und wenn Sie schon einmal eine SQL-Abfrage geschrieben haben, wissen Sie, dass das so ziemlich jede Abfrage ist, die Sie jemals schreiben werden! Dieses Wissen ist von grundlegender Bedeutung, und wenn Sie es beherrschen, wird SQL für Sie einfacher werden.

Wir werden einen kurzen Überblick über SQL-NULLs geben, warum es sie gibt und wie sich ihre Seltsamkeiten auf die Abfrageergebnisse auswirken. Es folgen einige SQL-Abfragen, die diese Merkwürdigkeit demonstrieren. Dann werden wir Standardtechniken zum Schreiben von Abfragen besprechen, die korrekt mit NULLs und Vergleichsoperatoren umgehen. Zum Schluss gibt es noch eine kurze Zusammenfassung aller SQL-Vergleichsoperatoren und wie diese mit NULL interagieren.

Aber keine Sorge. Es ist nicht so schwer, wie es klingt. Beginnen wir mit der Betrachtung von NULL-Werten in SQL.

SQL NULL - Das Bekannte Unbekannte

Datenbanken sind als eine einzige Quelle der Wahrheit gedacht. Was in den Feldern für jede Zeile aufgezeichnet wird, stellt das dar, was bekannt ist.

Sehen Sie sich die folgende Tabelle an, die aus What Is a NULL in SQL (eine großartige Quelle für einen tiefen Einblick in SQL NULLs) übernommen wurde. Dies könnte ein Teil einer Datenbank sein, die von einem Simpsons-Superfan erstellt wurde.

NULL steht hier für zwei verschiedene Dinge. Waylon Smithers ist bekanntermaßen ledig, so dass NULL in dieser Zeile in der Spalte Ehepartner einen nicht existierenden Wert darstellt. Aber wir wissen so wenig über Dr. Nick, dass die NULL-Werte in seinen Spalten Ehepartner und status einen unbekannten Wert darstellen.

Um die Integrität einer Datenbank zu wahren, sind beide Interpretationen von NULL notwendig. Und um Programmierern zu helfen, diese Integrität trotz fehlender und unbekannter Werte aufrechtzuerhalten, integriert SQL NULLs in seine ternäre Logik.

Was ist ternäre Logik und wie funktioniert sie in SQL?

Die binäre Logik verwendet zwei Werte: Wahr und Falsch, 0 und 1, usw. Die ternäre Logik verwendet drei Werte. In SQL sind diese drei Werte Wahr, Falsch und Unbekannt. In der ternären Logik von SQL entspricht der Wert NULL dem Wert Unbekannt.

Im Folgenden wird erläutert, wie die drei Werte der ternären Logik mit den logischen Operatoren von SQL NOT, OR und AND funktionieren:

Das Wichtigste zum Schluss: Jede logische Operation mit NULL ergibt den Wert unknown , mit Ausnahme von TRUE OR NULL und FALSE AND NULL. Interessant, aber nichts, was Sie auswendig lernen müssen. Wie Sie sehen werden, hilft uns SQL, diese schwierige Logik zu umgehen. Das ist praktisch, denn die SQL-Vergleichsoperationen verwenden dieselbe ternäre Logik.

SQL-Vergleichsoperationen und NULL-Werte

Hier ist eine Abfrage, die Vergleiche und logische Operatoren verwendet. Raten Sie mal, wie viele Zeilen sie zurückgibt:

SELECT spouse
  FROM simpsons
 WHERE      spouse = NULL
    OR NOT (spouse = NULL) 

Beginnen wir mit der ersten Vergleichsoperation:

WHERE      spouse = NULL

Was auch immer die Vergleichsspalte enthält - Gehälter, Tiernamen usw. -, wenn wir testen, dass sie gleich NULL ist, ist das Ergebnis unbekannt. Dies gilt selbst dann, wenn der Spaltenwert NULL ist. Dies verwirrt Programmierer, die mit anderen Sprachen vertraut sind. In Python zum Beispiel scheint der Wert None ähnlich wie SQL NULL zu sein und kann mit sich selbst verglichen werden:

>>> None == None
True

Aber die erste Zeile der WHERE -Klausel wird unbekannt zurückgeben. Wenn unsere Abfrage ausgewertet wird, sieht sie also wie folgt aus:

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR NOT (spouse = NULL) 

Schauen wir uns die zweite Zeile der WHERE-Bedingung an:

   OR NOT (spouse = NULL)
	 

Dieser Vergleich wird ebenfalls unbekannt zurückgeben. Anhand der Wahrheitstabelle können wir sehen, dass NOT unknown unbekannt zurückgibt. Unsere Abfrage ist jetzt also eine:

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR unknown 
	 

Die OR-Wahrheitstabelle oben sagt uns, dass das Ergebnis unbekannt sein wird.

Eine WHERE-Klausel erfordert wahre Bedingungen. Dass das Ergebnis nicht falsch ist, reicht nicht aus. Obwohl die Abfrage so aussieht, als würde sie jede Zeile zurückgeben, führt die ternäre Logik von SQL und die Natur von SQL NULL dazu, dass null Zeilen zurückgegeben werden.

Die anderen Vergleichsoperatoren von SQL

Für diese Beispiele werden wir eine andere Tabelle verwenden, pet_owners:

NULL und der <-Operator

Verwenden wir die Tabellepet_owners , um zu sehen, wer weniger als 5 Haustiere hat.

Das Ergebnis:

Warum? Alice, die ihr Umfrageformular für Haustiere nicht ausgefüllt hat, hat NULL für ihre pet_count. Der Wert von NULL ist unbekannt. Ist NULL < 5? Das ist unbekannt, also kann Alice nicht in die Ergebnisse aufgenommen werden.

NULL und der > Operator

Jetzt werden wir sehen, wer mehr als 3 Haustiere hat.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count > 3
	 

Das Ergebnis:

Warum? Wieder ist es der unbekannte Wert von NULL. Genauso wie es unbekannt ist, ob NULL 3 < 5, it is unknown if NULL >. Alice ist von den Ergebnissen ausgeschlossen.

NULL und der <= Operator

In einer subtilen Änderung erhalten wir nun eine Liste aller Personen mit nicht mehr als 5 Haustieren.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count <= 5
	 

Das Ergebnis:

Wenn wir unsere erste Abfrage von < auf <= ändern, wird Bob in die Ergebnismenge aufgenommen, Alice jedoch nicht. In der ersten Abfrage ist die Anzahl von Bobs Haustieren (5) nicht kleiner als 5. Aber sie ist kleiner oder gleich 5, so dass er jetzt im Abfrageergebnis enthalten ist. Alice taucht immer noch nicht auf.

Wenn wir die Zeile von Alice untersuchen, können wir <= als Abkürzung für "NULL < 5 ODER NULL = 5" betrachten. Wir wissen von oben, dass "NULL < ANYTHING" ein unbekanntes Ergebnis liefert; von früher wissen wir, dass "NULL = ANYTHING" ebenfalls ein unbekanntes Ergebnis liefert. Auch hier wird Alice von den Ergebnissen ausgeschlossen.

NULL und der >= Operator

Nun wollen wir sehen, wer mindestens 3 Haustiere hat.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count >= 3
	 

Das Ergebnis:

Wie <= können wir uns >= als eine logische Kombination zweier Vergleichsoperationen vorstellen. Für Alice ist der Vergleich also gleichbedeutend mit "NULL > 3 OR NULL = 3". Sie sollten sich jetzt darüber im Klaren sein, dass dies nur zu einem Wert von unknown führen kann.

SQL-Vergleichsoperatoren, die mit NULLs arbeiten

Um NULLs korrekt zu behandeln, bietet SQL zwei spezielle Vergleichsoperatoren: IS NULL und IS NOT NULL. Sie geben nur wahr oder falsch zurück und sind die beste Methode, um NULL-Werte in Ihre Abfragen einzubeziehen.

Um das erwartete Ergebnis zu erhalten, können wir die Simpsons-Abfrage folgendermaßen umschreiben:

SELECT spouse
  FROM simpsons
 WHERE spouse IS NULL
    OR spouse IS NOT NULL
	 

Jetzt gibt die Abfrage jede Zeile zurück, wie wir es erwartet haben.

SQL NULL und Outer Joins

Dies ist ein etwas fortgeschritteneres Thema. Wenn Sie neu bei SQL-JOINs sind, sollten Sie zuerst 7 SQL JOIN Beispiele mit detaillierten Erklärungen lesen.

Es ist üblich, eine WHERE-Klausel zusammen mit dem Operator IS NOT NULL zu verwenden, um Zeilen mit NULL-Werten loszuwerden. Dies kann jedoch manchmal ein ineffizienter Weg sein, um ein Ergebnis zu erzielen. Hier ist der Grund dafür.

Äußere Verknüpfungen - LEFT, RIGHT oder FULL JOIN - kann man sich wie eine INNER JOIN vorstellen (die übereinstimmende Zeilen zurückgibt) plus nicht übereinstimmende Zeilen, deren Spalten mit NULL-Werten gefüllt sind.

Ein LEFT JOIN gibt alle Zeilen in der linken Tabelle des Joins mit den übereinstimmenden Zeilen aus der rechten Tabelle zurück (oder NULL-Werte, wenn es keine Übereinstimmung gibt). Eine RIGHT JOIN gibt alle Zeilen aus der rechten Tabelle mit den übereinstimmenden Zeilen (oder NULL-Werten) aus der linken Tabelle zurück. Eine FULL JOIN ist wie eine INNER JOIN, die auch alle nicht übereinstimmenden Zeilen der linken und rechten Tabelle, erweitert um NULLs, zurückgibt.

Wenn die WHERE Klausel Ihrer Abfrage Zeilen herausfiltert, die durch NULLs erweitert wurden, heben Sie im Grunde Ihren Outer Join auf. Sie sollten Ihre Abfrage unter Verwendung einer INNER JOIN neu schreiben.

In anderen Fällen interagieren NULLs mit Ihrer WHERE-Klausel und führen zu falschen Ergebnissen. Schauen wir uns ein Beispiel an, das dies verdeutlicht. Sie können es selbst ausführen, indem Sie dieses SQL-Fiddle verwenden (hier finden Sie einen Leitfaden mit weiteren Websites, die Sie für SQL-Übungen verwenden können).

Für dieses Beispiel werden wir zwei Tabellen verwenden, eine für customers und eine für orders.

Tabelle customers

Tabelle orders

Wir möchten den Betrag sehen, den alle unsere registrierten Kunden seit dem 4. Februar 2021 ("2021-02-04") ausgegeben haben. Wir beginnen mit einem INNER JOIN. In dieser Abfrage gibt es einige zusätzliche SQL-Funktionen, die Ihnen vielleicht neu sind, aber LearnSQL hilft Ihnen dabei. Wir haben Artikel über COALESCE, Aggregatfunktionen wie SUM und die GROUP BY-Klausel. Sie brauchen sich jetzt nicht darum zu kümmern, was diese Funktionen tun. Konzentrieren Sie sich einfach darauf, was jede Abfrage zurückgibt, und beginnen Sie mit dieser Abfrage:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
INNER JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Die Abfrage gibt dieses Ergebnis aus:

Es sieht gut aus, aber wir haben 3 Kunden. Wenn wir jeden Kunden sehen wollen, müssen wir eine LEFT OUTER JOIN (kurz LEFT JOIN ) verwenden. Sie schließt jede Zeile in der linken Tabelle der FROM -Anweisung ein, auch wenn es keine passenden Daten in der rechten Tabelle gibt. So erhalten wir unsere nächste Abfrage:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Die Ergebnisse könnten Sie überraschen:

Warum ist das so? Warum ist Alice immer noch nicht da? Eine einfachere Abfrage wird uns einen Hinweis geben:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Das Ergebnis:

Wie bereits erwähnt, enthält LEFT JOIN alle Zeilen aus der linken Tabelle. Wo es keine passende Zeile in der rechten Tabelle gibt, werden die Spalten mit NULLen gefüllt.

Die WHERE-Klausel führt ihre Filterung nach JOIN durch, so dass alle Zeilen für Alice entfernt werden, da der Vergleich mit NULL, z. B. einer nicht existierenden order_date, ein unbekanntes Ergebnis liefert . WHERE gibt nur eine Zeile zurück, bei der die Bedingungen auf TRUE ausgewertet werden.

Dies lässt sich beheben, indem man den bedingten Ausdruck, hier o.order_date > '2021-02-04', in die JOIN verschiebt, indem man ihn in die Klausel ON einfügt:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Da wir eine LEFT JOIN verwenden, bleibt Alice trotz der zusätzlichen Datumsbedingung in der Ergebnismenge. Ihre NULL wird durch die Funktion COALESCE() in eine saubere "0" umgewandelt. Das Abfrageergebnis ist nun das, was wir erwartet haben:

Erfahren Sie mehr über SQL-NULLs

Sie sollten nun verstehen, wie SQL NULL-Werte behandelt und wie man am besten mit ihnen arbeitet. Sie kennen die Operatoren IS NULL und IS NOT NULL und wissen, dass die ternäre Logik von SQL immer Unbekannt zurückgibt, wenn etwas mit einem NULL-Wert verglichen wird , mit Ausnahme von zwei Sonderfällen. Sie haben auch gesehen, wie Sie Abfragen umschreiben können, damit Sie diese lästigen NULLs nicht herausfiltern müssen.

Aber es gibt noch mehr über die Arbeit mit NULLs in SQL zu wissen. Ich schlage vor, dass Sie mit den Artikeln Wie ORDER BY und NULL in SQL zusammenarbeiten und NULL-Werte und die GROUP BY-Klausel weiter lernen.

Und wenn Sie SQL wirklich beherrschen wollen, empfehle ich Ihnen den SQL-Übungssatz oder den SQL-Praxis Kurs. Sie bieten eine gründliche und leicht nachvollziehbare Lernerfahrung, die Ihnen helfen wird, Ihr Handwerk zu verfeinern.