Effektiv Daten kombinieren mit Unions in SQL

Einführung

Viele Datenbanken verteilen Informationen auf verschiedene Tabellen basierend auf deren Bedeutung und Kontext. Oft möchte man beim Abrufen von Informationen mit Unions in SQL auf mehr als eine Tabelle gleichzeitig zugreifen.

Die Structured Query Language (SQL) bietet mehrere Ansätze zum Abrufen von Daten aus verschiedenen Tabellen, wie etwa Mengenoperationen. Genauer gesagt wird der Mengenoperator Union in den meisten relationalen Datenbanksystemen unterstützt. Die Union-Operation nimmt die Ergebnisse von zwei Abfragen mit übereinstimmenden Spalten und fügt sie zu einem Ergebnis zusammen.

In diesem Leitfaden verwenden Sie Unions in SQL, um Daten aus mehr als einer Tabelle gleichzeitig abzurufen und die Ergebnisse dann zu kombinieren. Sie werden auch den Union-Operator mit Filterung kombinieren, um die Ergebnisse zu sortieren.

Voraussetzungen für Unions in SQL

Um diesem Leitfaden zu folgen, benötigen Sie einen Computer mit einem SQL-basierten relationalen Datenbankverwaltungssystem (RDBMS). Die Anweisungen und Beispiele in diesem Leitfaden wurden in der folgenden Umgebung validiert:

• Ein Server mit Ubuntu 20.04, mit einem Benutzer ohne Root-Rechte und administrativen Berechtigungen und einer Firewall, die mit UFW konfiguriert ist, wie in unserem Einrichtungsleitfaden für Ubuntu 20.04 beschrieben.
• MySQL installiert und auf dem Server gesichert, wie in der Anleitung „How To Install MySQL on Ubuntu 20.04“ beschrieben. Dieser Leitfaden wurde mit einem MySQL-Benutzer ohne Root-Rechte verifiziert, der im Schritt 3 erstellt wurde.
• Grundkenntnisse im Ausführen von SELECT-Abfragen, um Daten aus der Datenbank abzurufen, wie in unserem Leitfaden „How To SELECT Rows FROM Tables in SQL“ beschrieben.

Hinweis: Bitte beachten Sie, dass viele RDBMS ihre eigenen einzigartigen SQL-Implementierungen verwenden. Obwohl die in diesem Tutorial beschriebenen Befehle in den meisten RDBMS funktionieren und Teil der standardmäßigen SQL-Syntax sind, können sich die genaue Syntax oder Ausgabe unterscheiden, wenn Sie sie auf einem anderen System als MySQL testen.

Sie benötigen auch eine Datenbank mit einigen Tabellen, die mit Beispieldaten geladen sind, damit Sie die Verwendung von Union-Operationen üben können. Wir empfehlen Ihnen, den folgenden Abschnitt „Mit MySQL verbinden und eine Beispieldatenbank einrichten“ zu lesen, um Einzelheiten zur Verbindung mit einem MySQL-Server und zur Erstellung der in den Beispielen dieses Leitfadens verwendeten Beispieldatenbank zu erfahren.

Mit MySQL verbinden und eine Beispieldatenbank einrichten

<p>Verbinden Sie sich mit einem MySQL-Server und erstellen Sie eine Beispieldatenbank, um den Beispielen im Leitfaden zu folgen.</p>

<p>Falls Ihr SQL-Datenbanksystem auf einem Remote-Server läuft, melden Sie sich über SSH an:</p>

<pre>ssh sammy@your_server_ip</pre>

<p>Öffnen Sie dann die MySQL-Eingabeaufforderung:</p>

<pre>mysql -u sammy -p</pre>

<p>Erstellen Sie eine Datenbank:</p>

<pre>CREATE DATABASE bookstore;</pre>

<p>Wechseln Sie zur „bookstore“-Datenbank:</p>

<pre>USE bookstore;</pre>

<p>Erstellen Sie eine Tabelle „book_purchases“ mit folgenden Spalten:</p>

<pre>CREATE TABLE book_purchases (

purchase_id int,

customer_name varchar(30),

book_title varchar(40),

date date,

PRIMARY KEY (purchase_id)

);</pre>

<p>Erstellen Sie dann die Tabelle „book_leases“:</p>

