PostgreSQL Beziehungen und Joins

Einführung

In diesem Lab werden Sie Beziehungen und Joins in PostgreSQL untersuchen. Sie lernen, wie Sie Tabellen mit Fremdschlüsselbeschränkungen (foreign key constraints) erstellen, um die Datenintegrität sicherzustellen.

Sie beginnen mit der Erstellung von zwei Tabellen, customers (Kunden) und orders (Bestellungen), und richten eine Fremdschlüsselbeziehung zwischen ihnen ein. Anschließend fügen Sie Daten in diese Tabellen ein und stellen sicher, dass die Daten die definierte Beziehung einhalten. Abschließend lernen Sie, wie Sie Daten mit INNER JOIN abrufen und die Ergebnisse von LEFT, RIGHT und FULL OUTER JOIN Operationen vergleichen, um zu verstehen, wie diese unterschiedliche Datenbeziehungen behandeln.

Skills Graph

Tabellen mit Fremdschlüsselbeschränkungen erstellen

In diesem Schritt erstellen Sie zwei Tabellen, customers (Kunden) und orders (Bestellungen), und richten eine Fremdschlüsselbeschränkung (foreign key constraint) zwischen ihnen ein. Diese Beschränkung stellt sicher, dass die Beziehung zwischen den Tabellen aufrechterhalten wird und verhindert, dass ungültige Daten eingegeben werden.

Grundlegendes zu Fremdschlüsseln

Ein Fremdschlüssel (foreign key) ist eine Spalte in einer Tabelle, die auf den Primärschlüssel (primary key) einer anderen Tabelle verweist. Er stellt eine Verbindung zwischen den beiden Tabellen her. Die Tabelle, die den Fremdschlüssel enthält, wird als "Kind"-Tabelle (child table) bezeichnet, und die Tabelle, die den Primärschlüssel enthält, wird als "Eltern"-Tabelle (parent table) bezeichnet.

Schritt 1: Mit PostgreSQL verbinden

Öffnen Sie ein Terminal in Ihrer LabEx VM. Stellen Sie mit dem Befehl psql eine Verbindung zur PostgreSQL-Datenbank her:

sudo -u postgres psql

Sie sollten nun die PostgreSQL-Eingabeaufforderung (postgres=#) sehen.

Schritt 2: Die Tabelle customers erstellen

Erstellen Sie die Tabelle customers mit den folgenden Spalten:

• customer_id: Eine eindeutige Kennung für jeden Kunden (Primärschlüssel).
• first_name: Der Vorname des Kunden.
• last_name: Der Nachname des Kunden.
• email: Die E-Mail-Adresse des Kunden (muss eindeutig sein).

Führen Sie den folgenden SQL-Befehl in der psql-Shell aus:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

Dieser Befehl erstellt die Tabelle customers. Das Schlüsselwort SERIAL generiert automatisch eine Zahlenfolge für die customer_id, wodurch sie automatisch inkrementiert wird. PRIMARY KEY kennzeichnet customer_id als Primärschlüssel. NOT NULL stellt sicher, dass die Spalten first_name und last_name nicht leer sein dürfen, und UNIQUE stellt sicher, dass jede E-Mail-Adresse eindeutig ist.

Schritt 3: Die Tabelle orders mit einem Fremdschlüssel erstellen

Erstellen Sie die Tabelle orders mit den folgenden Spalten:

• order_id: Eine eindeutige Kennung für jede Bestellung (Primärschlüssel).
• customer_id: Die ID des Kunden, der die Bestellung aufgegeben hat (Fremdschlüssel, der auf customers verweist).
• order_date: Das Datum, an dem die Bestellung aufgegeben wurde.
• total_amount: Der Gesamtbetrag der Bestellung.

Führen Sie den folgenden SQL-Befehl in der psql-Shell aus:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Dieser Befehl erstellt die Tabelle orders. Die Spalte customer_id wird mit dem Schlüsselwort REFERENCES als Fremdschlüssel definiert. REFERENCES customers(customer_id) gibt an, dass die Spalte customer_id in der Tabelle orders auf die Spalte customer_id in der Tabelle customers verweist. Dadurch wird die Fremdschlüsselbeziehung hergestellt.

Schritt 4: Tabellenerstellung überprüfen

Überprüfen Sie, ob die Tabellen erfolgreich erstellt wurden, indem Sie die Tabellen in der Datenbank auflisten. Führen Sie den folgenden Befehl in der psql-Shell aus:

\dt

Sie sollten die Tabellen customers und orders aufgelistet sehen.

Schritt 5: Die Tabellen beschreiben

Um die Struktur der Tabellen anzuzeigen, verwenden Sie den Befehl \d, gefolgt vom Tabellennamen. Um beispielsweise die Tabelle customers zu beschreiben, führen Sie Folgendes aus:

\d customers

Dadurch werden Ihnen die Spalten, Datentypen und Beschränkungen angezeigt, die für die Tabelle customers definiert sind. Ebenso können Sie die Tabelle orders beschreiben:

\d orders

Dadurch wird Ihnen die Fremdschlüsselbeschränkung für die Spalte customer_id angezeigt.

Sie haben nun erfolgreich zwei Tabellen mit einer Fremdschlüsselbeschränkung erstellt.

Daten einfügen und referenzielle Integrität erzwingen

In diesem Schritt fügen Sie Daten in die Tabellen customers (Kunden) und orders (Bestellungen) ein und stellen sicher, dass die referenzielle Integrität (referential integrity) gewahrt bleibt. Dies bedeutet, dass Sie keine Bestellung für einen Kunden hinzufügen können, der in der Tabelle customers nicht existiert.

Grundlegendes zur referenziellen Integrität

Die referenzielle Integrität stellt sicher, dass die Beziehungen zwischen Tabellen konsistent bleiben. In unserem Fall bedeutet dies, dass die customer_id in der Tabelle orders in der Tabelle customers vorhanden sein muss.

Schritt 1: Daten in die Tabelle customers einfügen

Fügen Sie Daten in die Tabelle customers ein, indem Sie den folgenden SQL-Befehl in der psql-Shell ausführen:

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]'),
('David', 'Lee', '[email protected]');

