NULLs und Umgang mit fehlenden Daten in SQL

Der Umgang mit fehlenden Daten (d.h. NULLs) in SQL kann eine Herausforderung sein. NULLs können eine Menge Fallen aufwerfen, besonders wenn Sie nicht verstehen, wie sie funktionieren. In diesem Artikel werden wir über den Umgang mit NULL in SQL sprechen. Wir werden auch erklären, wie Sie häufige Fehler bei der Arbeit mit NULLs vermeiden können.

Fehlende Daten in Ihrer Datenbank sind eine unvermeidbare Realität. Es gibt viele Gründe, warum in Ihrer Datenbank Daten fehlen können: die vollständigen Daten sind noch nicht verfügbar, Benutzer geben unvollständige Informationen ein, Änderungen am Datenbankschema, Fehlfunktionen der Datenbank, menschliches Versagen und vieles mehr. Die Arbeit mit fehlenden Daten in SQL-Abfragen ist eine Herausforderung. In diesem Artikel zeige ich die häufigsten Fallstricke im Zusammenhang mit unvollständigen Daten in SQL.

Wenn Sie die Arbeit mit fehlenden oder unbekannten Daten in SQL üben möchten, empfehle ich Ihnen unseren SQL-Praxis Track. Zum Zeitpunkt der Erstellung dieses Artikels enthält er 10 Kurse, in denen Sie SQL üben können - und wir fügen ständig weitere hinzu! Die Kurse sind in Abschnitte unterteilt und viele von ihnen haben einen Abschnitt, der sich mit NULL beschäftigt. Ich empfehle insbesondere diese Kurse, um den Umgang mit NULL in SQL zu üben:

• Basic SQL-Praxis: A Store
• SQL-Praxis: Universität
• Grundlegende SQL-Praxis: Führe Abfragen durch!
• Basic SQL-Praxis: Blog & Verkehrsdaten

Was ist NULL in SQL?

In SQL steht NULL für einen fehlenden oder undefinierten Wert in einer Datenbank. Es wird verwendet, um die Tatsache zu bezeichnen, dass der Wert in einem Feld nicht vorhanden oder unbekannt ist.

Ein häufiges Anfängerproblem bei NULLs ist, dass Ihre Datenbank NULLs oft nicht explizit anzeigt. In der folgenden Tabelle sind das Feld like für die Beitragskennung 1, das Feld location für die Beitragskennung 2 und das Feld views für die Beitragskennung 3 alle NULL. In einer Datenbank wird NULL jedoch nicht explizit angezeigt; stattdessen wird ein leeres Feld angezeigt. Sie müssen sich bewusst sein, dass NULL eine Möglichkeit ist, zu erraten, dass diese Felder NULL sind.

id	title	location	views	likes
1	Quick Morning Routines!	London	94,365
2	Eco-Friendly Living Tips		123,891	6,587
3	Healthy Snacks on the Go	Paris		9,457

Es ist jedoch wichtig zu wissen, dass NULL sich von einer leeren Zeichenkette oder einer Null unterscheidet. NULL ist das Fehlen eines Wertes; es bedeutet, dass der Wert unbekannt ist. Wie wir gleich sehen werden, ist NULL selbst eigentlich kein echter Wert. Viele Leute sagen oder schreiben etwas wie "Es gibt NULL Werte in diesem Feld", aber das ist technisch nicht korrekt.

Beim Entwurf einer Tabelle in einer Datenbank kann ein Datenbankdesigner entscheiden, dass NULL für ein bestimmtes Feld nicht zulässig ist. Dies geschieht, indem bei der Erstellung der Tabelle eine NOT NULL-Beschränkung für eine Spalte festgelegt wird. Primärschlüssel (Spalten, die Zeilen in einer Tabelle identifizieren) sind standardmäßig auch NOT NULL.

Es gilt als beste Praxis, NULLs in Ihrem Datenbankdesign zu vermeiden. Daher sollten so viele Spalten wie möglich als NOT NULL definiert werden. NULLs sollten Sie nur zulassen, wenn es unbedingt erforderlich ist. Daten können jedoch unübersichtlich sein, und manchmal lässt sich NULL nicht vermeiden.

