Viele Tabellen und eine Tabelle mit sich selbst verbunden

Kurz gesagt: Mehrere JOINs hintereinander verknüpfen eine Kette von Tabellen, und ein Self Join verknüpft eine Tabelle mit sich selbst, zum Beispiel eine Kategorie mit ihrer Elternkategorie.

Problem

Umsatz nach Kategorie

Die Inhaberin will wissen, aus welchen Kategorien das Geld kommt. Menge und Preis stehen in order_items. Ob die Bestellung storniert wurde, steht in orders. Die Kategorie des Produkts steht in products und der Name der Kategorie in categories.

Vier Tabellen für einen Bericht. JOIN hört nicht bei zwei auf.

Die Idee

Eine Kette von JOINs

Jeder weitere JOIN fügt dem, was schon zusammengesetzt ist, eine Tabelle hinzu. Bestellung 60 mit ihren Produkten und deren Kategorien:

SQLDatenbank: Shop

SELECT o.id, p.name AS product, cat.name AS category, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.id = 60;

SELECT o.id, p.name AS product, cat.name AS category, oi.quantity FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.id = 60;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Der Weg folgt den Fremdschlüsseln: orders → order_items (über order_id) → products (über product_id) → categories (über category_id). Jedes Glied ist ein eigener JOIN … ON.

Sowohl products als auch categories haben eine Spalte name. Deshalb benennen wir sie hier mit AS: product und category. Sonst hätte das Ergebnis zwei Spalten name.

Probier es aus

Wer hat was bewertet

Zeige für jede Bewertung den Namen des Kunden, den Namen des Produkts und die Note.

SQLDatenbank: Shop

SELECT r.customer_id, r.product_id, r.rating
FROM reviews r;

SELECT r.customer_id, r.product_id, r.rating FROM reviews r;

Prüfen▶ Ausführen

Sieh es dir an

Das verlorene Geld

Umsatz nach Kategorie, ohne die stornierten Bestellungen:

SQLDatenbank: Shop

SELECT cat.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.name
ORDER BY revenue DESC;

SELECT cat.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled' GROUP BY cat.name ORDER BY revenue DESC;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Sieht gut aus. Jetzt der gesamte Umsatz, ohne Kategorien:

SQLDatenbank: Shop

SELECT SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled';

SELECT SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.status <> 'cancelled';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die sechs Kategorien ergeben 1.537 €, aber der Umsatz beträgt 1.697 €.

1. Die Kategorien ergeben zusammen 1.537 €, aber der gesamte Umsatz beträgt 1.697 €. Wo sind die 160 €?

Ein RundungsfehlerIn den stornierten BestellungenIm Verkauf der Geschenkkarten: Die Geschenkkarte hat keine Kategorie, und JOIN categories hat sie hinausgeworfen

Prüfen

In einer Kette von JOINs kann jedes Glied unbemerkt Zeilen hinauswerfen. Für ein Produkt ohne Kategorie gibt es in categories keinen Partner. Mit einem LEFT JOIN zu den Kategorien bleiben die Verkäufe, und COALESCE gibt der fehlenden Kategorie einen Namen:

SQLDatenbank: Shop

SELECT COALESCE(cat.name, 'No category') AS category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
LEFT JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.name
ORDER BY revenue DESC;

SELECT COALESCE(cat.name, 'No category') AS category, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id LEFT JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled' GROUP BY cat.name ORDER BY revenue DESC;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Sieben Zeilen, und die Summe ist 1.697 €. Wann immer ein Bericht Geld zählt, vergleiche seine Summe mit der Gesamtsumme. Der Unterschied zeigt die verlorenen Zeilen.

Probier es aus

Umsatz nach Stadt

Zeige den Umsatz nach Stadt des Kunden, ohne die stornierten Bestellungen.

SQLDatenbank: Shop

Prüfen▶ Ausführen

Die Idee

Eine Tabelle mit sich selbst verbunden

