SQLite Tabellenverknüpfung (Table Joining)

Einführung

In diesem Lab lernen Sie, wie Sie Daten aus mehreren Tabellen in SQLite mithilfe von Tabellenverknüpfungstechniken (table joining techniques) kombinieren können. Wir werden INNER JOIN, LEFT JOIN, das Verknüpfen mehrerer Tabellen und das Filtern von verknüpften Ergebnissen behandeln. Am Ende dieses Labs werden Sie in der Lage sein, Daten effizient mit SQLite abzurufen und zu kombinieren.

Skills Graph

Tabellen erstellen und befüllen

In diesem Schritt erstellen Sie zwei Tabellen, employees (Mitarbeiter) und departments (Abteilungen), und befüllen diese mit Beispieldaten. Diese Tabellen werden in den folgenden Schritten verwendet, um Tabellenverknüpfungstechniken (table joining techniques) zu demonstrieren.

Öffnen Sie zunächst die SQLite-Shell, indem Sie den folgenden Befehl im Terminal ausführen:

sqlite3 /home/labex/project/company.db

Dieser Befehl öffnet die SQLite-Shell und verbindet sich mit einer Datenbankdatei namens company.db. Wenn die Datei nicht existiert, wird sie von SQLite erstellt.

Erstellen Sie nun die Tabelle employees mit dem folgenden SQL-Befehl:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER
);

Dieser Befehl erstellt eine Tabelle namens employees mit drei Spalten: id, name (Name) und department_id (Abteilungs-ID). Die Spalte id ist der Primärschlüssel (primary key) und identifiziert jeden Mitarbeiter eindeutig.

Als Nächstes erstellen Sie die Tabelle departments mit dem folgenden SQL-Befehl:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT
);

Dieser Befehl erstellt eine Tabelle namens departments mit zwei Spalten: id und name. Die Spalte id ist der Primärschlüssel und identifiziert jede Abteilung eindeutig.

Fügen Sie nun einige Beispieldaten in die Tabelle employees ein:

