Die SQL Coalesce-Funktion: Umgang mit Nullwerten

Sie wissen vielleicht schon, wie man in SQL Nullwerte zurückgibt. Jetzt werden wir lernen, wie man das Gegenteil macht. Die SQL-Funktion COALESCE mag zwar komplex erscheinen, ist aber eigentlich sehr einfach und nützlich. Schauen wir uns einige Beispiele an, wie die Funktion SQL COALESCE verwendet werden kann, um mit NULL-Werten in SQL zu arbeiten.

Die Notwendigkeit von Coalesce in SQL

Bevor wir uns im Detail mit der SQL-Funktion COALESCE beschäftigen, sollten Sie verstehen, wie sich NULL-Werte in Ausdrücken verhalten. Einfach ausgedrückt, bedeutet ein NULL-Wert, dass es für einen bestimmten Eintrag in einer Tabellenspalte derzeit keinen Wert gibt.

Jeder Ausdruck, der Zahlen, Datumsangaben, Zeichenketten oder Boolesche Werte und den Wert NULL enthält, gibt selbst NULL zurück. Das liegt daran, dass NULL einen unbekannten Wert darstellt. Da jeder Ausdruck, der einen unbekannten Wert enthält, nicht vollständig bestimmt werden kann, muss er natürlich NULL zurückgeben!

Werfen Sie einen Blick auf die folgenden Beispielausdrücke:

	2  +  NULL 			returns		 NULL

	2  *  NULL 			returns		 NULL

	'hello'  ||  NULL 		returns		 NULL

	NULL  +  interval '1 day' 	returns		 NULL

	TRUE and NULL	                returns		 NULL

Arbeiten mit Transit-Fees

Um zu erklären, wie die Funktion SQL COALESCE funktioniert, verwenden wir das geführte Beispiel der Transitbußgelder. Die nachstehende Tabelle speichert die ID des Fahrers, das Bußgeld, das er erhalten hat, das Datum und die Uhrzeit, zu der das Bußgeld verhängt wurde, die Stufe/Grad des Vergehens, das der Fahrer begangen hat, und den Betrag der vorherigen unbezahlten Bußgelder, die der Fahrer angehäuft hat.

Beachten Sie, dass die Spalte offense_level die Werte "Band A", "Band B" oder "Band C" haben kann. Ein Bußgeldwert der Stufe B bedeutet, dass der Fahrer das 1,5-fache des Bußgeldbetrags zahlen muss, wenn er das ursprüngliche Bußgeld nicht innerhalb von 30 Tagen begleicht. Bei Stufe C verdoppelt sich der Bußgeldbetrag nach 30 Tagen. Eine Einstufung in Stufe A hat keinen Einfluss auf die Höhe des Bußgelds, wenn es nicht innerhalb von 30 Tagen bezahlt wird.

Das Mysterium der verschwundenen Geldstrafe

Zunächst betrachten wir die Geldbußen innerhalb der 30-tägigen Zahlungsfrist. Die zusätzlichen Gebühren, die dem Fahrer nach Ablauf der 30-Tage-Frist in Rechnung gestellt werden können, werden wir in einem späteren Beispiel betrachten.

Theoretisch sollte die folgende Abfrage den Wert der einzelnen Bußgelder und den Gesamtwert aller Bußgelder, die der Fahrer zahlen muss (einschließlich früherer unbezahlter Bußgelder), zurückgeben.

SELECT 	driver_id,
		fine_value 			as "fine_amount", 
		fine_value + unpaid_fines 	as "total_due" 
FROM 	fines;

Wenn wir jedoch einen Blick auf die Abfrageausgabe in der nächsten Tabelle werfen, werden wir feststellen, dass der erste Datensatz keinen Wert für die Spalte total_due enthält! Was ist schief gelaufen?

Der Grund dafür ist ganz einfach. Da der Fahrer mit der ID 16877165 keine unbezahlten Geldstrafen hatte, wurde der in der Spalte unpaid_fines für diesen Fahrer gespeicherte Wert standardmäßig als NULL behandelt.

Wie bereits erwähnt, gibt jeder Ausdruck, der einen NULL-Wert enthält, NULL zurück. So ergab die Addition von fine_value zu unpaid_fines versehentlich NULL, was sich in einer leeren Zelle in der Ergebnistabelle niederschlug.

SQL Coalesce als Retter in der Not

Um dieses Problem zu beheben, verwenden wir die SQL-Funktion COALESCE, die den ersten Nicht-NULL-Wert aus einer Liste von Argumenten zurückgibt, die wir ihr übergeben. Die Funktion kann so viele Argumente wie nötig annehmen. In diesem Fall übergeben wir dem Funktionsaufruf jedoch nur zwei Argumente: unpaid_fines und einen Wert von 0. Hier ist die Abfrage:

SELECT 	driver_id,
		fine_value 						as "fine_amount", 
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM 	fines;

Erklären wir nun, wie und warum das funktioniert.