<pre>CREATE TABLE book_leases (

lease_id int,

customer_name varchar(30),

book_title varchar(40),

date_from date,

date_to date,

PRIMARY KEY (lease_id)

);</pre>

<p>Fügen Sie Beispieldaten in „book_purchases“ ein:</p>

<pre>INSERT INTO book_purchases

VALUES

(1, 'sammy', 'The Picture of Dorian Gray', '2022-10-01'),

(2, 'sammy', 'Pride and Prejudice', '2022-10-04'),

(3, 'sammy', 'The Time Machine', '2022-09-23'),

(4, 'bill', 'Frankenstein', '2022-07-23'),

(5, 'bill', 'The Adventures of Huckleberry Finn', '2022-10-01'),

(6, 'walt', 'The Picture of Dorian Gray', '2022-04-15'),

(7, 'walt', 'Frankenstein', '2022-10-13'),

(8, 'walt', 'Pride and Prejudice', '2022-10-19');</pre>

<p>Fügen Sie Beispieldaten in „book_leases“ ein:</p>

<pre>INSERT INTO book_leases

VALUES

(1, 'sammy', 'Frankenstein', '2022-09-14', '2022-11-14'),

(2, 'sammy', 'Pride and Prejudice', '2022-10-01', '2022-12-31'),

(3, 'sammy', 'The Adventures of Huckleberry Finn', '2022-10-01', '2022-12-01'),

(4, 'bill', 'The Picture of Dorian Gray', '2022-09-03', '2022-09-18'),

(5, 'bill', 'Crime and Punishment', '2022-09-27', '2022-12-05'),

(6, 'kim', 'The Picture of Dorian Gray', '2022-10-01', '2022-11-15'),

(7, 'kim', 'Pride and Prejudice', '2022-09-08', '2022-11-17'),

(8, 'kim', 'The Time Machine', '2022-09-04', '2022-10-23');</pre>

<p>Die Daten sind nun bereit für Union-Operationen.</p>

Syntax des Union-Operators verstehen

<p>Der Union-Operator in SQL führt zwei Ergebnismengen aus separaten SELECT-Abfragen zu einer zusammen, die Zeilen aus beiden Abfragen enthält.</p>

<p><strong>Hinweis:</strong> SQL-Datenbanken beschränken die Komplexität der Union-Abfragen nicht. Häufig kombiniert Union komplexe Abfragen, aber für diesen Leitfaden werden einfache SELECT-Abfragen verwendet.</p>

<p>Ein Beispiel zeigt die grundlegende Union-Syntax:</p>

<pre>SELECT column1, column2 FROM table1

UNION

SELECT column1, column2 FROM table2;

</pre>

<p>Union führt die erste SELECT-Anweisung aus und fügt dann die Ergebnisse der zweiten hinzu. Angenommen, Sie möchten Kunden auflisten, die entweder ein Buch gekauft oder ausgeliehen haben:</p>

<pre>SELECT customer_name FROM book_purchases

UNION

SELECT customer_name FROM book_leases;

</pre>

<p>Ergebnis:</p>

<pre>Output

+---------------+

| customer_name |

+---------------+

| sammy |

| bill |

| walt |

| kim |

+---------------+

</pre>

<p>Diese Ausgabe zeigt Kunden, die gekauft oder ausgeliehen haben. Um zu sehen, wie sie erstellt wurde, führen Sie die beiden SELECT-Anweisungen einzeln aus. Die erste Abfrage gibt alle Kunden zurück, die Bücher gekauft haben:</p>

<pre>SELECT customer_name FROM book_purchases;</pre>

<p>Ergebnis:</p>

<pre>Output

+---------------+

| customer_name |

+---------------+

| sammy |

| sammy |

| sammy |

| bill |

| bill |

| walt |

| walt |

| walt |

+---------------+

</pre>

<p>Die zweite Abfrage gibt Kunden zurück, die Bücher ausgeliehen haben:</p>

<pre>SELECT customer_name FROM book_leases;</pre>

<p>Ergebnis:</p>

<pre>Output

+---------------+

| customer_name |

+---------------+

| sammy |

| sammy |

| sammy |

| bill |

| bill |

| kim |

| kim |

| kim |

+---------------+

</pre>

<p>Union entfernt doppelte Werte, kombiniert die Ergebnisse und gibt eine Liste eindeutiger Kunden aus. Union funktioniert korrekt, wenn die Abfragen das gleiche Format haben.</p>

