PostgreSQL: ON vs. WHERE bei Outer Joins - wann das Verschieben einer Bedingung das Ergebnis ändert

Kontext: Wie PostgreSQL ON und WHERE bei Outer Joins verarbeitet

PostgreSQL baut den Tabellenausdruck einer Abfrage als Pipeline auf: Die FROM-Klausel erzeugt eine Zwischen-Virtualtable, und WHERE, GROUP BY und HAVING transformieren diese anschließend. Innerhalb von FROM lebt die Bedingung eines qualifizierten Joins in der ON-Klausel (oder USING), und sie bestimmt, welche Zeilen der beiden Quellen als zueinander passend gelten. Bei einem LEFT OUTER JOIN führt PostgreSQL zunächst einen Inner Join aus und fügt dann für jede linke Zeile, die nichts gefunden hat, eine Zeile hinzu, wobei die rechten Spalten mit NULL gefüllt werden. Das bedeutet: Die ON-Klausel tut zwei Dinge - sie entfernt nicht passende Kombinationen und sie ergänzt null-erweiterte Zeilen für linke Zeilen ohne Treffer.

WHERE hat keine solche Rolle. Es läuft, nachdem die FROM-Klausel die verbundene Tabelle erzeugt hat, und eliminiert einfach Zeilen, die seine Suchbedingung nicht erfüllen. Die Dokumentation legt diese Reihenfolge ausdrücklich fest: Eine Einschränkung in der ON-Klausel wird vor dem Join verarbeitet, eine Einschränkung in der WHERE-Klausel danach. Bei Inner Joins ist diese Reihenfolge im Ergebnis unsichtbar, bei Outer Joins jedoch nicht, weil ein WHERE-Prädikat auf einer rechten Spalte genau die null-erweiterten Zeilen verwerfen kann, die der Outer Join eigentlich bewahren sollte.

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';; num | name | num | value;-----+------+-----+-------; 1 | a | 1 | xxx;(1 row)

Empfehlung: Wo Bedingungen platziert werden, und die Abwägungen

Eine praktische Regel: Wenn eine Bedingung die nullable (rechte) Seite eines Outer Joins einschränkt und Sie trotzdem die Zeilen ohne Treffer von der bewahrten Seite wollen, setzen Sie sie in ON. Soll die Bedingung das Endergebnis filtern, einschließlich des Verwerfens null-erweiterter Zeilen, gehört sie in WHERE. Bedingungen auf der bewahrten (linken) Seite eines LEFT JOIN verhalten sich in beiden Positionen hinsichtlich der erhaltenen linken Zeilen gleich, aber in ON bleibt der Join in sich geschlossen und Überraschungen bei einer späteren Änderung des Jointyps werden vermieden. Bei Inner Joins weist die Dokumentation darauf hin, dass die Join-Bedingung in WHERE oder in der JOIN-Klausel geschrieben werden kann und die Wahl hauptsächlich eine Stilfrage ist; Lesbarkeit und Team-Konventionen sollten entscheiden. Beachten Sie, dass Outer Joins selbst in der FROM-Klausel geschrieben werden müssen; eine Schreibweise nur mit WHERE gibt es dafür nicht.

Der Kompromiss besteht zwischen semantischer Klarheit und Kürze. Das Mischen von Nicht-Join-Prädikaten in ON kann eine Abfrage schwerer lesbar machen, aber das Verschieben eines solchen Prädikats in WHERE verwandelt eine Abfrage mit 'Zeilen ohne Treffer behalten' stillschweigend in ein inner-join-artiges Ergebnis. Behandeln Sie beim Review oder Refactoring jede Verlagerung eines Prädikats über die ON/WHERE-Grenze eines Outer Joins als semantische Änderung, nicht als kosmetische.

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';; num | name | num | value;-----+------+-----+-------; 1 | a | 1 | xxx; 2 | b | |; 3 | c | |;(3 rows)

Konkretes Beispiel: LEFT JOIN mit einer Bedingung auf t2.value

Das Dokumentationsbeispiel verwendet zwei Tabellen, wobei t1 die Zeilen (1,a), (2,b), (3,c) enthält und t2 die Zeilen (1,xxx) und (3,yyy). Mit der Einschränkung in ON passt der Join nur zur t2-Zeile mit dem Wert 'xxx'; die Zeilen 2 und 3 von t1 finden keine passende Zeile und werden mit NULLs in den t2-Spalten zurückgegeben, insgesamt also drei Zeilen. Mit derselben Einschränkung in WHERE matcht der Join zunächst nur über num und erzeugt Zeilen für die Paare (1,xxx) und (3,yyy); dann verwirft WHERE jede Zeile, bei der t2.value nicht 'xxx' ist - und verwirft auch die null-erweiterten Zeilen, da NULL = 'xxx' nicht wahr ist. Nur eine Zeile bleibt übrig. Die folgenden Beispielausgaben folgen dem Beispiel der Dokumentation; führen Sie sie gegen Ihre eigenen Testdaten aus, um das zu bestätigen.

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx'; -- returns 3 rows: (1,a,1,xxx), (2,b,NULL,NULL), (3,c,NULL,NULL) SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx'; -- returns 1 row: (1,a,1,xxx)

Grenzen der Anwendbarkeit

Der Unterschied ist nur bei LEFT-, RIGHT- und FULL-Outer-Joins relevant, weil nur deren ON-Klauseln sowohl Zeilen entfernen als auch hinzufügen. Bei INNER JOIN sind ON und WHERE äquivalent, und die Platzierung ist Stil. Der Rat hier betrifft nur die Ergebnissemantik; er sagt nichts darüber aus, welchen Plan oder Index der Planner wählt, und Performancefragen erfordern weiterhin die Messung des tatsächlichen Plans. Die Beispiele entsprechen der PostgreSQL-18-Dokumentation; die JOIN-Syntax ist Standard-SQL, prüfen Sie das Verhalten aber in anderen Datenbanksystemen, bevor Sie sich darauf verlassen. Denken Sie außerdem daran, dass JOIN in der FROM-Liste enger bindet als das Komma, sodass gemischte Komma-und-JOIN-Listen ändern können, auf welche Tabellen eine ON-Bedingung verweisen darf.

FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition -- the condition can reference T1 here,;-- but not in the comma-list equivalent;FROM T1, T2 INNER JOIN T3 ON condition