Der Datensatz

In diesem Artikel verwenden wir Daten aus der Tabelle posts. Stellen Sie sich vor, Sie holen sich Daten von Ihrer bevorzugten Social-Media-Plattform, um sie zu analysieren. Die Daten von dieser Plattform sind in dieser Tabelle gespeichert. Hier sind die Felder:

• id - Die ID des Beitrags.
• title - Der Titel des Beitrags.
• url - Die URL (Webadresse) des Beitrags.
• creator - Der Name des Verfassers des Beitrags.
• published - Das Datum, an dem der Beitrag veröffentlicht wurde.
• type - Der Typ des Beitrags.
• location Wo der Beitrag veröffentlicht wurde; dies kann NULL sein, wenn der Ort unbekannt oder irrelevant ist.
• views - Wie viele Aufrufe jeder Beitrag hat; dies kann NULL sein, wenn der Ersteller diese Daten nicht öffentlich sichtbar machen möchte.
• likes - Die Anzahl der Likes für den Beitrag; dies kann NULL sein, wenn der Ersteller diese Daten nicht öffentlich sichtbar machen möchte.
• dislikes - Die Anzahl der Dislikes für den Beitrag; dies kann NULL sein, da die Plattform diese Daten nicht mehr anzeigt. Wir haben jedoch möglicherweise die Daten zu den Ablehnungen für ältere Beiträge.

Nachdem wir uns nun die Daten angesehen haben, wollen wir sie nutzen, um NULL zu verstehen.

Vergleichsoperatoren mit NULL

Selbst etwas so scheinbar Einfaches wie das Verhalten von NULL bei der Verwendung mit Vergleichsoperatoren kann für Anfänger kontraintuitiv und überraschend sein.

Angenommen, wir wollen herausfinden, wie viele Zeilen in der Tabelle posts fehlende views Felder haben. Zum Beispiel:

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

Ergebnis: 0 Zeilen

Großartig, null Zeilen haben fehlende Ansichten. Erstaunlich! Wie viele von ihnen sind dann nicht fehlend?

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

Ergebnis: 0 Zeilen

Auch null? Irgendetwas muss falsch sein.

Das Problem hier ist, dass Sie die IS NULL and IS NOT NULL Operatoren verwenden sollten, um auf NULLs zu testen:

SELECT COUNT(*)
FROM posts
WHERE views IS NULL;

Ergebnis: 34 Zeilen

SELECT COUNT(*)
FROM posts
WHERE views IS NOT NULL;

Ergebnis: 66 Zeilen

Warum unterscheiden sich diese Ergebnisse so sehr von denen der beiden vorherigen Abfragen?

Dreistufige Logik in SQL

Die Vergleichsprobleme für NULL ergeben sich aus der Tatsache, dass NULL kein echter Wert ist. Dies ist der wichtigste Punkt, den Sie verstehen müssen, um mit NULL effizient arbeiten zu können.

SQL verwendet eine dreistufige Logik. Jede logische Bedingung in SQL kann einen von drei Werten haben: TRUE, FALSE oder NULL. NULL bedeutet hier "Ich weiß es nicht". Wenn Sie eine Bedingung in WHERE verwenden, gibt SQL die Zeilen zurück, für die die logische Bedingung in WHERE TRUE ist. Es gibt nicht die Zeilen zurück, für die die Bedingung FALSE ist (wie Sie es erwarten würden) und für die die Bedingung NULL ist (was nicht immer das ist, was Sie erwarten würden).

Betrachten wir ein Beispiel dafür, wie WHERE funktioniert:

SELECT COUNT(*)
FROM posts
WHERE views < 100;

Diese Abfrage zählt Beiträge, bei denen die Spalte views einen Wert hat und dieser Wert kleiner als 100 ist. Beiträge mit unbekannten Ansichten werden nicht gezählt. Dies mag kontraintuitiv sein: Wenn Sie die Beitragsdaten anzeigen, sehen Sie die Leere in vielen Feldern der Ansichten. Intuitiv denken Sie, dass diese Leere sicherlich weniger als 100 beträgt. Aber die Leere bedeutet, dass die Datenbank die Daten nicht hat; da sie die Daten nicht hat, kann sie nicht sagen, ob es weniger als 100 sind oder nicht. Die Zeilen mit einem leeren views Feld werden also nicht gezählt.