Union mit nicht übereinstimmender Spaltenanzahl

Versuchen Sie, eine Union zwischen einer SELECT-Anweisung, die eine einzelne Spalte zurückgibt, und einer anderen, die zwei Spalten zurückgibt, auszuführen:

SELECT purchase_id, customer_name FROM book_purchases
UNION
SELECT customer_name FROM book_leases;

Der Datenbankserver antwortet mit einem Fehler:

Output
The used SELECT statements have a different number of columns

Das Ausführen von Union-Operationen auf Ergebnismengen mit unterschiedlichen Spaltenzahlen ist nicht möglich.

Union mit nicht übereinstimmender Spaltenreihenfolge

Versuchen Sie, eine Union zwischen zwei SELECT-Anweisungen auszuführen, die dieselben Werte, aber in einer anderen Reihenfolge zurückgeben:

SELECT customer_name, book_title FROM book_purchases
UNION
SELECT book_title, customer_name FROM book_leases;

Der Datenbankserver gibt keinen Fehler zurück, aber die Ergebnismenge wird nicht korrekt sein:

Output
+------------------------------------+------------------------------------+
| customer_name                      | book_title                         |
+------------------------------------+------------------------------------+
| sammy                              | The Picture of Dorian Gray         |
| sammy                              | Pride and Prejudice                |
| sammy                              | The Time Machine                   |
| bill                               | Frankenstein                       |
| bill                               | The Adventures of Huckleberry Finn |
| walt                               | The Picture of Dorian Gray         |
| walt                               | Frankenstein                       |
| walt                               | Pride and Prejudice                |
| Frankenstein                       | sammy                              |
| Pride and Prejudice                | sammy                              |
| The Adventures of Huckleberry Finn | sammy                              |
| The Picture of Dorian Gray         | bill                               |
| Crime and Punishment               | bill                               |
| The Picture of Dorian Gray         | kim                                |
| Pride and Prejudice                | kim                                |
| The Time Machine                   | kim                                |
+------------------------------------+------------------------------------+
16 rows in set (0.000 sec)

In diesem Beispiel verbindet die Union-Operation die erste Spalte der ersten Abfrage mit der ersten Spalte der zweiten Abfrage und macht dasselbe für die zweite Spalte, wodurch Kundennamen und Buchtitel zusammen gemischt werden.

Verwendung von WHERE-Klauseln und Sortierung mit Unions in SQL

Im vorherigen Beispiel haben Sie Ergebnismengen kombiniert, die alle Zeilen in zwei entsprechenden Tabellen darstellen. Oft müssen Sie jedoch Zeilen filtern, bevor Sie die Ergebnisse zusammenführen. SELECT-Anweisungen, die mit dem Union-Operator zusammengeführt werden, können dabei die WHERE-Klausel verwenden.

Angenommen, Sie möchten wissen, welche Bücher Sammy entweder gekauft oder ausgeliehen hat. Führen Sie die folgende Abfrage aus:

SELECT book_title FROM book_purchases
WHERE customer_name = 'Sammy'
UNION
SELECT book_title FROM book_leases
WHERE customer_name = 'Sammy';

Beide SELECT-Abfragen beinhalten die WHERE-Klausel und filtern die Zeilen aus zwei verschiedenen Tabellen, um nur Käufe und Leihen von Sammy einzuschließen. Das Ergebnis dieser Abfrage sieht wie folgt aus:

Output
+------------------------------------+
| book_title                         |
+------------------------------------+
| The Picture of Dorian Gray         |
| Pride and Prejudice                |
| The Time Machine                   |
| Frankenstein                       |
| The Adventures of Huckleberry Finn |
+------------------------------------+
5 rows in set (0.000 sec)

Erneut stellt Union sicher, dass keine Duplikate in der Ergebnisliste vorhanden sind. Sie können WHERE-Klauseln verwenden, um einzuschränken, welche Zeilen in beiden SELECT-Abfragen oder nur in einer von ihnen zurückgegeben werden. Zusätzlich kann die WHERE-Klausel in beiden Anweisungen auf unterschiedliche Spalten und Bedingungen verweisen.

Die durch die Union-Operation zurückgegebenen Ergebnisse folgen keiner bestimmten Reihenfolge. Um dies zu ändern, können Sie die ORDER BY-Klausel verwenden. Die Sortierung erfolgt dabei auf den endgültigen, zusammengeführten Ergebnissen und nicht auf den einzelnen Abfragen.

