PostgreSQL: ON vs WHERE in outer joins - when moving a condition changes the result

Context: how PostgreSQL processes ON and WHERE in outer joins

PostgreSQL builds a query's table expression as a pipeline: the FROM clause derives an intermediate virtual table, and WHERE, GROUP BY and HAVING then transform it. Within FROM, a qualified join's condition lives in the ON (or USING) clause, and it determines which rows from the two sources are considered to match. For a LEFT OUTER JOIN, PostgreSQL first performs an inner join, then adds one row for every left-side row that matched nothing, filling the right-side columns with nulls. This means the ON clause does two things: it removes non-matching combinations and it adds null-extended rows for unmatched left rows.

WHERE has no such role. It runs after the FROM clause has produced the joined table and simply eliminates rows that fail its search condition. The documentation states this ordering explicitly: a restriction in the ON clause is processed before the join, while a restriction in the WHERE clause is processed after the join. For inner joins this ordering is invisible in the result, but for outer joins it is not, because a WHERE predicate on a right-side column can reject the very null-extended rows that the outer join was supposed to preserve.

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)

Recommendation: where to place conditions, and the trade-offs

A practical rule: if a condition restricts the nullable (right) side of an outer join and you still want unmatched rows from the preserved side, put it in ON. If the condition is meant to filter the final result, including discarding null-extended rows, put it in WHERE. Conditions on the preserved (left) side of a LEFT JOIN behave the same in either place in terms of which left rows survive, but placing them in ON keeps the join self-contained and avoids surprises when the join type is later changed. For inner joins, the documentation notes that the join condition may be written in WHERE or in the JOIN clause and the choice is mainly a matter of style; readability and team conventions should decide. Note that outer joins themselves must be written in the FROM clause; there is no WHERE-only spelling for them.

The trade-off is semantic clarity versus brevity. Mixing non-join predicates into ON can make a query harder to read, but moving such a predicate into WHERE silently converts a 'keep unmatched rows' query into an inner-join-like result. When reviewing or refactoring, treat any relocation of a predicate across the ON/WHERE boundary of an outer join as a semantic change, not a cosmetic one.

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)

Concrete illustration: LEFT JOIN with a condition on t2.value

The documentation example uses two tables where t1 has rows (1,a), (2,b), (3,c) and t2 has (1,xxx) and (3,yyy). With the restriction inside ON, the join matches only the t2 row with value 'xxx'; rows 2 and 3 of t1 find no qualifying match and are returned with NULLs in the t2 columns, giving three rows. With the same restriction in WHERE, the join first matches on num alone, producing rows for pairs (1,xxx) and (3,yyy), then WHERE discards every row where t2.value is not 'xxx' - and it also discards the null-extended rows, since NULL = 'xxx' is not true. Only one row survives. The illustrative outputs below follow the documentation's example; run them against your own seed data to confirm.

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)

Limits of applicability

The distinction matters only for LEFT, RIGHT and FULL outer joins, because only their ON clauses both remove and add rows. For INNER JOIN, ON and WHERE are equivalent and placement is style. The advice here concerns result semantics only; it says nothing about which plan or index the planner will choose, and performance questions still require measuring the actual plan. The examples reflect PostgreSQL 18 documentation; the JOIN syntax is standard SQL, but verify behavior in other database systems before relying on it. Also remember that JOIN binds more tightly than comma in the FROM list, so mixed comma-and-JOIN lists can change which tables an ON condition may reference.

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