Fortgeschrittene Verbindungstechniken: LATERAL-Verbindungen, Semi-Verbindungen, Anti-Verbindungen

# Einführung

INNER JOIN und LEFT JOIN verarbeiten die meisten SQL-Abfragen. Eine kleinere Klasse von Problemen braucht andere Be a part of-Typen: Zählen der Ergebnisse einer mengenliefernden Funktion Zeile für Zeile, Filtern von Zeilen nach Existenz in einer anderen Tabelle und Zurückgeben von Zeilen, die in einer anderen Tabelle keine Übereinstimmung haben.

Drei weniger verbreitete Verknüpfungen handhaben diese sauber. Durch LATERAL-Joins kann eine Unterabfrage in der FROM-Klausel auf Spalten von früheren Zeitpunkten in derselben FROM-Klausel verweisen. Semi-Joins geben Zeilen zurück, bei denen eine Übereinstimmung in einer anderen Tabelle vorhanden ist, ohne diese Zeilen zu duplizieren. Anti-Joins geben Zeilen zurück, in denen keine Übereinstimmung vorhanden ist.

Lassen Sie uns untersuchen, wie Sie diese Muster in der Praxis anwenden können.

# SEITLICHE Verbindungen

Eine LATERAL-Unterabfrage in der FROM-Klausel kann auf Spalten aus vorhergehenden Tabellen in derselben FROM-Klausel verweisen. Ohne LATERAL wird eine Unterabfrage in FROM unabhängig ausgewertet und kann diese Spalten nicht sehen.

Dies ist am wichtigsten, wenn eine Funktion aufgerufen wird, die eine Menge zurückgibt (eine Funktion, die mehrere Zeilen professional Eingabe zurückgibt). Funktionen, die Mengen zurückgeben, können in der SELECT-Liste aufgerufen werden, aber um sie Zeile für Zeile auf eine Spalte aus einer äußeren Tabelle innerhalb der FROM-Klausel anzuwenden, ist LATERAL erforderlich.

Häufige Fälle:

• Berufung unnest() auf einer Array-Spalte, um eine Zeile professional Array-Component zu erhalten
• Berufung regexp_matches() mit dem 'g' Flag, um jede Übereinstimmung professional Zeile zu extrahieren
• Berechnen eines Prime-N-Ergebnisses professional Gruppe mit einer korrelierten Unterabfrage in FROM
• JSON-Arrays professional Zeile aufteilen

// Beispiel: Vorkommen von Wörtern zählen

Diese Google-Frage fordert uns auf, zu zählen, wie oft die Wörter „Stier“ und „Bär“ in einem vorkommen contents Spalte. Bei Übereinstimmungen muss die Groß-/Kleinschreibung nicht beachtet werden und Teilzeichenfolgen wie „bullish“ oder „pearing“ sollten ausgeschlossen werden.

Daten: Die google_file_store Tabelle ist:

Code: regexp_matches() gibt eine Zeile professional Treffer zurück. Um es einmal professional Reihe auszuführen google_file_store und alle Übereinstimmungen in der Tabelle zählen, fügen wir sie mit LATERAL in die FROM-Klausel ein. Der m Und M Anker sind PostgreSQL Wortgrenzen, die „bullisch“ und „tragend“ ausschließen.

SELECT 'bull' AS phrase,
       COUNT(*) AS nentry
FROM google_file_store,
     LATERAL regexp_matches(LOWER(contents), 'm(bull)M', 'g')
UNION ALL
SELECT 'bear' AS phrase,
       COUNT(*) AS nentry
FROM google_file_store,
     LATERAL regexp_matches(LOWER(contents), 'm(bear)M', 'g');

// Ausgabe

# Halbverbindungen

Ein Semi-Be a part of gibt Zeilen aus der linken Tabelle zurück, in denen in der rechten Tabelle mindestens eine Übereinstimmung vorhanden ist, wobei jede Zeile in der linken Tabelle höchstens einmal vorkommt. INNER JOIN dupliziert die Zeilen der linken Tabelle, wenn die rechte Seite mehrere Übereinstimmungen aufweist. Bei Semi-Joins ist dies nicht der Fall.

Zwei SQL-Implementierungen:

• WHERE EXISTS (SELECT 1 FROM ...)
• WHERE col IN (SELECT col FROM ...)

EXISTS ist die allgemeinere Kind, da sie mehrspaltige Be a part of-Bedingungen und korrelierte Unterabfragen verarbeitet, ohne die Abfrage neu zu schreiben.