In den Kategorien verweist parent_id auf id in derselben Tabelle. Um eine Kategorie neben ihrer Elternkategorie zu sehen, verbindest du categories mit sich selbst. Die Tabelle kommt zweimal vor, mit zwei verschiedenen kurzen Namen:

SQLDatenbank: Shop

SELECT c.name AS category, parent.name AS parent
FROM categories c
LEFT JOIN categories parent ON parent.id = c.parent_id;

SELECT c.name AS category, parent.name AS parent FROM categories c LEFT JOIN categories parent ON parent.id = c.parent_id;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

c ist die Kategorie, parent ist dieselbe Tabelle, gesehen als Elternteil. ON parent.id = c.parent_id sagt: Das Elternteil ist die Zeile, deren id gleich der parent_id der Kategorie ist. Das heißt Self Join. Hier sind die kurzen Namen Pflicht: Ohne sie lässt sich name aus den beiden Exemplaren nicht unterscheiden.

Es ist ein LEFT JOIN, damit „Lebensmittel“ und „Haushalt“, die kein Elternteil haben, bleiben.

Übung

Zwei Aufgaben

1. Der neue Katalog braucht alle 25 Produkte mit ihrer Kategorie und der Elternkategorie. Die Geschenkkarte muss auch auf der Liste stehen.

Das Produkt, seine Kategorie und die Elternkategorie, für alle Produkte:

SQLDatenbank: Shop

SELECT p.name AS product, cat.name AS category
FROM products p
JOIN categories cat ON cat.id = p.category_id;

SELECT p.name AS product, cat.name AS category FROM products p JOIN categories cat ON cat.id = p.category_id;

Prüfen▶ Ausführen

2. Wie hoch ist der Umsatz der zwei obersten Kategorien, „Lebensmittel“ und „Haushalt“? Ohne die stornierten Bestellungen.

Umsatz nach oberster Kategorie:

SQLDatenbank: Shop

Prüfen▶ Ausführen

Genauer, für Neugierige

Die Reihenfolge einfacher JOINs in einer Abfrage ändert das Ergebnis nicht: Die Datenbank wählt selbst, mit welcher Tabelle sie beginnt. Bei LEFT JOIN zählt die Reihenfolge, denn „links“ ist alles, was davor zusammengesetzt wurde.

Eine Tabelle mit sich selbst verbunden ergibt nur eine Ebene nach oben. Ein Baum mit vielen Ebenen (eine Unterkategorie einer Unterkategorie) wird mit WITH RECURSIVE durchlaufen, im Modul über Unterabfragen.

Test

Teste dich

1. Eine Abfrage verbindet orders, order_items, products und categories. Wie viele JOIN … ON hat sie?

VierDreiZwei

2. Warum sind die kurzen Namen in FROM categories c LEFT JOIN categories parent … nötig?

Sie sind nicht nötigWeil LEFT JOIN sie verlangtWeil dieselbe Tabelle zweimal vorkommt und man ohne sie c.name und parent.name nicht unterscheiden kann

3. Ein Bericht nach Kategorie mit JOIN categories verliert die Produkte ohne Kategorie. Wie behältst du sie?

Mit ORDER BYMit WHERE category_id IS NOT NULLMit LEFT JOIN categories und COALESCE für den Namen

4. Das Ergebnis hat zwei Spalten name (die des Kunden und die des Produkts). Was tust du?

Du entfernst eineDu benennst sie mit AS: customer, productNichts, die Datenbank unterscheidet sie

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: GROUP BY: ein Bericht nach Gruppen · Primärschlüssel und Fremdschlüssel · JOIN: zwei Tabellen in einer Abfrage · LEFT JOIN: auch Zeilen ohne Partner
• Geht weiter in: UNION, INTERSECT, EXCEPT und CROSS JOIN · Eine Auswertung in Schritten: WITH · Bäume und Folgen: WITH RECURSIVE · Views
• Aufgaben zum Thema: Tabellen verbinden