SELECT COUNT(*)
FROM posts
WHERE views < likes;

Diese Abfrage gibt Zeilen zurück, bei denen sowohl die Anzahl der Aufrufe als auch die Anzahl der Likes bekannt sind und der Wert views kleiner ist als der Wert likes. Es werden keine Zeilen zurückgegeben, bei denen der views Wert unbekannt ist oder der likes Wert unbekannt ist.

Wenn Sie die Beiträge mit leeren views Feldern in das Ergebnis aufnehmen möchten, müssen Sie explizit nach NULL filtern:

SELECT COUNT(*)
FROM posts
WHERE views < likes OR views IS NULL;

Kehren wir noch einmal zu den Abfragen zurück, mit denen wir begonnen haben:

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

Die Bedingung WHERE vergleicht die Spalte views mit NULL. NULL bedeutet jedoch "Ich kenne den Wert nicht". Die Datenbank kann nicht sagen, ob views gleich einem unbekannten Wert ist (oder nicht). Es kann sein oder auch nicht, also sagt die Datenbank NULL - d.h. "Ich weiß es nicht" - und diese Zeilen werden nicht im Ergebnis zurückgegeben.

Zur Erinnerung:

• Testen Sie auf NULL mit IS NULL und IS NOT NULL
• Vergleichsoperatoren (wie <, <=, >, >=, =, <>, und LIKE) geben NULL zurück, wenn eines der Argumente NULL ist. Wenn Sie NULL einbeziehen wollen, testen Sie explizit mit IS NULL oder IS NOT NULL darauf.

Die Verwendung von NULL in SQL-Funktionen

NULL ist in Operatoren und Funktionen ebenso problematisch. Die meisten Funktionen und Operatoren geben NULL zurück, wenn ihnen NULL als Argument übergeben wird.

Beispiel 1: Stellen Sie sich vor, Sie möchten die Kopfzeile für jeden Beitrag zurückgeben. (Die Überschrift besteht aus dem Titel, einem Bindestrich und dem Ort). Hier ist die Abfrage:

SELECT 
  title || ‘ - ‘ || location
FROM posts;

Ergebnis:

Quick Morning Routines! - London
Healthy Snacks on the Go - Paris

Die Abfrage gibt NULL zurück, wenn title oder location fehlen. Der Beitrag mit der ID 2 hat NULL als Ergebnis unserer Abfrage, da seine location unbekannt ist.

Beispiel 2: Das Gleiche gilt für arithmetische Operatoren. Nehmen wir an, Sie möchten das Engagement eines Beitrags als Summe von likes und dislikes berechnen:

SELECT 
  title, 
  likes, 
  dislikes, 
  likes + dislikes AS engagement
FROM posts;

Ergebnis:

title	likes	dislikes	engagement
Quick Morning Routines!	25	15	30
Eco-Friendly Living Tips	10
Healthy Snacks on the Go		34

Wenn eines der Felder likes oder dislikes NULL ist, dann ist der in der Spalte "Engagement" zurückgegebene Wert auch NULL.

Beispiel 3: Das gleiche Verhalten zeigen reguläre Funktionen wie UPPER():

SELECT 
  title, 
  UPPER(creator)
FROM posts;

title	UPPER(creator)
Quick Morning Routines!	JENNY
Eco-Friendly Living Tips
Healthy Snacks on the Go	RACHEL82

Der Ersteller des Beitrags "Tipps für umweltfreundliches Leben" ist unbekannt, und daher gibt der Ausdruck UPPER(creator) den Wert NULL zurück.

Funktionen, die mit NULLs arbeiten

Glücklicherweise gibt es in SQL Funktionen, die diese Probleme mit NULL entschärfen.

COALESCE

COALESCE() nimmt viele Argumente entgegen und gibt den ersten Wert der Argumente zurück, der nichtNULL ist. Sie wird normalerweise verwendet, um NULL durch einen sinnvollen Wert in einer anderen Funktion oder einem Ausdruck zu ersetzen. Wir könnten unsere Engagement-Abfrage wie folgt ändern:

