Einführung in fortgeschrittene Join-Techniken
Die Verwendung von INNER JOIN und LEFT JOIN deckt die meisten SQL-Abfragen ab. Allerdings erfordert eine kleinere Gruppe von Problemen alternative Join-Typen, um beispielsweise zeilenweise Ergebnisse von set-returning Funktionen zu zählen, Zeilen basierend auf der Existenz in einer anderen Tabelle zu filtern oder Zeilen zurückzugeben, die in einer anderen Tabelle keine Übereinstimmung haben.
Drei weniger verbreitete Join-Methoden lösen diese Herausforderungen effizient. LATERAL Joins ermöglichen es einer Unterabfrage in der FROM-Klausel, auf Spalten aus vorhergehenden Tabellen in derselben FROM-Klausel zuzugreifen. Semi Joins geben Zeilen zurück, bei denen eine Übereinstimmung in einer anderen Tabelle besteht, ohne diese Zeilen zu duplizieren. Anti Joins hingegen liefern Zeilen zurück, bei denen keine Übereinstimmung existiert.
Im Folgenden wird erläutert, wie diese Muster in der Praxis angewendet werden können.
LATERAL Joins
Eine LATERAL-Unterabfrage in der FROM-Klausel kann auf Spalten aus vorhergehenden Tabellen in derselben Klausel zugreifen. Ohne LATERAL wird eine Unterabfrage in FROM unabhängig ausgewertet und kann diese Spalten nicht sehen.
Dies ist besonders wichtig, wenn set-returning Funktionen aufgerufen werden, die mehrere Zeilen pro Eingabe zurückgeben. Set-returning Funktionen können in der SELECT-Liste aufgerufen werden, aber um sie zeilenweise auf eine Spalte aus einer äußeren Tabelle innerhalb der FROM-Klausel anzuwenden, ist LATERAL erforderlich.
Typische Anwendungsfälle
- Aufruf von unnest() auf einer Array-Spalte, um eine Zeile pro Array-Element zu erhalten
- Aufruf von regexp_matches() mit dem ‚g‘-Flag, um jedes Match pro Zeile zu extrahieren
- Berechnung eines Top-N-per-Gruppe Ergebnisses mit einer korrelierten Unterabfrage in FROM
- Aufspaltung von JSON-Arrays pro Zeile
Beispiel: Zählen von Wortvorkommen
Eine Beispielabfrage fragt, wie oft die Wörter „bull“ und „bear“ in einer Inhalts-Spalte erscheinen. Übereinstimmungen müssen nicht großgeschrieben werden, und Substrings wie „bullish“ oder „bearing“ sollten ausgeschlossen werden.
Die Daten in der Tabelle google_file_store sind:
- draft1.txt: „Die Börse prognostiziert einen Bullenmarkt, was viele Investoren glücklich machen würde.“
- draft2.txt: „Die Börse prognostiziert einen Bullenmarkt… aber Analysten warnen… wir erwarten einen Bärenmarkt.“
- final.txt: „Die Börse prognostiziert einen Bullenmarkt… einen Bärenmarkt. Wie immer ist die Vorhersage des zukünftigen Marktes unsicher…“
Der Code verwendet regexp_matches(), um einmal pro Zeile von google_file_store zu laufen und alle Übereinstimmungen in der Tabelle zu zählen. Dabei wird LATERAL in der FROM-Klausel verwendet. Die \m und \M-Anker sind PostgreSQL-Wortgrenzen, die „bullish“ und „bearing“ ausschließen.
SELECT 'bull' AS word,
COUNT(*) AS nentry
FROM google_file_store,
LATERAL regexp_matches(LOWER(contents), '\\m(bull)\\M', 'g')
UNION ALL
SELECT 'bear' AS word,
COUNT(*) AS nentry
FROM google_file_store,
LATERAL regexp_matches(LOWER(contents), '\\m(bear)\\M', 'g');Ausgabe
| word | nentry |
|---|---|
| bull | 3 |
| bear | 2 |
Semi Joins
Ein Semi Join gibt Zeilen aus der linken Tabelle zurück, bei denen mindestens eine Übereinstimmung in der rechten Tabelle besteht, wobei jede Zeile der linken Tabelle höchstens einmal erscheint. Ein INNER JOIN dupliziert die Zeilen der linken Tabelle, wenn die rechte Seite mehrere Übereinstimmungen hat. Semi Joins hingegen tun dies nicht.
Implementierungen in SQL
- WHERE EXISTS (SELECT 1 FROM …)
- WHERE col IN (SELECT col FROM …)
EXISTS ist die allgemeinere Form, da sie mehrspaltige Join-Bedingungen und korrelierte Unterabfragen behandelt, ohne die Abfrage umschreiben zu müssen.
Beispiel: Hochwertige Kunden finden
Diese Abfrage sucht nach Kunden, die mindestens eine Bestellung über 100 $ aufgegeben haben, und gibt deren Kunden-ID und Namen zurück.
Die Daten in den Tabellen online_store_customers und online_store_orders sind:
- Kunden-ID: 1, Name: Alice Johnson
- Kunden-ID: 2, Name: Bob Smith
- Kunden-ID: 3, Name: Carol Williams
Der Code verwendet die EXISTS-Unterabfrage, um pro Kunde zu überprüfen, ob eine Bestellung über 100 $ existiert. SELECT 1 ist die Konvention, da EXISTS nur interessiert, ob eine Zeile zurückkommt, nicht was darin steht.
SELECT
c.customer_id,
c.customer_name
FROM online_store_customers c
WHERE EXISTS (
SELECT 1
FROM online_store_orders o
WHERE o.customer_id = c.customer_id
AND o.amount > 100
);Ausgabe
| customer_id | customer_name |
|---|---|
| 1 | Alice Johnson |
| 2 | Bob Smith |
| 3 | Carol Williams |
Anti Joins
Ein Anti Join gibt Zeilen aus der linken Tabelle zurück, bei denen keine Übereinstimmung in der rechten Tabelle existiert. Dies ist das Gegenteil eines Semi Joins.
Implementierungen in SQL
- LEFT JOIN … WHERE right_table.col IS NULL
- WHERE NOT EXISTS (SELECT 1 FROM …)
Beide Methoden liefern dasselbe Ergebnis. NOT EXISTS erzeugt oft einen besseren Abfrageplan in modernen PostgreSQL-Versionen und ist leichter zu lesen. Das Muster LEFT JOIN + IS NULL ist älter und nützlich, wenn auch Spalten von der rechten Seite für nicht übereinstimmende Zeilen benötigt werden.
Beispiel: Kostenlose Nutzer ohne Anrufe im April
Die Daten in den Tabellen rc_calls und rc_users sind:
- Nutzer-ID: 1218, Anruf-ID: 0, Anrufdatum: 2020-04-19 01:06:00
- Nutzer-ID: 1554, Anruf-ID: 1, Anrufdatum: 2020-03-01 16:51:00
- Nutzer-ID: 1857, Anruf-ID: 2, Anrufdatum: 2020-03-29 07:06:00
Der Code platziert den Datumsfilter in der ON-Klausel, nicht in WHERE. Diese Unterscheidung macht dies zu einem Anti Join. Wenn der Datumsfilter in WHERE platziert wird, würden Zeilen, bei denen der LEFT JOIN NULLs erzeugt hat, entfernt, was es wieder zu einem INNER JOIN machen würde. Mit dem Filter in ON erzeugen kostenlose Nutzer ohne qualifizierenden Anruf im April dennoch eine Zeile, wobei NULLs auf der rechten Seite vorhanden sind, und die IS NULL-Prüfung behält nur diese Zeilen.
SELECT DISTINCT u.user_id
FROM rc_users u
LEFT JOIN rc_calls c
ON u.user_id = c.user_id
AND c.call_date BETWEEN '2020-04-01' AND '2020-04-30'
WHERE u.status = 'free'
AND c.user_id IS NULL;Ausgabe
| user_id |
|---|
| 1575 |
| 1910 |
Fazit
Diese drei Join-Methoden lösen Fälle, in denen INNER JOIN und LEFT JOIN unpraktisch oder fehlerhaft sind:
- LATERAL ist der Weg, um set-returning Funktionen zeilenweise innerhalb von FROM aufzurufen.
- EXISTS liefert „Zeilen mit einer Übereinstimmung“ ohne die Duplikation, die INNER JOIN verursacht.
- NOT EXISTS oder LEFT JOIN + IS NULL geben „Zeilen ohne Übereinstimmung“ sauber zurück.
Das Muster, das man sich merken sollte, ist kurz: Wenn INNER JOIN Zeilen dupliziert, die man nicht möchte, verwendet man EXISTS. Wenn man Zeilen benötigt, die keine Übereinstimmung haben, verwendet man NOT EXISTS oder LEFT JOIN + IS NULL. Wenn eine Unterabfrage in FROM auf Spalten aus einer äußeren Tabelle zugreifen muss, fügt man LATERAL hinzu.
Die Anwendung dieser Techniken auf reale SQL-Interviewfragen macht die Syntax automatisch.
Nate Rosidi ist Datenwissenschaftler und im Produktmanagement tätig. Er ist zudem Dozent für Analytik und Gründer von StrataScratch, einer Plattform, die Datenwissenschaftlern hilft, sich auf Interviews mit echten Fragen von Top-Unternehmen vorzubereiten. Nate schreibt über die neuesten Trends im Arbeitsmarkt, gibt Interviewtipps, teilt Projekte aus der Datenwissenschaft und behandelt alles rund um SQL.
Quellen: kdnuggets
Bildquelle: KI generiert
🚀