10 Tabellen verbinden

In relationalen Datenbanken sind Daten oft auf mehrere Tabellen verteilt, um Redundanz zu vermeiden und die Datenintegrität zu gewährleisten. Um die Daten sinnvoll auswerten zu können, müssen die Tabellen miteinander verknüpft werden. Hierfür gibt es in SQL die JOIN-Anweisung.

10.1 Primär-Fremdschlüsselpaare

Die Verbindung von Tabellen basiert häufig auf Primär-Fremdschlüssel-Beziehungen. Ein Primärschlüssel ist ein eindeutiger Identifikator für jeden Datensatz in einer Tabelle. Ein Fremdschlüssel in einer anderen Tabelle verweist auf diesen Primärschlüssel und stellt so eine Beziehung zwischen den beiden Tabellen her.

Hier ein typisches Beispiel:

-- Primärschlüsseltabelle
CREATE TABLE abteilungen (
    abteilung_id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Fremdschlüsseltabelle
CREATE TABLE mitarbeiter (
    mitarbeiter_id INT PRIMARY KEY,
    name VARCHAR(100),
    abteilung_id INT,
    FOREIGN KEY (abteilung_id) 
    REFERENCES abteilungen(abteilung_id)
);

In diesem Beispiel ist abteilung_id der Primärschlüssel in der Tabelle abteilungen. Die Tabelle mitarbeiter enthält eine Fremdschlüsselspalte abteilung_id, die auf den Primärschlüssel in abteilungen verweist. Dadurch wird eine Beziehung zwischen Mitarbeitern und Abteilungen hergestellt.

10.2 INNER JOIN zwischen zwei Tabellen

10.2.1 Bauanleitung für einen INNER JOIN

Der INNER JOIN verbindet nur Datensätze, die in beiden Tabellen vorhanden sind. Das bedeutet, nur Mitarbeiter, die einer Abteilung zugeordnet sind und nur Abteilungen, denen Mitarbeiter zugeordnet sind, werden im Ergebnis angezeigt.

-- Grundlegende Syntax
SELECT m.name, a.name AS abteilung
FROM mitarbeiter m
INNER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id;

-- Mit weiteren Bedingungen
SELECT m.name, a.name AS abteilung
FROM mitarbeiter m
INNER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id
WHERE a.name = 'Vertrieb';

-- Mit Sortierung
SELECT m.name, a.name AS abteilung
FROM mitarbeiter m
INNER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id
ORDER BY a.name, m.name;

Der INNER JOIN wird mit der Klausel ON definiert, die angibt, welche Spalten für die Verknüpfung verwendet werden sollen. In diesem Fall wird der Fremdschlüssel abteilung_id aus der Tabelle mitarbeiter mit dem Primärschlüssel abteilung_id aus der Tabelle abteilungen verknüpft.

10.2.2 Abkürzende Schreibweisen

Es gibt verschiedene Möglichkeiten, JOINs zu schreiben. Die Standardschreibweise mit explizitem INNER JOIN und ON-Klausel ist am übersichtlichsten und wird daher empfohlen.

-- Standardschreibweise (empfohlen)
SELECT m.name, a.name
FROM mitarbeiter m
INNER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id;

-- Verkürzt mit USING (nur bei gleichen Spaltennamen)
SELECT m.name, a.name
FROM mitarbeiter m
INNER JOIN abteilungen a 
    USING (abteilung_id);

-- Alte Schreibweise (nicht empfohlen)
SELECT m.name, a.name
FROM mitarbeiter m, abteilungen a
WHERE m.abteilung_id = a.abteilung_id;

Die verkürzte Schreibweise mit USING kann verwendet werden, wenn die Spaltennamen in beiden Tabellen identisch sind. Die alte Schreibweise ohne expliziten JOIN ist weniger übersichtlich und gilt als veraltet.

10.2.3 Als Datenquelle für temporäre Tabellen

JOINs können auch für temporäre Tabellen verwendet werden. Eine temporäre Tabelle ist eine Tabelle, die nur für die Dauer einer Sitzung oder Transaktion existiert. Sie kann verwendet werden, um Zwischenergebnisse zu speichern oder komplexe Abfragen zu vereinfachen.

-- Temporäre Tabelle aus JOIN erstellen
CREATE TEMPORARY TABLE mitarbeiter_details
SELECT 
    m.mitarbeiter_id,
    m.name,
    a.name AS abteilung,
    g.bezeichnung AS gehaltsstufe
FROM mitarbeiter m
INNER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id
INNER JOIN gehaltsstufen g 
    ON m.gehaltsstufe_id = g.gehaltsstufe_id;

In diesem Beispiel wird eine temporäre Tabelle mitarbeiter_details erstellt, die Daten aus den Tabellen mitarbeiter, abteilungen und gehaltsstufen kombiniert. Die temporäre Tabelle kann dann für weitere Abfragen verwendet werden.

10.2.4 JOIN über Nichtschlüsselspalten

JOINs können auch über andere Spalten als Primär- und Fremdschlüssel erfolgen. Beispielsweise können Kunden anhand ihrer Postleitzahl verknüpft werden oder Bestellungen anhand eines Datums mit Aktionen.

-- JOIN über Namen
SELECT k1.name, k2.name AS partner
FROM kunden k1
INNER JOIN kunden k2 
    ON k1.plz = k2.plz
    AND k1.kunde_id < k2.kunde_id;

-- JOIN über Datumsbereich
SELECT b.bestellnummer, p.aktion
FROM bestellungen b
INNER JOIN promotion p 
    ON b.datum BETWEEN p.start_datum AND p.end_datum;

Im ersten Beispiel werden Kunden mit gleicher Postleitzahl verknüpft, wobei jeder Kunde nur einmal als “partner” auftauchen soll (daher die zusätzliche Bedingung k1.kunde_id < k2.kunde_id).

Im zweiten Beispiel werden Bestellungen mit Aktionen verknüpft, die zu diesem Zeitpunkt gültig waren. Die Bedingung b.datum BETWEEN p.start_datum AND p.end_datum prüft, ob das Bestelldatum innerhalb des Gültigkeitszeitraums der Aktion liegt.

10.3 INNER JOIN über mehr als zwei Tabellen

Komplexere Abfragen können mehrere Tabellen verbinden. Dabei werden einfach mehrere JOIN-Anweisungen hintereinander geschrieben.

-- Drei Tabellen verbinden
SELECT 
    b.bestellnummer,
    k.name AS kunde,
    p.bezeichnung AS produkt
FROM bestellungen b
INNER JOIN kunden k 
    ON b.kunde_id = k.kunde_id
INNER JOIN bestellpositionen bp 
    ON b.bestell_id = bp.bestell_id
INNER JOIN produkte p 
    ON bp.produkt_id = p.produkt_id;

-- Mit Aggregation
SELECT 
    k.name AS kunde,
    a.name AS abteilung,
    COUNT(*) AS anzahl_bestellungen,
    SUM(b.betrag) AS gesamtumsatz
FROM bestellungen b
INNER JOIN kunden k 
    ON b.kunde_id = k.kunde_id
INNER JOIN abteilungen a 
    ON k.abteilung_id = a.abteilung_id
GROUP BY k.kunde_id, a.abteilung_id;

Im ersten Beispiel werden die Tabellen bestellungen, kunden, bestellpositionen und produkte verknüpft, um für jede Bestellung den Kundennamen und das bestellte Produkt anzuzeigen.

Im zweiten Beispiel werden Bestellungen nach Kunde und Abteilung gruppiert und aggregiert. Für jeden Kunden wird die Anzahl der Bestellungen und der Gesamtumsatz berechnet.

10.4 OUTER JOIN

Im Gegensatz zum INNER JOIN, der nur Datensätze anzeigt, die in beiden Tabellen vorhanden sind, zeigt der OUTER JOIN auch Datensätze an, die in einer der beiden Tabellen keine Entsprechung haben. Es gibt zwei Arten von OUTER JOINs:

• LEFT OUTER JOIN: Zeigt alle Datensätze aus der linken Tabelle an, auch wenn es keine Entsprechung in der rechten Tabelle gibt.
• RIGHT OUTER JOIN: Zeigt alle Datensätze aus der rechten Tabelle an, auch wenn es keine Entsprechung in der linken Tabelle gibt.

-- LEFT OUTER JOIN
SELECT m.name, a.name AS abteilung
FROM mitarbeiter m
LEFT OUTER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id;

-- RIGHT OUTER JOIN
SELECT m.name, a.name AS abteilung
FROM mitarbeiter m
RIGHT OUTER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id;

-- Fehlende Zuordnungen finden
SELECT m.name, a.name AS abteilung
FROM mitarbeiter m
LEFT OUTER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id
WHERE a.abteilung_id IS NULL;

Im ersten Beispiel werden alle Mitarbeiter angezeigt, auch wenn sie keiner Abteilung zugeordnet sind (die Abteilung ist dann NULL).

Im zweiten Beispiel werden alle Abteilungen angezeigt, auch wenn ihnen keine Mitarbeiter zugeordnet sind (der Mitarbeitername ist dann NULL).

Im dritten Beispiel werden mit Hilfe eines LEFT OUTER JOIN und einer zusätzlichen WHERE-Bedingung alle Mitarbeiter gesucht, die keiner Abteilung zugeordnet sind.

10.5 SELF JOIN

Eine Tabelle kann auch mit sich selbst verbunden werden. Dies wird als SELF JOIN bezeichnet und ist nützlich, um Beziehungen innerhalb einer Tabelle darzustellen, wie z.B. hierarchische Strukturen.

-- Mitarbeiter und ihre Vorgesetzten
SELECT 
    m1.name AS mitarbeiter,
    m2.name AS vorgesetzter
FROM mitarbeiter m1
LEFT JOIN mitarbeiter m2 
    ON m1.vorgesetzter_id = m2.mitarbeiter_id;

-- Hierarchische Strukturen
SELECT 
    k1.kategorie AS hauptkategorie,
    k2.kategorie AS unterkategorie
FROM kategorien k1
LEFT JOIN kategorien k2 
    ON k2.parent_id = k1.kategorie_id
WHERE k1.parent_id IS NULL;

Im ersten Beispiel wird die Tabelle mitarbeiter mit sich selbst verknüpft, um für jeden Mitarbeiter seinen Vorgesetzten anzuzeigen. Der LEFT JOIN stellt sicher, dass auch Mitarbeiter angezeigt werden, die keinen Vorgesetzten haben (der Vorgesetzte ist dann NULL).

Im zweiten Beispiel wird eine hierarchische Struktur von Kategorien abgebildet. Jede Kategorie kann eine Oberkategorie (parent_id) haben. Der SELF JOIN verbindet jede Kategorie mit ihrer Oberkategorie. Die WHERE-Bedingung filtert nur die obersten Kategorien (die keine Oberkategorie haben).

10.6 Eine Verknüpfung beschleunigen

JOINs können bei großen Tabellen zu Performanceproblemen führen. Hier sind einige Tipps zur Optimierung:

-- Indizes für JOIN-Spalten
CREATE INDEX idx_abteilung 
ON mitarbeiter(abteilung_id);

-- Zusammengesetzter Index für mehrere Bedingungen
CREATE INDEX idx_bestellung 
ON bestellungen(kunde_id, datum);

-- Temporäre Tabellen für Zwischenergebnisse
CREATE TEMPORARY TABLE temp_umsatz AS
SELECT 
    kunde_id,
    SUM(betrag) AS gesamtumsatz
FROM bestellungen
GROUP BY kunde_id;

• Erstellen Sie Indizes für die Spalten, die in JOIN-Bedingungen verwendet werden. Indizes beschleunigen die Suche nach übereinstimmenden Werten.
• Verwenden Sie zusammengesetzte Indizes, wenn mehrere Spalten in der JOIN-Bedingung verwendet werden.
• Verwenden Sie temporäre Tabellen, um Zwischenergebnisse zu speichern, insbesondere bei Aggregationen. Dadurch müssen die Originaltabellen nicht mehrfach durchsucht werden.

Praktische Tipps für JOINs:

1. Verwenden Sie aussagekräftige Aliase:

-- Gut
SELECT m.name, a.name AS abteilung
FROM mitarbeiter m
INNER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id;

Aliase wie m für mitarbeiter und a für abteilungen machen die Abfrage übersichtlicher. Verwenden Sie immer Aliase, insbesondere bei mehreren Tabellen.

2. Vermeiden Sie unnötige JOINs:

-- Besser: Direkte Abfrage wenn möglich
SELECT name FROM mitarbeiter
WHERE abteilung_id = 5;

-- Statt
SELECT m.name 
FROM mitarbeiter m
INNER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id
WHERE a.abteilung_id = 5;

Wenn Sie nur Daten aus einer Tabelle benötigen und die Bedingung direkt auf diese Tabelle angewendet werden kann, ist ein JOIN überflüssig und verlangsamt nur die Abfrage.

3. Nutzen Sie EXPLAIN zur Analyse:

EXPLAIN SELECT m.name, a.name
FROM mitarbeiter m
INNER JOIN abteilungen a 
    ON m.abteilung_id = a.abteilung_id;

Mit EXPLAIN können Sie sich den Ausführungsplan einer Abfrage anzeigen lassen. So können Sie sehen, wie die Tabellen verknüpft werden und ob Indizes verwendet werden. Dies hilft bei der Optimierung.

4. Beachten Sie die Reihenfolge der Tabellen:

-- Kleine Tabelle zuerst
SELECT m.name, a.name
FROM abteilungen a
INNER JOIN mitarbeiter m 
    ON m.abteilung_id = a.abteilung_id;

Wenn eine der zu verknüpfenden Tabellen deutlich kleiner ist als die andere, sollte diese zuerst in der FROM-Klausel stehen. Die Datenbank optimiert die Abfrage, indem sie für jeden Datensatz in der kleinen Tabelle die passenden Datensätze in der großen Tabelle sucht. Dadurch werden insgesamt weniger Datensätze durchsucht.

JOINs sind ein mächtiges Werkzeug, um Daten aus verschiedenen Tabellen zu kombinieren. Sie ermöglichen es, Beziehungen zwischen Tabellen auszunutzen und komplexe Abfragen zu formulieren. Gleichzeitig erfordern sie aber auch ein gutes Verständnis der Tabellenstruktur und der Zusammenhänge zwischen den Tabellen.

Hier noch einmal die wichtigsten Punkte zusammengefasst:

• JOINs verbinden Tabellen basierend auf einer Bedingung, meist einer Gleichheit von Primär- und Fremdschlüssel.
• INNER JOINs zeigen nur Datensätze an, die in beiden Tabellen vorhanden sind.
• OUTER JOINs (LEFT oder RIGHT) zeigen auch Datensätze an, die in einer der beiden Tabellen keine Entsprechung haben.
• JOINs können über mehrere Tabellen hinweg erfolgen und mit anderen SQL-Klauseln wie WHERE, ORDER BY und GROUP BY kombiniert werden.
• Für eine gute Performanz sollten Indizes auf den verknüpften Spalten erstellt und die Reihenfolge der Tabellen optimiert werden.
• Temporäre Tabellen können helfen, komplexe Abfragen zu vereinfachen und zu beschleunigen.

Mit einem guten Verständnis von JOINs können Sie die volle Leistungsfähigkeit relationaler Datenbanken ausschöpfen und auch komplexe Datenabfragen effizient gestalten. Üben Sie die verschiedenen JOIN-Typen anhand praktischer Beispiele und analysieren Sie bestehende Abfragen, um ein Gefühl für die Möglichkeiten und Grenzen von JOINs zu bekommen.