INSERT INTO employees (name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', NULL);

Dieser Befehl fügt fünf Zeilen in die Tabelle employees ein. Jede Zeile repräsentiert einen Mitarbeiter und seine entsprechende Abteilungs-ID.

Fügen Sie abschließend Beispieldaten in die Tabelle departments ein:

INSERT INTO departments (name) VALUES
('Sales'),
('Marketing'),
('Engineering');

Dieser Befehl fügt drei Zeilen in die Tabelle departments ein. Jede Zeile repräsentiert eine Abteilung und ihren Namen.

Um zu überprüfen, ob die Tabellen korrekt erstellt und befüllt wurden, können Sie den folgenden SQL-Befehl ausführen:

SELECT * FROM employees;

Erwartete Ausgabe (Expected Output):

1|Alice|1
2|Bob|2
3|Charlie|1
4|David|3
5|Eve|

Und:

SELECT * FROM departments;

Erwartete Ausgabe (Expected Output):

1|Sales
2|Marketing
3|Engineering

INNER JOIN-Abfragen durchführen

In diesem Schritt lernen Sie, wie Sie die INNER JOIN-Klausel in SQLite verwenden. Ein INNER JOIN gibt Zeilen zurück, wenn in beiden Tabellen eine Übereinstimmung basierend auf einer bestimmten Bedingung vorhanden ist.

Die grundlegende Syntax für INNER JOIN lautet:

SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

In unserem Fall möchten wir die Mitarbeiternamen zusammen mit ihren Abteilungsnamen abrufen. Führen Sie dazu den folgenden SQL-Befehl aus:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

Dieser Befehl verknüpft die Tabellen employees und departments basierend auf der Spalte department_id in employees, die mit der Spalte id in departments übereinstimmt. Anschließend wählt er den Namen des Mitarbeiters aus der Tabelle employees und den Namen der Abteilung aus der Tabelle departments aus.

Erwartete Ausgabe (Expected Output):

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering

Diese Ausgabe zeigt den Namen des Mitarbeiters und den entsprechenden Abteilungsnamen. Beachten Sie, dass Eve nicht im Ergebnis enthalten ist, da ihre department_id NULL ist und es keine übereinstimmende Abteilung gibt. INNER JOIN gibt nur übereinstimmende Zeilen zurück.

LEFT JOIN für optionale Daten verwenden

In diesem Schritt lernen Sie, wie Sie LEFT JOIN in SQLite verwenden. Ein LEFT JOIN (oder LEFT OUTER JOIN) gibt alle Zeilen aus der linken Tabelle und die übereinstimmenden Zeilen aus der rechten Tabelle zurück. Wenn in der rechten Tabelle keine Übereinstimmung vorhanden ist, werden NULL-Werte für die Spalten der rechten Tabelle zurückgegeben. Dies ist nützlich, wenn Sie alle Datensätze aus einer Tabelle abrufen und zugehörige Daten aus einer anderen Tabelle einbeziehen möchten, falls diese vorhanden sind.

Die grundlegende Syntax für LEFT JOIN lautet:

SELECT column1, column2 FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Um alle Mitarbeiter und ihre Abteilungsnamen abzurufen, auch wenn einem Mitarbeiter keine Abteilung zugewiesen ist, führen Sie den folgenden SQL-Befehl aus:

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

Dieser Befehl verknüpft die Tabellen employees und departments basierend auf der Spalte department_id in employees, die mit der Spalte id in departments übereinstimmt. Er wählt den Namen des Mitarbeiters aus der Tabelle employees und den Namen der Abteilung aus der Tabelle departments aus. Da es sich um einen LEFT JOIN handelt, werden alle Mitarbeiter aufgelistet.

Erwartete Ausgabe (Expected Output):

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering
Eve|

Beachten Sie, dass Eve aufgeführt ist, der Abteilungsname jedoch leer (NULL) ist, da ihre department_id NULL ist und es keine übereinstimmende Abteilung gibt. Dies zeigt, wie LEFT JOIN alle Zeilen aus der linken Tabelle (employees) einschließt, auch wenn in der rechten Tabelle (departments) keine Übereinstimmung vorhanden ist.

Mehrere Tabellen verknüpfen

In diesem Schritt lernen Sie, wie Sie mehrere Tabellen in SQLite verknüpfen. Das Verknüpfen von mehr als zwei Tabellen beinhaltet das Kombinieren von Daten aus mehreren verwandten Tabellen mithilfe von mehreren JOIN-Klauseln.

Fügen wir zunächst eine neue Tabelle namens locations hinzu, um Abteilungsstandorte zu speichern. Führen Sie den folgenden SQL-Befehl aus, um die Tabelle locations zu erstellen:

CREATE TABLE locations (
    id INTEGER PRIMARY KEY,
    department_id INTEGER,
    city TEXT
);

Dieser Befehl erstellt eine Tabelle namens locations mit drei Spalten: id, department_id und city.

Fügen Sie als Nächstes einige Beispieldaten in die Tabelle locations ein:

INSERT INTO locations (department_id, city) VALUES
(1, 'New York'),
(2, 'Los Angeles'),
(3, 'Chicago');

Verknüpfen wir nun alle drei Tabellen, um Mitarbeiternamen, Abteilungsnamen und Abteilungsstandorte abzurufen. Führen Sie den folgenden SQL-Befehl aus:

SELECT employees.name, departments.name, locations.city
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id;

Diese Abfrage verknüpft zuerst employees und departments basierend auf employees.department_id = departments.id. Anschließend wird das Ergebnis mit der Tabelle locations basierend auf departments.id = locations.department_id verknüpft. Dies verknüpft Mitarbeiter mit ihren Abteilungen und dann mit dem Standort dieser Abteilungen.

Erwartete Ausgabe (Expected Output):

Alice|Sales|New York
Bob|Marketing|Los Angeles
Charlie|Sales|New York
David|Engineering|Chicago

Dies zeigt den Namen jedes Mitarbeiters, den Namen seiner Abteilung und die Stadt, in der sich seine Abteilung befindet. Eve ist nicht enthalten, da ihr keine Abteilung zugewiesen ist.

Verknüpfte Ergebnisse filtern

In diesem Schritt lernen Sie, wie Sie die Ergebnisse verknüpfter Tabellen mithilfe der WHERE-Klausel in SQLite filtern. Das Filtern ermöglicht es Ihnen, nur die Zeilen abzurufen, die bestimmte Kriterien nach der Verknüpfungsoperation erfüllen.

Um die Namen der Mitarbeiter abzurufen, die in der Vertriebsabteilung (Sales department) arbeiten, führen Sie den folgenden SQL-Befehl aus:

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
WHERE departments.name = 'Sales';

Diese Abfrage verknüpft die Tabellen employees und departments und filtert dann die Ergebnisse, um nur Mitarbeiter einzubeziehen, deren Abteilungsname 'Sales' ist.

Erwartete Ausgabe (Expected Output):

Alice
Charlie

Dies zeigt, dass Alice und Charlie in der Vertriebsabteilung arbeiten.

Rufen wir nun die Namen der Mitarbeiter ab, die in Abteilungen mit Sitz in New York arbeiten. Führen Sie den folgenden SQL-Befehl aus:

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id
WHERE locations.city = 'New York';

Diese Abfrage verknüpft alle drei Tabellen und filtert dann die Ergebnisse, um nur Mitarbeiter einzubeziehen, deren Abteilungsstandort New York ist.

Erwartete Ausgabe (Expected Output):

Alice
Charlie

Auch hier sind Alice und Charlie die einzigen Mitarbeiter in Abteilungen mit Sitz in New York.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie Daten aus mehreren Tabellen in SQLite mithilfe von Tabellenverknüpfungstechniken kombinieren. Sie haben INNER JOIN behandelt, das Zeilen zurückgibt, wenn es in beiden Tabellen eine Übereinstimmung gibt, und LEFT JOIN, das alle Zeilen aus der linken Tabelle und die übereinstimmenden Zeilen aus der rechten Tabelle zurückgibt. Sie haben auch gelernt, wie Sie mehrere Tabellen verknüpfen und verknüpfte Ergebnisse mithilfe der WHERE-Klausel filtern. Diese Fähigkeiten ermöglichen es Ihnen, Daten effizient mit SQLite abzurufen und zu kombinieren.