Dieser Befehl fügt der Tabelle customers drei Kunden hinzu. Die customer_id wird automatisch generiert.

Schritt 2: Dateninsertion in die Tabelle customers überprüfen

Überprüfen Sie, ob die Daten korrekt eingefügt wurden, indem Sie die Tabelle customers abfragen:

SELECT * FROM customers;

Sie sollten die drei Kunden sehen, die Sie gerade eingefügt haben, zusammen mit ihren automatisch generierten customer_id-Werten. Die Ausgabe sollte in etwa so aussehen:

 customer_id | first_name | last_name |         email
-------------+------------+-----------+------------------------
           1 | John       | Doe       | [email protected]
           2 | Jane       | Smith     | [email protected]
           3 | David      | Lee       | [email protected]
(3 rows)

Schritt 3: Daten in die Tabelle orders einfügen

Fügen Sie Daten in die Tabelle orders ein und verweisen Sie auf die customer_id-Werte aus der Tabelle customers:

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-11-01', 100.00),
(2, '2023-11-05', 250.50),
(1, '2023-11-10', 75.25),
(3, '2023-11-15', 120.00);

Dieser Befehl fügt der Tabelle orders vier Bestellungen hinzu. Jede Bestellung ist einer customer_id aus der Tabelle customers zugeordnet.

Schritt 4: Dateninsertion in die Tabelle orders überprüfen

Überprüfen Sie, ob die Daten korrekt eingefügt wurden, indem Sie die Tabelle orders abfragen:

SELECT * FROM orders;

Sie sollten die vier Bestellungen sehen, die Sie gerade eingefügt haben. Die Ausgabe sollte in etwa so aussehen:

 order_id | customer_id | order_date | total_amount
----------+-------------+------------+--------------
        1 |           1 | 2023-11-01 |       100.00
        2 |           2 | 2023-11-05 |       250.50
        3 |           1 | 2023-11-10 |        75.25
        4 |           3 | 2023-11-15 |       120.00
(4 rows)

Schritt 5: Versuch, ungültige Daten einzufügen (Demonstration der referenziellen Integrität)

Um die Fremdschlüsselbeschränkung (foreign key constraint) zu demonstrieren, versuchen Sie, eine Bestellung mit einer customer_id einzufügen, die in der Tabelle customers nicht existiert:

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(4, '2023-11-20', 50.00);

Sie sollten eine Fehlermeldung ähnlich dieser sehen:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(4) is not present in table "customers".

Diese Fehlermeldung bestätigt, dass die Fremdschlüsselbeschränkung funktioniert. Die Datenbank verhindert das Einfügen der Bestellung, da customer_id 4 in der Tabelle customers nicht existiert.

Sie haben nun erfolgreich Daten in die Tabellen customers und orders eingefügt und dabei die referenzielle Integrität sichergestellt.

Daten mit INNER JOIN abfragen

In diesem Schritt lernen Sie, wie Sie Daten aus mehreren Tabellen mit der INNER JOIN-Klausel in PostgreSQL abrufen. INNER JOIN kombiniert Zeilen aus zwei oder mehr Tabellen basierend auf einer zugehörigen Spalte.