Wenn COALESCE(unpaid_fines, 0) ausgewertet wird und unpaid_fines für einen bestimmten Datensatz in der Tabelle NULL ist, überspringt SQL diesen Wert und geht zum nächsten Argument über, das wir an die Funktion übergeben haben. Erinnern Sie sich, dass in diesem Zusammenhang ein Wert von NULL bedeutet, dass ein Fahrer keine unbezahlten Geldstrafen hat.

In diesem Fall ist der nächste Wert, den wir nach unpaid_fines angegeben haben, 0. Da dies der erste Nicht-NULL-Wert ist, auf den wir gestoßen sind, als wir uns für den ersten Fahrer von links nach rechts bewegt haben, wird der Funktionsaufruf einfach 0 zurückgeben. Das ist genau das, was wir wollen - wenn ein Fahrer keine unbezahlten Bußgelder hat, dann müssen wir seine unbezahlten Bußgelder einfach als Null behandeln!

Wenn ein Fahrer jedoch einige unbezahlte Bußgelder hat, dann ist der Wert, der unter unpaid_fines für diesen bestimmten Datensatz gespeichert ist, nicht NULL. In diesem Fall gibt der Aufruf von COALESCE(unpaid_fines, 0) den unter unpaid_fines gespeicherten Wert zurück, so dass wir einen unbezahlten Betrag, der nicht Null ist, zur aktuellen Geldstrafe des Fahrers addieren können, um den fälligen Gesamtbetrag zu erhalten.

Versäumte Zahlungsfristen: Zusätzliche Gebühr fällt an

Wie bereits erwähnt, wird der zu zahlende Betrag mit dem Vergehenssatz multipliziert, wenn das Bußgeld nicht innerhalb von 30 Tagen bezahlt wird. In der folgenden Abfrage mit der SQL-Funktion COALESCE werden wir die offense_rate in unsere Berechnungen einbeziehen. Schauen Sie sich das an:

SELECT driver_id,
	     fine_value as "fine_amount", 
				COALESCE(unpaid_fines,0) as "unpaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * ofense_rate + COALESCE(unpaid_fines,0)	as "total due_after_30_days" 
FROM  fines;

Dieses Mal haben wir einen leeren Wert in der vierten Zeile unter der Spalte total_due_after_30_days. Hoffentlich können Sie erkennen, was wir unserer Abfrage hinzufügen müssen, damit sie richtig funktioniert.

Wie zuvor müssen wir COALESCE aufrufen. Diesmal müssen wir SQL COALESCE verwenden, um den Feinmultiplikator zu berechnen. Damit wird die Möglichkeit berücksichtigt, dass das Vergehen des Fahrers nicht angegeben wurde. In diesem Fall wird die Geldstrafe so behandelt, als ob das Vergehen als Band A (keine Strafe) eingestuft worden wäre.

Natürlich geben wir als zweites Argument den Wert 1 an. Wenn also der Wert unter offense_level für einen bestimmten Fahrer zufällig NULL ist, bleibt der Bußgeldwert einfach gleich (er wird mit 1 multipliziert).

Hier ist die aktualisierte Abfrage:

SELECT driver_id,
	     fine_value 	as "fine_amount", 
                COALESCE(unpaid_fines,0)	as "upaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * COALESCE(offense_level, 1) + COALESCE(unpaid_fines,0)	as "total_due_after_30_days" 
FROM  fines;

Für unser letztes Beispiel führen wir eine String-Verkettung durch, um zu sehen, was passiert, wenn eine der Spalten, mit denen wir arbeiten, den Wert NULL enthält. Hier ist die Abfrage:

SELECT	driver_id,
		reason ||' - '|| offense_level  			as “reason_and_severity”,
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM		fines

Wenn wir uns den vierten Datensatz ansehen, werden wir wieder einen leeren Wert feststellen, dieses Mal in der Spalte reason_and_severity. Dies liegt wiederum daran, dass wir versucht haben, einen Ausdruck auszuwerten, der einen NULL-Wert enthält.

Wenn wir stattdessen die SQL-Funktion COALESCE verwenden, können wir einen Wert angeben, auf den wir zurückgreifen können, wenn wir auf NULL stoßen. Für unsere Zwecke geben wir die Zeichenfolge "No Band" als Standardwert ein. Hier ist die aktualisierte Abfrage, die wie erwartet ausgeführt wird:

SELECT	driver_id,
		reason ||' - '|| COALESCE(offense_level, 'No Band')  	as "reason_and_severity",
		fine_value + COALESCE(unpaid_fines,0)		as "total_due" 
FROM		fines

Abschließende Überlegungen zur SQL COALESCE-Funktion

Nicht alle Datenbankmanager haben Zugriff auf die SQL-Funktion COALESCE, aber alle Datenbanken bieten ähnliche Funktionen für die Arbeit mit NULL-Werten. Mit Funktionen wie IFNULL(), NVL() und ISNULL() können Sie NULL-Werte erkennen, ersetzen oder umwandeln.

Wenn Sie mehr über SQL-Funktionen erfahren möchten, besuchen Sie unseren Kurs LearnSQL.de Standard-SQL-Funktionen , in dem wir viele wichtige SQL-Operationen und -Funktionen behandeln.