Um die Buchtitel alphabetisch zu sortieren, nachdem Sie eine Liste aller von Sammy gekauften oder ausgeliehenen Bücher abgerufen haben, führen Sie die folgende Abfrage aus:

SELECT book_title FROM book_purchases
WHERE customer_name = 'Sammy'
UNION
SELECT book_title FROM book_leases
WHERE customer_name = 'Sammy'
ORDER BY book_title;

Die folgende Ausgabe wird auf dem Bildschirm angezeigt:

Output
+------------------------------------+
| book_title                         |
+------------------------------------+
| Frankenstein                       |
| Pride and Prejudice                |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray         |
| The Time Machine                   |
+------------------------------------+
5 rows in set (0.001 sec)

Diesmal werden die Ergebnisse basierend auf der Spalte „book_title“ sortiert, die die zusammengeführten Ergebnisse beider SELECT-Abfragen enthält.

Verwendung von Union ALL zur Beibehaltung der Duplikate

<p>Der Union-Operator entfernt automatisch doppelte Zeilen aus Ergebnissen. Manchmal ist das jedoch nicht gewünscht. Angenommen, Sie interessieren sich für Bücher, die entweder am 1. Oktober 2022 gekauft oder ausgeliehen wurden. Um diese Titel abzurufen, verwenden Sie die folgende Abfrage:</p>

<pre>SELECT book_title FROM book_purchases

WHERE date = '2022-10-01'

UNION

SELECT book_title FROM book_leases

WHERE date_from = '2022-10-01'

ORDER BY book_title;

</pre>

<p>Diese Abfrage liefert eindeutige Ergebnisse:</p>

<pre>Output

+------------------------------------+

| book_title |

+------------------------------------+

| Pride and Prejudice |

| The Adventures of Huckleberry Finn |

| The Picture of Dorian Gray |

+------------------------------------+

3 rows in set (0.001 sec)

</pre>

<p>Die Ergebnisse zeigen jedoch nicht, ob ein Buch nur gekauft, nur ausgeliehen oder beides wurde, da Union Duplikate entfernt. Um doppelte Zeilen beizubehalten, können Sie stattdessen Union ALL verwenden:</p>

<pre>SELECT book_title FROM book_purchases

WHERE date = '2022-10-01'

UNION ALL

SELECT book_title FROM book_leases

WHERE date_from = '2022-10-01'

ORDER BY book_title;

</pre>

<p>Das Ergebnis enthält jetzt doppelte Einträge:</p>

<pre>Output

+------------------------------------+

| book_title |

+------------------------------------+

| Pride and Prejudice |

| The Adventures of Huckleberry Finn |

| The Adventures of Huckleberry Finn |

| The Picture of Dorian Gray |

| The Picture of Dorian Gray |

+------------------------------------+

5 rows in set (0.000 sec)

</pre>

<p>Union ALL ist auch schneller als Union, da die Datenbank keine Duplikatprüfung durchführt. Wenn keine doppelten Zeilen erwartet werden, kann Union ALL die Leistung bei größeren Datensätzen verbessern.</p>

Fazit: Unions in SQL

Durch das Befolgen dieses Leitfadens haben Sie Daten aus mehreren Tabellen mithilfe von Union- und Union ALL-Operationen abgerufen. Sie haben auch WHERE-Klauseln verwendet, um die Ergebnisse zu filtern, und ORDER BY-Klauseln, um die Ergebnisse zu sortieren. Schließlich haben Sie mögliche Fehler und unerwartete Verhaltensweisen kennengelernt, wenn die SELECT-Anweisungen unterschiedliche Datenformate aufweisen.

Obwohl die hier beschriebenen Befehle in den meisten relationalen Datenbanken funktionieren sollten, verwendet jede SQL-Datenbank ihre eigene spezifische Implementierung der Sprache.  Sie sollten die offizielle Dokumentation Ihres RDBMS für eine vollständige Beschreibung jedes Befehls und seiner Optionen konsultieren.

Wenn Sie mehr über verschiedene Konzepte rund um die SQL-Sprache und die Arbeit damit erfahren möchten, empfehlen wir Ihnen, die anderen Leitfäden in der Serie „How To Use SQL“ zu lesen.