Grundlegendes zu INNER JOIN

Ein INNER JOIN gibt nur die Zeilen zurück, in denen es in beiden Tabellen, die verknüpft werden, eine Übereinstimmung gibt. Wenn keine Übereinstimmung vorhanden ist, wird die Zeile aus dem Ergebnis ausgeschlossen.

Schritt 1: Mit PostgreSQL verbinden

Stellen Sie sicher, dass Sie mit dem Befehl psql mit der PostgreSQL-Datenbank verbunden sind:

sudo -u postgres psql

Schritt 2: Die INNER JOIN-Abfrage ausführen

Führen Sie die folgende SQL-Abfrage in der psql-Shell aus:

SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Lassen Sie uns diese Abfrage aufschlüsseln:

• SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount: Dies gibt die Spalten an, die Sie aus den Tabellen orders und customers abrufen möchten. Die Verwendung des Tabellennamens als Präfix (z. B. orders.order_id) verdeutlicht, aus welcher Tabelle jede Spalte stammt.
• FROM orders: Dies gibt die erste Tabelle an, aus der Sie abfragen.
• INNER JOIN customers ON orders.customer_id = customers.customer_id: Dies gibt die zweite Tabelle an, mit der Sie eine Verknüpfung herstellen (customers), sowie die Verknüpfungsbedingung (orders.customer_id = customers.customer_id). Die ON-Klausel gibt an, dass die customer_id in der Tabelle orders mit der customer_id in der Tabelle customers übereinstimmen muss, damit Zeilen einbezogen werden.

Schritt 3: Die Ergebnisse analysieren

Die Abfrage gibt ein Resultset (Ergebnismenge) zurück, das die Bestell-ID, den Vornamen des Kunden, das Bestelldatum und den Gesamtbetrag für jede Bestellung enthält. Die Ausgabe sollte in etwa so aussehen:

 order_id | first_name | order_date | total_amount
----------+------------+------------+--------------
        1 | John       | 2023-11-01 |       100.00
        3 | John       | 2023-11-10 |        75.25
        2 | Jane       | 2023-11-05 |       250.50
        4 | David      | 2023-11-15 |       120.00
(4 rows)

Die Abfrage hat die Tabellen orders und customers erfolgreich basierend auf der customer_id verknüpft und die angeforderten Informationen abgerufen. Es werden nur Bestellungen mit einem entsprechenden Kunden in der Tabelle customers berücksichtigt.

Schritt 4: Verwenden von Aliasen (optional)

Für komplexere Abfragen können Sie Aliase verwenden, um die Abfrage lesbarer zu machen. Die vorherige Abfrage kann mit Aliasen umgeschrieben werden:

SELECT o.order_id, c.first_name, o.order_date, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

In dieser Abfrage ist o ein Alias für orders und c ein Alias für customers. Das Ergebnis ist das gleiche, aber die Abfrage ist prägnanter.

Sie haben nun erfolgreich Daten aus mehreren Tabellen mit der INNER JOIN-Klausel abgefragt.

LEFT, RIGHT und FULL OUTER JOIN Ergebnisse vergleichen

In diesem Schritt werden Sie die Ergebnisse von LEFT OUTER JOIN, RIGHT OUTER JOIN und FULL OUTER JOIN in PostgreSQL untersuchen und vergleichen. Diese Joins (Verknüpfungen) rufen alle Zeilen aus einer oder beiden Tabellen ab, auch wenn in der anderen Tabelle keine übereinstimmenden Werte vorhanden sind.

Grundlegendes zu OUTER JOINs

