SQLite Subquery-Techniken (Unterabfragen)

Einführung

In diesem Lab werden Sie SQLite Subquery-Techniken erkunden, um Ihre Datenabruf- und Filterfähigkeiten zu verbessern. Sie werden lernen, wie Sie Subqueries innerhalb der WHERE-Klausel verwenden, sie in die SELECT-Anweisung einbetten und korrelierte Subqueries erstellen.

Tabellen erstellen und Daten einfügen

In diesem ersten Schritt erstellen Sie zwei Tabellen, departments (Abteilungen) und employees (Mitarbeiter), und fügen einige Beispieldaten in diese ein. Dies stellt die Daten bereit, die Sie benötigen, um die Verwendung von Subqueries (Unterabfragen) in den folgenden Schritten zu üben.

Öffnen Sie zunächst Ihr Terminal in der LabEx VM (virtuelle Maschine). Ihr Standardpfad ist /home/labex/project.

Um zu beginnen, verbinden Sie sich mit einer SQLite-Datenbank namens company.db. Wenn die Datenbank nicht existiert, erstellt SQLite sie für Sie. Führen Sie den folgenden Befehl aus:

sqlite3 company.db

Dieser Befehl öffnet das SQLite-Befehlszeilentool und verbindet sich mit der company.db-Datenbank. Sie sehen die sqlite>-Eingabeaufforderung.

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

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT,
    location TEXT
);

Dieser Befehl erstellt eine Tabelle namens departments mit drei Spalten: department_id, department_name und location. Die Spalte department_id ist der Primärschlüssel (primary key) für diese Tabelle.

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

INSERT INTO departments (department_name, location) VALUES
('Sales', 'New York'),
('Marketing', 'Los Angeles'),
('Engineering', 'San Francisco');

Dieser Befehl fügt drei Zeilen in die Tabelle departments ein, die drei verschiedene Abteilungen und ihre Standorte darstellen.

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

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Dieser Befehl erstellt eine Tabelle namens employees mit drei Spalten: employee_id, employee_name und department_id. Die Spalte department_id ist ein Fremdschlüssel (foreign key), der auf die Spalte department_id in der Tabelle departments verweist.

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

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

Dieser Befehl fügt vier Zeilen in die Tabelle employees ein, die vier verschiedene Mitarbeiter und ihre Abteilungs-IDs darstellen.

Verwendung von Subqueries in der WHERE-Klausel

In diesem Schritt lernen Sie, wie Sie Subqueries (Unterabfragen) in der WHERE-Klausel verwenden, um Ergebnisse basierend auf der Ausgabe einer anderen Abfrage zu filtern.

Eine Subquery ist eine SELECT-Anweisung, die in eine andere SQL-Anweisung eingebettet ist. In diesem Fall verwenden Sie eine Subquery, um department_id-Werte aus der Tabelle departments (Abteilungen) auszuwählen und diese Werte dann zu verwenden, um die Ergebnisse einer Abfrage in der Tabelle employees (Mitarbeiter) zu filtern.

Lassen Sie uns alle Mitarbeiter finden, die in Abteilungen mit Sitz in 'New York' arbeiten. Dazu müssen Sie zuerst die department_id für Abteilungen in 'New York' finden und dann alle Mitarbeiter mit dieser department_id finden.

Geben Sie den folgenden SQL-Befehl in der sqlite>-Eingabeaufforderung ein:

SELECT employee_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Dieser Befehl wählt den employee_name aus der Tabelle employees aus, wobei die department_id in der Liste der department_id-Werte enthalten ist, die von der Subquery zurückgegeben werden. Die Subquery wählt die department_id aus der Tabelle departments aus, wobei die location 'New York' ist.

Nach Ausführung des Befehls sollten Sie die folgende Ausgabe sehen:

Alice
Charlie

Diese Ausgabe zeigt die Namen der Mitarbeiter, die in der Vertriebsabteilung (Sales department) arbeiten, die sich in New York befindet.

Subqueries in der SELECT-Klausel einbetten

In diesem Schritt lernen Sie, wie Sie Subqueries (Unterabfragen) in die SELECT-Klausel einer SQL-Anweisung einbetten, um zugehörige Daten abzurufen.

Das Einbetten einer Subquery in die SELECT-Klausel ermöglicht es Ihnen, einen einzelnen Wert für jede Zeile in der äußeren Abfrage abzurufen. Dieser Wert ist oft ein berechneter Wert oder ein zugehöriger Wert aus einer anderen Tabelle.

Lassen Sie uns den Namen jedes Mitarbeiters zusammen mit dem Namen seiner Abteilung abrufen. Geben Sie den folgenden SQL-Befehl in der sqlite>-Eingabeaufforderung ein:

SELECT employee_name, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name FROM employees;

Dieser Befehl wählt den employee_name aus der Tabelle employees (Mitarbeiter) aus und enthält auch eine Subquery, die den department_name aus der Tabelle departments (Abteilungen) abruft. Die Subquery verwendet die department_id aus der Tabelle employees, um die richtige Abteilung zuzuordnen. Das Ergebnis der Subquery wird als department_name mit einem Alias versehen.

