Warum JOIN Zeilen vervielfacht: Schlüssel, Beziehungen und Ergebnisebene
Wiederholte JOIN-Zeilen anhand passender Schlüssel, Datenbankbedingungen und der gewünschten Ergebnisebene erklären.
Auf dieser Seite
Die kurze Antwort
Ein JOIN liefert passende Zeilenpaare. Eine Ausgangszeile darf deshalb mehrfach erscheinen, ohne dass ein Fehler vorliegt. Legen Sie zuerst fest, was eine Ergebniszeile darstellen soll: einen Auftrag, eine Auftragsposition oder eine Zusammenfassung. Zählen Sie anschließend die tatsächlichen Verbindungsschlüssel auf beiden Seiten nach Anwendung der jeweiligen Filter. Kommt ein Schlüssel ohne NULL links L-mal und rechts R-mal vor, entstehen bei einer Gleichheitsverknüpfung L × R Paare. Prüfen Sie Eindeutigkeitsbedingungen getrennt: Die heutigen Daten garantieren nicht, dass spätere Einfügungen ebenfalls eindeutig bleiben.
Was ein JOIN zählt
Bei INNER JOIN erzeugt jedes Zeilenpaar, das die ON-Bedingung erfüllt, eine Ergebniszeile. Eine äußere Verknüpfung kann zusätzlich Zeilen ohne Partner erhalten und fehlende Werte mit NULL ergänzen. Das kartesische Produkt erklärt die SQL-Logik, nicht zwingend die physische Ausführung aller möglichen Kombinationen. Untersuchen Sie daher zunächst die Verbindung und anschließend den übrigen Abfragetext. Ein späterer WHERE-Filter kann bereits verbundene Zeilen entfernen und die endgültige Anzahl verändern.
Kartesisches Produkt und wiederholte Schlüssel
CROSS JOIN kombiniert jede linke Zeile mit jeder rechten Zeile. Zwei Eingaben mit jeweils drei Zeilen ergeben neun Paare. Eine Gleichheitsverknüpfung behält nur passende Schlüssel. Wiederholt sich derselbe Schlüssel auf beiden Seiten, entspricht seine Paaranzahl dem Produkt beider Häufigkeiten. Damit können Beziehungen viele zu vielen entstehen. Eine Beziehung eins zu vielen allein bedeutet dagegen nicht, dass das Ergebnis größer als die größte Eingabetabelle sein muss. Die Anzahl hängt von den konkreten passenden Paaren ab.
WITH t1(num) AS (VALUES (1), (2), (3)),
t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num AS left_num, t2.num AS right_num
FROM t1 CROSS JOIN t2;
-- Nine pairs in this illustrative dataset.Ein Beispiel für eins zu vielen
Im Beispiel hat Auftrag 1 zwei Positionen und Auftrag 2 eine Position. Die Verbindung ergibt drei Zeilen: Auftrag 1 erscheint mit zwei unterschiedlichen Positionsnummern. Diese Zeilen beschreiben sinnvolle Beziehungen und dürfen nicht automatisch als falsche Duplikate gelöscht werden. Soll der Bericht Positionen zeigen, ist diese Ebene geeignet. Wird genau eine Zeile pro Auftrag benötigt, müssen die Positionen zuvor zusammengefasst oder die benötigten Informationen anders ermittelt werden. Entscheidend ist der Zweck der Ausgabe.
WITH orders(order_id) AS (VALUES (1), (2)),
order_items(item_id, order_id) AS (VALUES (10, 1), (11, 1), (12, 2))
SELECT o.order_id, i.item_id
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
ORDER BY o.order_id, i.item_id;
-- Three pairs: order 1 appears with two different items.LEFT JOIN und fehlende Partner
LEFT JOIN erhält eine linke Zeile ohne Treffer und setzt die rechten Spalten auf NULL. Bei mehreren Treffern erscheint dieselbe linke Zeile trotzdem mehrfach. Im kleinen Beispiel finden die Schlüssel 1 und 3 einen Partner, Schlüssel 2 nicht: Es entstehen drei Zeilen. Das gilt für die Verbindung vor nachfolgenden Filtern. Eine WHERE-Bedingung auf einer rechten Spalte kann die Zeilen ohne Treffer ausschließen. Prüfen Sie deshalb die vollständige Abfrage und verlassen Sie sich nicht nur auf die Bezeichnung LEFT JOIN.
WITH t1(num) AS (VALUES (1), (2), (3)),
t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num AS left_num, t2.num AS right_num
FROM t1 LEFT JOIN t2 ON t1.num = t2.num
ORDER BY t1.num;
-- Three rows; the right value for left key 2 is NULL.Die tatsächlichen Schlüssel zählen
Zählen Sie die tatsächlichen Verbindungsschlüssel auf beiden Seiten, nachdem relevante Filter angewendet wurden. Das Beispiel gruppiert Positionen nach order_id und findet den Auftrag mit zwei Positionen. Bei gewöhnlicher Gleichheit sollten NULL-Schlüssel ausgeschlossen werden, denn NULL ist in dieser Bedingung nicht gleich einem weiteren NULL. Eine einzige beobachtete Zeile pro Schlüssel beweist weder eine Eindeutigkeitsbedingung noch eine dauerhafte Beziehung eins zu eins. Auch das Schema muss geprüft werden; spätere Einfügungen können das Muster verändern.
WITH order_items(item_id, order_id) AS (VALUES (10, 1), (11, 1), (12, 2))
SELECT order_id, COUNT(*) AS item_count
FROM order_items
WHERE order_id IS NOT NULL
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Order 1 has two items in this illustrative dataset.Fremdschlüssel, Eindeutigkeit und NULL
Ein Fremdschlüssel verbindet die Kindspalten mit einem referenzierten Schlüssel, dessen Eindeutigkeit abgesichert ist. Die Kindwerte werden dadurch nicht eindeutig: Mehrere Positionen können denselben Auftrag referenzieren. Auch NULL kann zulässig sein; der Fremdschlüssel allein verlangt keinen ausgefüllten Elternschlüssel. Das Beispielschema ergänzt deshalb order_id ausdrücklich um NOT NULL. Vergleichen Sie die ON-Spalten mit den deklarierten Bedingungen, insbesondere bei zusammengesetzten Schlüsseln und zusätzlichen Verbindungskriterien. Unterschiedliche Spalten können unterschiedliche Beziehungen erzeugen.
CREATE TABLE orders (
order_id integer PRIMARY KEY
);
CREATE TABLE order_items (
item_id integer PRIMARY KEY,
order_id integer NOT NULL REFERENCES orders(order_id)
);Die gewünschte Ergebnisebene wählen
Werden mehrere Detailtabellen verbunden, können Werte einer Sammlung für jedes Element einer anderen Sammlung wiederholt werden. Summen werden dann irreführend. Für eine Zeile pro Auftrag sollten die Details zuerst auf dieser Ebene aggregiert werden. Das Beispiel zählt Positionen und verbindet anschließend die Zusammenfassung mit den Aufträgen. Sein INNER JOIN zeigt nur Aufträge mit Positionen. Sollen andere Aufträge erhalten bleiben, verwenden Sie LEFT JOIN und legen Sie fest, wie eine fehlende Anzahl dargestellt wird, beispielsweise als null Positionen.
SELECT o.order_id, i.item_count
FROM orders AS o
JOIN (
SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id
) AS i ON i.order_id = o.order_id;Drei Verknüpfungen mit sichtbaren Beispieldaten
Das letzte Beispiel definiert kleine Eingaben mit VALUES, sodass sämtliche Schlüssel direkt sichtbar sind. Nur 1 und 3 kommen auf beiden Seiten vor; INNER JOIN liefert deshalb zwei Zeilen. Das vorherige LEFT-JOIN-Beispiel erhält zusätzlich Schlüssel 2 und liefert drei Zeilen. CROSS JOIN erzeugt alle neun Paare. Diese Zahlen folgen aus den aufgeführten Beispieldaten und sind keine Leistungsmessung. Ergänzen Sie gedanklich einen passenden Schlüssel und bestimmen Sie, welche zusätzlichen Paare dadurch entstehen müssten.
WITH t1(num) AS (VALUES (1), (2), (3)),
t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num
FROM t1 INNER JOIN t2 ON t1.num = t2.num
ORDER BY t1.num;
-- Two rows: matching keys 1 and 3.Was Sie prüfen sollten
- Die Verbindungsschlüssel beider Eingaben nach ihren Filtern zählen und NULL berücksichtigen.
- PRIMARY KEY, UNIQUE und FOREIGN KEY getrennt von den momentan beobachteten Werten prüfen.
- Für L passende Zeilen links und R rechts entstehen bei einem Schlüssel ohne NULL L × R Gleichheitspaare.
- EXPLAIN für Schätzungen verwenden, aber die Vervielfachung anhand der Schlüssel prüfen: Der Algorithmus allein beweist sie nicht.
Geltungsbereich
Die Beispiele verwenden Gleichheitsverknüpfungen und PostgreSQL-Syntax. Andere Bedingungen, zusammengesetzte Schlüssel, WHERE, Gruppierung und Projektion können Ergebnisse verändern. Die Zahlen gelten für illustrative VALUES; Ausführung und Geschwindigkeit wurden nicht gemessen.