Null in SQL analysieren

NULL steht in der Welt der Computer und Programmierung für unbekannt und unsicher. Obwohl die chinesische Übersetzung „leer“ ist, ist diese Leere (null) nicht so leer (leer). Null stellt einen unbekannten Zustand dar, einen zukünftigen Zustand. Ich weiß zum Beispiel nicht, wie viel Geld Xiao Ming in seiner Tasche hat, aber ich kann nicht sicher sein, dass es 0 ist. Derzeit wird Null in Computern zur Darstellung verwendet unbekannt und unsicher.

Obwohl Leute, die sich mit SQL auskennen, keine Zweifel an Null haben werden, ist es immer noch schwierig, einen Artikel zu finden, der alles umfassend zusammenfasst. Ich habe eine englische Version gesehen und fühle mich ziemlich gut .

Tony Hoare erfand das Null-Zitat im Jahr 1965 und hielt es für einen „Milliarden-Dollar-Fehler“, den er begangen hatte. Auch heute noch, 50 Jahre später, sind in SQL Nullwerte verantwortlich viele häufige Fehler.

Werfen wir einen Blick auf einige der schockierendsten Fälle.

Null unterstützt keine Größen-/Gleichheitsbeurteilung

Bei den folgenden beiden Abfragen sind die zurückgegebenen Datensätze unabhängig von der Anzahl der Datensätze in der Benutzertabelle 0 Zeilen:

select * from users where deleted_at = null;
 
– result: 0 rows
 
select * from users where deleted_at != null;
 
– result: 0 rows

Wie könnte das sein? Alles nur, weil null einen „unbekannten“ Typ darstellt. Das heißt, es macht keinen Sinn, Null mit den normalen bedingten Operatoren mit anderen Werten zu vergleichen. Null ist nicht gleich Null (ungefähres Verständnis: Ein unbekannter Wert kann nicht gleich einem unbekannten Wert sein, und die Beziehung zwischen den beiden ist ebenfalls unbekannt, sonst werden Mathematik und Logik durcheinander gebracht). 　–

Hinweis

: Das folgende SQL ist für MySQL geeignet. Wenn es sich um Oracle handelt, müssen Sie... von dual; hinzufügen

Der richtige Weg, einen Wert mit Null zu vergleichen, besteht darin, das Schlüsselwort is und den Operator is not zu verwenden:

select null > 0;
 
– result: null
 
select null < 0;
 
– result: null
 
select null = 0;
 
– result: null
 
select null = null;
 
– result: null
 
select null != null;
 
– result: null

Wenn Sie feststellen möchten, ob die Werte zweier Spalten unterschiedlich sind, Sie können Folgendes verwenden:

select * from users
 
where deleted_at is null;
 
– result: 所有被标记为删除的 users

select * from users
 
where has_address is distinct from has_photo
 
– result: 地址(address)或照片(photo)两者只有其一的用户

Nicht in und Null

Unterabfrage (Unterauswahl) ist eine sehr praktische Möglichkeit,

Daten zu filtern

. Wenn Sie beispielsweise Benutzer abfragen möchten, die keine Pakete haben, können Sie die folgende Abfrage schreiben:

Wenn jedoch zu diesem Zeitpunkt die Benutzer-ID einer Zeile in der Pakettabelle null ist, Es entsteht ein Problem: Die Rückgabeergebnisse sind leer! Um zu verstehen, warum diese seltsame Sache passiert, müssen wir verstehen, was der SQL-Compiler tut:

select * from users 
 
where id not in (select user_id from packages)

Diese SQL-Anweisung wird wie folgt konvertiert:

select * from users 
 
where id not in (1, 2, null)

Wir wissen, dass id != null zu einem unbekannten Wert führt, null. Und das Ergebnis der UND-Verknüpfung zwischen einem beliebigen Wert und null ist null, sodass es keinen anderen Bedingungen entspricht ist das Ergebnis Der Grund für das Ergebnis ist, dass der logische Wert von null nicht wahr ist

select * from users 
 
where id != 1 and id != 2 and id != null