// Beispiel: Suche nach hochwertigen Kunden

Diese Frage bittet uns, Kunden zu finden, die mindestens eine Bestellung über 100 $ aufgegeben haben, und ihre Kundennummer und ihren Namen zurückzugeben.

Daten: Vorschauen von online_store_customers Und online_store_orders:

Code: Die EXISTS-Unterabfrage prüft professional Kunde, ob eine Bestellung über 100 $ vorliegt. SELECT 1 ist die Konvention, weil EXISTS sich nur darum kümmert, ob eine Zeile zurückgegeben wird, nicht darum, was darin enthalten ist.

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.quantity > 100
);

Würden wir stattdessen INNER JOIN verwenden, würde Kunde 1 im Ergebnis zweimal auftauchen, da zwei Bestellungen übereinstimmen. EXISTS gibt einmal Kunde 1 zurück.

// Ausgabe

# Anti-Joins

Ein Anti-Be a part of gibt Zeilen aus der linken Tabelle zurück, in denen in der rechten Tabelle keine Übereinstimmung vorhanden ist. Es ist die Umkehrung eines Semi-Joins.

Zwei SQL-Implementierungen:

• LEFT JOIN ... WHERE right_table.col IS NULL
• WHERE NOT EXISTS (SELECT 1 FROM ...)

Beide führen zum gleichen Ergebnis. NOT EXISTS erzeugt in modernen PostgreSQL-Versionen oft einen besseren Abfrageplan und liest direkter. Das Muster LEFT JOIN + IS NULL ist älter und nützlich, wenn Sie für nicht übereinstimmende Zeilen auch Spalten von der rechten Seite benötigen.

// Beispiel: Kostenlose Benutzer ohne April-Anrufe

Diese Frage fordert uns auf, kostenlose Benutzer zurückzugeben, die im April 2020 keine Anrufe getätigt haben.

Daten: Vorschauen von rc_calls Und rc_users:

Code: Der Datumsfilter befindet sich in der ON-Klausel, nicht in WHERE. Diese Unterscheidung macht dies zu einem Anti-Be a part of. Das Einfügen des Datumsfilters in WHERE würde Zeilen löschen, in denen der LEFT JOIN NULL-Werte erzeugte, und ihn wieder auf einen INNER JOIN reduzieren. Wenn der Filter aktiviert ist, erzeugen kostenlose Benutzer ohne qualifizierenden April-Aufruf immer noch eine Zeile mit NULL-Werten auf der rechten Seite, und die IS NULL-Prüfung behält nur diese Zeilen bei.

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.standing="free"
  AND c.user_id IS NULL;

// Ausgabe

# Abschluss

Diese drei Joins lösen Fälle, in denen INNER JOIN und LEFT JOIN umständlich oder falsch sind:

• LATERAL ist die Möglichkeit, Funktionen, die Mengen zurückgeben, Zeile für Zeile innerhalb von FROM aufzurufen.
• EXISTS liefert Ihnen „Zeilen mit einer Übereinstimmung“ ohne die Duplizierung, die INNER JOIN verursacht.
• NOT EXISTS oder LEFT JOIN + IS NULL gibt Ihnen sauber „Zeilen ohne Übereinstimmung“.

Das zu merkende Muster ist kurz. Wenn INNER JOIN unerwünschte Zeilen dupliziert, verwenden Sie EXISTS. Wenn Sie Zeilen benötigen, die keine Übereinstimmung haben, verwenden Sie NOT EXISTS oder LEFT JOIN + IS NULL. Wenn eine Unterabfrage in FROM auf Spalten aus einer äußeren Tabelle verweisen muss, fügen Sie LATERAL hinzu.

Üben Sie diese in der Praxis Fragen zum SQL-Interviewund die Syntax wird automatisch.

Nate Rosidi ist Datenwissenschaftler und in der Produktstrategie tätig. Er ist außerdem außerordentlicher Professor für Analytik und Gründer von StrataScratch, einer Plattform, die Datenwissenschaftlern hilft, sich mit echten Interviewfragen von Prime-Unternehmen auf ihre Interviews vorzubereiten. Nate schreibt über die neuesten Developments auf dem Karrieremarkt, gibt Ratschläge zu Vorstellungsgesprächen, stellt Information-Science-Projekte vor und behandelt alles rund um SQL.