SELECT 
  title, 
  likes, 
  dislikes, 
  COALESCE(likes, 0) + COALESCE(dislikes, 0) AS engagement
FROM posts;

Immer wenn der Wert von likes oder dislikes NULL ist, ersetzt die Funktion COALESCE() ihn durch 0. Der neue Wert wird in der Berechnung verwendet, und wir vermeiden NULL Ergebnisse:

title	likes	dislikes	engagement
Quick Morning Routines!	25	15	30
Eco-Friendly Living Tips	10		10
Healthy Snacks on the Go		34	34

Sie können auch COALESCE() verwenden, um eine sinnvolle Bezeichnung für NULLs in den Ergebnissen anzugeben. Die folgende Abfrage ersetzt NULL durch "Unbekannt" in der Ergebnismenge; das Feld selbst ist weiterhin NULL in der Datenbank:

SELECT 
  title, 
  COALESCE(location, ‘Unknown’) AS location
FROM posts;

Hier ist das Ergebnis:

title	location
Quick Morning Routines!	London
Eco-Friendly Living Tips	Unknown
Healthy Snacks on the Go	Paris

Zur Erinnerung: Sie verwenden die Funktion COALESCE():

• Um eine aussagekräftige Bezeichnung für NULL in Berichten bereitzustellen.
• Um einen Wert für NULL in Berechnungen anzugeben.

NULLIF

Eine weitere Funktion, die mit NULL arbeitet, ist NULLIF. Diese Funktion ist etwas seltsam: Sie nimmt zwei Argumente entgegen und gibt NULL zurück, wenn die Argumente gleich sind. In der Praxis verwenden Sie NULLIF, um eine Division durch Null zu vermeiden:

SELECT 
  title, 
  likes / NULLIF(views, 0)
FROM posts;

Sie wollen das Verhältnis von likes zu views für Beiträge berechnen. Wenn der Wert views jedoch 0 ist, könnte ein Fehler bei der Division durch Null auftreten. Um dies zu vermeiden, verwenden Sie die Funktion NULLIF. Wenn views gleich Null ist, dann gibt NULLIF(views, 0) NULL zurück.

NULL bei der Division ergibt NULL das Ergebnis und vermeidet den Fehler bei der Division durch Null. Hier nutzen wir den Vorteil der NULL Kaskadierung über die Ergebnisse der Berechnungen.

NULL in GROUP BY und Aggregatfunktionen

Wenn Sie mit fehlenden Werten arbeiten, ist es gut zu wissen, wie sich NULL in GROUP BY und Aggregatfunktionen verhält.

NULL und GROUP BY

GROUP BY ordnen Zeilen in Gruppen ein, die auf gemeinsamen Werten in einer bestimmten Spalte basieren. Sie können dann Aggregatfunktionen auf jede Gruppe anwenden und Zusammenfassungen für jede Gruppe berechnen. Diese Abfrage zählt die Anzahl der Beiträge für jeden Ort:

SELECT 
  location, 
  COUNT(*)
FROM posts
GROUP BY location;

Mit GROUP BY werden alle Zeilen mit NULL in der Spalte zu einer Gruppe zusammengefasst; Sie berechnen die Statistiken für diese Gruppe wie jede andere.

In unserem Beispiel werden alle Beiträge mit einem unbekannten Ort in eine Gruppe eingeordnet:

location	COUNT
London	45
Paris	23
	12
…	…

NULL und Aggregatfunktionen

Generell gilt, dass Aggregatfunktionen auch NULLs ignorieren. Es gibt jedoch einige wichtige Varianten, wie einige Aggregatfunktionen NULLs behandeln.

Die Funktionen SUM(), MIN(), MAX() ignorieren alle NULLs:

SELECT 
  type, 
  SUM(views), 
  MIN(views), 
MAX(views)
FROM posts
GROUP BY type;

type	SUM	MIN	MAX
video	230,485	5,632	100,589
image	159,340	1,289	45,003
text	34,224	256	3,341
infographics