Nach Ausführung des Befehls sollten Sie die folgende Ausgabe sehen:

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

Diese Ausgabe zeigt den Namen jedes Mitarbeiters und den Namen seiner entsprechenden Abteilung.

Korrelierte Subqueries erstellen

In diesem Schritt lernen Sie, wie Sie korrelierte Subqueries (Correlated Subqueries) erstellen. Korrelierte Subqueries sind Subqueries (Unterabfragen), die sich auf eine Spalte aus der äußeren Abfrage beziehen. Dies bedeutet, dass die Subquery für jede Zeile der äußeren Abfrage einmal ausgeführt wird.

Im Gegensatz zu einfachen Subqueries, die einmal ausgeführt werden und deren Ergebnis von der äußeren Abfrage verwendet wird, hängen korrelierte Subqueries von der äußeren Abfrage für ihre Werte ab. Sie werden verwendet, wenn Sie Werte innerhalb der Subquery mit Werten in der aktuellen Zeile der äußeren Abfrage vergleichen müssen.

Lassen Sie uns alle Mitarbeiter finden, die in einer Abteilung arbeiten, die sich in derselben Stadt wie der Name des Mitarbeiters befindet. Um dies zu ermöglichen, aktualisieren wir zuerst die Mitarbeiternamen, sodass sie Städtenamen sind.

UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Alice';
UPDATE employees SET employee_name = 'Los Angeles' WHERE employee_name = 'Bob';
UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Charlie';
UPDATE employees SET employee_name = 'San Francisco' WHERE employee_name = 'David';

Diese Befehle aktualisieren die Spalte employee_name in der Tabelle employees (Mitarbeiter) mit Städtenamen.

Schreiben wir nun die korrelierte Subquery:

SELECT e.employee_name FROM employees e WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location = e.employee_name);

Dieser Befehl wählt den employee_name aus der Tabelle employees (als e bezeichnet) aus, wobei die department_id in der Liste der department_id-Werte enthalten ist, die von der Subquery zurückgegeben werden. Die Subquery wählt die department_id aus der Tabelle departments (Abteilungen, als d bezeichnet) aus, wobei die location mit dem employee_name aus der äußeren Abfrage übereinstimmt.

Nach Ausführung des Befehls sollten Sie die folgende Ausgabe sehen:

New York
Los Angeles
San Francisco

Diese Ausgabe zeigt die Namen der Mitarbeiter (jetzt Städtenamen), die in Abteilungen arbeiten, die sich in derselben Stadt befinden.

Subquery-Effizienz mit JOIN bewerten

In diesem Schritt lernen Sie, wie Sie die Effizienz von Subqueries (Unterabfragen) bewerten und alternative Ansätze mithilfe von JOIN-Operationen zur Optimierung untersuchen.

Subqueries sind zwar leistungsstark, können aber insbesondere bei großen Datensätzen zu Performance-Engpässen führen. In vielen Fällen können Sie Subqueries mithilfe von JOIN-Operationen umschreiben, was effizienter sein kann.

Schreiben wir die korrelierte Subquery aus dem vorherigen Schritt mithilfe eines JOIN um:

SELECT e.employee_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location = e.employee_name;

Dieser Befehl wählt den employee_name aus der Tabelle employees (Mitarbeiter, als e bezeichnet) aus und verbindet ihn mit der Tabelle departments (Abteilungen, als d bezeichnet) über die Spalte department_id. Die WHERE-Klausel filtert dann die Ergebnisse, um nur Zeilen einzubeziehen, in denen die location in der Tabelle departments mit dem employee_name in der Tabelle employees übereinstimmt.

Um das Ergebnis zu überprüfen, führen Sie den Befehl aus. Sie sollten die gleiche Ausgabe wie im vorherigen Schritt sehen:

New York
Los Angeles
San Francisco

Um die Effizienz zu bewerten, würden Sie typischerweise EXPLAIN QUERY PLAN vor und nach der Änderung verwenden. Aufgrund von Einschränkungen in der LabEx-Umgebung können wir den Befehl EXPLAIN QUERY PLAN jedoch nicht vollständig demonstrieren. Die wichtigste Erkenntnis ist, dass JOIN-Operationen oft effizienter sind als korrelierte Subqueries, insbesondere bei größeren Datensätzen.

Beenden Sie abschließend die sqlite3-Shell:

.exit

Dadurch gelangen Sie zurück zur Bash-Eingabeaufforderung.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie SQLite Subqueries (Unterabfragen) verwenden, um Ihre Fähigkeiten zur Datenabfrage und -filterung zu verbessern. Sie haben geübt, Subqueries innerhalb der WHERE-Klausel zu verwenden, sie in die SELECT-Anweisung einzubetten und korrelierte Subqueries zu erstellen. Sie haben auch gelernt, wie Sie Subqueries mithilfe von JOIN-Operationen umschreiben können, um eine bessere Effizienz zu erzielen. Diese Techniken bieten Ihnen leistungsstarke Werkzeuge für die Arbeit mit Daten in SQLite.