• LEFT OUTER JOIN (oder LEFT 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 für die Spalten der rechten Tabelle NULL-Werte zurückgegeben.
• RIGHT OUTER JOIN (oder RIGHT JOIN): Gibt alle Zeilen aus der rechten Tabelle und die übereinstimmenden Zeilen aus der linken Tabelle zurück. Wenn in der linken Tabelle keine Übereinstimmung vorhanden ist, werden für die Spalten der linken Tabelle NULL-Werte zurückgegeben.
• FULL OUTER JOIN (oder FULL JOIN): Gibt alle Zeilen aus beiden Tabellen zurück. Wenn in einer Tabelle keine Übereinstimmung vorhanden ist, werden für die Spalten der anderen Tabelle NULL-Werte zurückgegeben.

Schritt 1: Mit PostgreSQL verbinden

Stellen Sie sicher, dass Sie mit dem Befehl psql mit der PostgreSQL-Datenbank verbunden sind:

sudo -u postgres psql

Schritt 2: Einen neuen Kunden ohne Bestellungen einfügen

Fügen Sie einen neuen Kunden in die Tabelle customers ein, der noch keine Bestellungen aufgegeben hat:

INSERT INTO customers (first_name, last_name, email) VALUES
('Alice', 'Brown', '[email protected]');

Schritt 3: Den neuen Kunden überprüfen

Überprüfen Sie, ob der neue Kunde zur Tabelle customers hinzugefügt wurde:

SELECT * FROM customers;

Sie sollten Alice Brown in den Ergebnissen sehen, mit einer neuen customer_id (wahrscheinlich 4).

Schritt 4: Einen LEFT OUTER JOIN durchführen

Führen Sie die folgende SQL-Abfrage aus, um einen LEFT OUTER JOIN zwischen den Tabellen customers und orders durchzuführen:

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Diese Abfrage gibt alle Kunden zusammen mit allen Bestellungen zurück, die sie aufgegeben haben. Wenn ein Kunde keine Bestellungen aufgegeben hat, enthalten die Spalten order_id und order_date NULL-Werte. Die Ausgabe sollte in etwa so aussehen:

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 John       |        3 | 2023-11-10
 Jane       |        2 | 2023-11-05
 David      |        4 | 2023-11-15
 Alice      |          |
(5 rows)

Beachten Sie, dass Alice Brown enthalten ist, obwohl sie keine Bestellungen aufgegeben hat. Die Spalten order_id und order_date sind für sie NULL.

Schritt 5: Einen RIGHT OUTER JOIN durchführen

Führen Sie die folgende SQL-Abfrage aus, um einen RIGHT OUTER JOIN zwischen den Tabellen customers und orders durchzuführen:

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Diese Abfrage gibt alle Bestellungen zusammen mit dem Vornamen des Kunden zurück, der jede Bestellung aufgegeben hat. Da jede Bestellung einen entsprechenden Kunden hat, ist das Ergebnis in diesem Fall das gleiche wie bei einem INNER JOIN. Die Ausgabe sollte in etwa so aussehen:

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 Jane       |        2 | 2023-11-05
 John       |        3 | 2023-11-10
 David      |        4 | 2023-11-15
(4 rows)

Schritt 6: Einen FULL OUTER JOIN durchführen

Führen Sie die folgende SQL-Abfrage aus, um einen FULL OUTER JOIN zwischen den Tabellen customers und orders durchzuführen:

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Diese Abfrage gibt alle Kunden und alle Bestellungen zurück. Wenn ein Kunde keine Bestellungen aufgegeben hat, enthalten die Spalten order_id und order_date NULL-Werte. Wenn eine Bestellung keinen entsprechenden Kunden hat (was in unserem aktuellen Setup aufgrund der Fremdschlüsselbeschränkung nicht möglich ist), würde die Spalte first_name NULL-Werte enthalten. Die Ausgabe sollte in etwa so aussehen:

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 John       |        3 | 2023-11-10
 Jane       |        2 | 2023-11-05
 David      |        4 | 2023-11-15
 Alice      |          |
(5 rows)

Beachten Sie, dass Alice Brown mit NULL-Werten für order_id und order_date enthalten ist.

Schritt 7: Die Unterschiede verstehen

• LEFT OUTER JOIN enthält alle Zeilen aus der Tabelle customers, auch wenn keine übereinstimmenden Bestellungen vorhanden sind.
• RIGHT OUTER JOIN enthält alle Zeilen aus der Tabelle orders. In unserem Fall verhält es sich wie ein INNER JOIN, da alle Bestellungen einen entsprechenden Kunden haben.
• FULL OUTER JOIN enthält alle Zeilen aus beiden Tabellen.

Sie haben nun die Ergebnisse von LEFT OUTER JOIN, RIGHT OUTER JOIN und FULL OUTER JOIN in PostgreSQL untersucht und verglichen.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie in PostgreSQL Tabellen mit Fremdschlüsselbeschränkungen (foreign key constraints) erstellen, um die Datenintegrität (data integrity) zu gewährleisten. Sie haben die Tabellen customers und orders erstellt und eine Beziehung zwischen ihnen mithilfe eines Fremdschlüssels (foreign key) aufgebaut. Anschließend haben Sie Daten in diese Tabellen eingefügt und dabei sichergestellt, dass die Fremdschlüsselbeschränkung (foreign key constraint) durchgesetzt wird. Abschließend haben Sie verschiedene Arten von JOIN-Operationen (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN und FULL OUTER JOIN) untersucht, um Daten aus verwandten Tabellen abzurufen und zu verstehen, wie diese unterschiedliche Datenbeziehungen handhaben.