Die Funktion SUM() behandelt NULL so, als wäre es 0, so dass NULL keinen Einfluss auf das Ergebnis von SUM hat. Wenn jedoch alle Werte in der Gruppe NULL sind, ist das Ergebnis von SUM() NULL . In unserem Beispiel haben wir keine Ansichtsdaten für die Infografikgruppe, so dass die Summe für diese Gruppe NULL ist.

Die Funktionen MIN() und MAX() ignorieren auch NULL; sie geben die Minimal- und Maximalwerte der bekannten Werte zurück. Nur wenn alle Werte in der Gruppe NULL sind, geben diese Funktionen NULL zurück. Da unsere Infografikgruppe keine Daten enthält, werden die Minimal- und Maximalwerte als NULL gemeldet.

Die Funktion COUNT() ist etwas subtiler, wenn es um die Handhabung von NULL geht. Es gibt drei Varianten der COUNT -Syntax: COUNT(*), COUNT(expression), COUNT(DISTINCT). Sie können diese in unserem Artikel Was ist der Unterschied zwischen COUNT(*), COUNT(1), COUNT(column) und COUNT(DISTINCT)? nachlesen:

SELECT 
  COUNT(*), 
  COUNT(location), 
  COUNT(DISTINCT location)
FROM posts;

COUNT	COUNT	COUNT
100	78	52

Der Ausdruck COUNT(*) zählt alle Zeilen in der Ergebnismenge. In unserer Tabelle gibt es 100 Einträge posts Tabelle, also gibt dieser Ausdruck 100 zurück.

Der Ausdruck COUNT(location) zählt alle nichtNULL Werte in der angegebenen Spalte. In unserem Beispiel werden Beiträge gezählt, bei denen die Spalte location nicht NULL lautet. Beiträge mit unbekannten Orten werden ignoriert.

Der Ausdruck COUNT(DISTINCT location) schließlich zählt eindeutige Werte, die nichtNULL sind; mit anderen Worten, er ignoriert wiederholte Werte. Es wird gezählt, wie viele verschiedene Orte es in unserer Tabelle gibt. posts Tabelle gibt.

Die Funktion AVG() ignoriert NULL. Das ist im Allgemeinen das, was Sie erwarten. Sie sollten jedoch vorsichtig sein, wenn Sie AVG() mit COALESCE() verwenden. Alle folgenden Varianten geben unterschiedliche Werte zurück: AVG(views), AVG(COALESCE(views,0)), COALESCE(AVG(views)).

Zur Erinnerung:

• Zeilen mit NULLs in GROUP BY Spalten werden in eine separate Gruppe gestellt.
• Aggregatfunktionen ignorieren NULL und verwenden nur bekannte Werte in Berechnungen.
• Verwenden Sie COALESCE, wenn Sie einen unbekannten Wert durch einen bestimmten Wert ersetzen wollen.

NULL und JOIN

Sie müssen an NULL denken, wenn Sie JOIN verwenden, insbesondere mit OUTER JOINs wie LEFT JOIN oder FULL JOIN. Es kann NULLs in Spalten geben, die aus der richtigen Tabelle stammen.

Stellen Sie sich vor, wir haben eine andere Tabelle, comments, die Daten über Kommentare zu Beiträgen enthält. Sie enthält Informationen in den folgenden Spalten:

• id - Ein eindeutiger Bezeichner für jeden Kommentar.
• post_id - Die ID des Beitrags, auf den sich der Kommentar bezieht.
• content - Der Inhalt des Kommentars
• author - Der Autor des Kommentars
• upvotes - Die Anzahl der "Upvotes", die für diesen Kommentar vergeben wurden; dies kann sein NULL
• downvotes - Die Anzahl der Downvotes für diesen Kommentar; dies kann sein NULL

Wir wollen zählen, wie viele Kommentare es für jeden Beitrag gibt, aber wir wollen auch Beiträge ohne Kommentare in die Ergebnisse aufnehmen. Sie müssen posts LEFT JOIN comments verwenden, um alle Beiträge einzuschließen.