Wenn die Bedingungen umgekehrt sind, treten keine Probleme mit den Abfrageergebnissen auf. Jetzt fragen wir Benutzer mit Paketen ab.

Ebenso können wir ein einfaches Beispiel verwenden:

select * from users 
 
where id in (select user_id from packages)

Dieses SQL wird konvertiert in:

select * from users
 
where id in (1, 2, null)

Weil die Dabei handelt es sich bei der Klausel um eine Reihe von ODER-Bedingungen. Es spielt keine Rolle, ob eine davon null ergibt. Unwahre Werte haben keinen Einfluss auf die Berechnungsergebnisse anderer Teile der Klausel und sind gleichbedeutend mit Ignorieren.

select * from users 
 
where id = 1 or id = 2 or id = null

Null und Sortieren

Beim Sortieren wird der Nullwert als der größte angesehen. Dies bereitet Ihnen beim Sortieren in absteigender Reihenfolge (absteigend) Kopfschmerzen, weil Der Nullwert steht an erster Stelle.

Die folgende Abfrage dient dazu, Benutzerrankings basierend auf Bewertungen anzuzeigen, aber sie ordnet Benutzer ohne Bewertungen an die Spitze.

Es gibt zwei Möglichkeiten, diese Art von Problem zu lösen. Am einfachsten ist es, die Auswirkung von Null mithilfe von „coalesce“ zu beseitigen:

select name, points
 
from users
 
order by 2 desc;
 
– points 为 null 的记录排在所有记录之前!

Es gibt auch eine Möglichkeit, die Datenbankunterstützung erfordert und angibt, ob der Nullwert beim Sortieren an erster oder letzter Stelle stehen soll:

– 在输出时将 null 转换为 0 :
 
select name, coalesce(points, 0)
 
from users
 
order by 2 desc;
 
– 输出时保留 null, 但排序时转换为 0 :
 
select name, points
 
from users
 
order by coalesce(points, 0) desc;

Null kann natürlich auch verwendet werden, um das Auftreten von Fehlern zu verhindern, beispielsweise um mathematische Operationsfehler zu behandeln, wenn der Divisor 0 ist.

select name, coalesce(points, 0)
 
from users
 
order by 2 desc nulls last;

Dividieren durch 0

Dividieren durch 0 ist ein sehr schmerzhafter Fehler. SQL, das gestern einwandfrei lief, ging plötzlich schief, als es durch 0 geteilt wurde. Eine übliche Lösung besteht darin, zunächst mithilfe einer case-Anweisung zu bestimmen, ob der Nenner 0 ist, und dann die Divisionsoperation durchzuführen.

Die Funktionsweise der ase-Anweisung ist tatsächlich hässlich und der Nenner wird wiederverwendet. Es ist in Ordnung, wenn es sich um eine einfache Situation handelt, aber wenn der Nenner ein sehr komplexer

select case when num_users = 0 then 0 
 
else total_sales/num_users end;

Ausdruck

ist, wird es eine Tragödie geben: Es ist schwer zu lesen, schwer zu warten und zu ändern, und es wird viele Fehler geben, wenn Sie Seien Sie nicht vorsichtig. Zu diesem Zeitpunkt können wir uns die Vorteile von null ansehen. Verwenden Sie nullif, um den Nenner null zu machen, wenn er 0 ist. Dies wird keinen Fehler mehr melden Das Rückgabeergebnis wird zu Null.

Wenn Sie nicht Null möchten, sondern in 0 oder andere Zahlen konvertieren möchten, können Sie die Koaleszenzfunktion basierend auf dem vorherigen SQL verwenden:

select total_sales/nullif(num_users, 0);
 
nullif 是将其他值转为 null, 而Oracle的 nvl 是将 null 转换为其他值。

select coalesce(total_sales/nullif(num_users, 0), 0);
 
null 再转换回0

Fazit

Tony Hoare mag seinen Fehler bereuen, aber zumindest kann das Problem der Null leicht gelöst werden. Üben Sie also Ihren neuen ultimativen Zug und halten Sie sich von der ineffektiven Grube (Nullifizierung) fern, die Null gegraben hat!

Das obige ist der detaillierte Inhalt vonNull in SQL analysieren. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!