Als nächstes müssen Sie daran denken, COUNT(comments.id) und nicht COUNT(*) zu verwenden, wenn Sie die Kommentare zählen. Bei letzterem werden die Zeilen gezählt, unabhängig davon, ob die Zeile mit dem Kommentar in Verbindung steht. Richtig ist es, COUNT(comments.id) zu verwenden. Wenn es keine Kommentare gibt, lautet die ID NULL und wird nicht gezählt.

SELECT 
  posts.title, 
  COUNT(comments.id)
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id;

Ein weiteres Problem ist, dass die Bedingung WHERE manchmal die Bedingung OUTER JOIN"aufheben" kann. In der folgenden Abfrage wollen wir Kommentare finden, deren upvotes höher als 100 ist. Wenn der Beitrag einige Kommentare mit einer unbekannten Anzahl von "Upvotes" enthält, werden diese Kommentare nicht in das Ergebnis aufgenommen. Wenn der Beitrag nur Kommentare mit einer unbekannten Anzahl von Bewertungen enthält, wird der Beitrag trotz der Verwendung von LEFT JOIN überhaupt nicht berücksichtigt. Die Bedingung WHERE hebt die Bedingung LEFT JOIN effektiv auf:

SELECT 
  posts.title, 
  comments.content
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id
WHERE upvotes > 100;

Zur Erinnerung:

• LEFT JOIN RIGHT JOIN oder können in das Ergebnis einbringen. FULL JOIN NULL
• Die WHERE Bedingung kann die OUTER JOIN"auslöschen".

NULL in ORDER BY

Wenn Sie einen Bericht erstellen, möchten Sie die Daten oft in einer bestimmten Reihenfolge sortieren - z. B. in alphabetischer, aufsteigender oder absteigender Reihenfolge. Wie verhält sich NULL bei der Sortierung?

Wenn Sie nach einer Spalte sortieren, die NULL enthält, erscheinen die Zeilen mit NULL an erster oder letzter Stelle, je nachdem, welche Datenbank-Engine Sie verwenden. MySQL sortiert beispielsweise NULLs in aufsteigender Reihenfolge an erster Stelle, während Oracle sie in aufsteigender Reihenfolge an letzter Stelle sortiert. Sie können das Standardverhalten Ihrer Datenbank in deren Dokumentation nachlesen.

Wenn Sie sich nicht an das Standardverhalten erinnern oder Ihnen das Standardverhalten nicht gefällt, können Sie die Operatoren NULLS FIRST oder NULLS LAST nach ORDER BY verwenden, um das gewünschte Verhalten anzugeben:

SELECT 
  title, 
  views
FROM posts
ORDER BY views DESC NULLS LAST;

Dadurch wird sichergestellt, dass alle Zeilen, die ein NULL enthalten, zuletzt aufgelistet werden:

title	views
Quick Morning Routines!	120,365
…	…
Eco-Friendly Living Tips	256
Easy At-Home Workouts for All Levels
Healthy Snacks on the Go

Wie NULL mit ORDER BY zusammenarbeitet, können Sie in How ORDER BY and NULL Work Together in SQL im Detail nachlesen.

Behandeln Sie fehlende Daten mit NULL in SQL!

Der Umgang mit NULL und fehlenden Daten in SQL ist eine wichtige Fähigkeit für jeden, der mit Daten arbeitet. Wenn Sie die Feinheiten von NULL, sein Verhalten bei verschiedenen Operationen und die besten Praktiken für den Umgang mit fehlenden Daten verstehen, stellen Sie sicher, dass Ihre Abfragen genau und Ihre Analysen zuverlässig sind.

Um Ihre SQL-Kenntnisse zu vertiefen, sollten Sie unser Alle für immer SQL-Paket erwerben. Mit dieser einmaligen Zahlung erhalten Sie lebenslangen Zugang zu allen aktuellen und zukünftigen SQL-Kursen. Die Kurse decken alles ab, von grundlegenden Abfragen bis zu fortgeschrittenem SQL; was Sie lernen, wird Ihnen auf allen Ebenen Ihrer Karriere nützlich sein. Schauen Sie sich auch unseren SQL-Praxis Track mit 10 SQL-Praxiskursen und über 1.000 Übungen an. Erweitern Sie Ihre Kenntnisse langfristig mit LearnSQL.de!