PostgreSQL : ON vs WHERE dans les jointures externes - quand déplacer une condition change le résultat

Contexte : comment PostgreSQL traite ON et WHERE dans les jointures externes

PostgreSQL construit l'expression de table d'une requête comme un pipeline : la clause FROM dérive une table virtuelle intermédiaire, puis WHERE, GROUP BY et HAVING la transforment. Au sein du FROM, la condition d'une jointure qualifiée se trouve dans la clause ON (ou USING), et elle détermine quelles lignes des deux sources sont considérées comme correspondantes. Pour un LEFT OUTER JOIN, PostgreSQL effectue d'abord une jointure interne, puis ajoute une ligne pour chaque ligne de gauche qui n'a rien trouvé, en remplissant les colonnes de droite avec des valeurs NULL. Cela signifie que la clause ON fait deux choses : elle élimine les combinaisons sans correspondance et elle ajoute des lignes étendues par NULL pour les lignes de gauche sans correspondance.

WHERE n'a pas ce rôle. Il s'exécute après que la clause FROM a produit la table jointe et élimine simplement les lignes qui ne satisfont pas sa condition de recherche. La documentation énonce explicitement cet ordre : une restriction dans la clause ON est traitée avant la jointure, tandis qu'une restriction dans la clause WHERE est traitée après la jointure. Pour les jointures internes, cet ordre est invisible dans le résultat, mais pas pour les jointures externes, car un prédicat WHERE sur une colonne du côté droit peut rejeter précisément les lignes étendues par NULL que la jointure externe était censée préserver.

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)

Recommandation : où placer les conditions, et les compromis

Règle pratique : si une condition restreint le côté annulable (droite) d'une jointure externe et que vous voulez toujours les lignes sans correspondance du côté préservé, placez-la dans ON. Si la condition est destinée à filtrer le résultat final, y compris en écartant les lignes étendues par NULL, placez-la dans WHERE. Les conditions sur le côté préservé (gauche) d'un LEFT JOIN se comportent de la même manière aux deux emplacements en ce qui concerne les lignes de gauche conservées, mais les placer dans ON garde la jointure autonome et évite les surprises si le type de jointure est modifié ultérieurement. Pour les jointures internes, la documentation note que la condition de jointure peut être écrite dans WHERE ou dans la clause JOIN et que le choix est surtout une question de style ; la lisibilité et les conventions d'équipe devraient décider. Notez que les jointures externes elles-mêmes doivent être écrites dans la clause FROM ; il n'existe pas d'écriture uniquement en WHERE pour elles.

Le compromis est la clarté sémantique contre la concision. Mélanger des prédicats non liés à la jointure dans ON peut rendre une requête plus difficile à lire, mais déplacer un tel prédicat vers WHERE transforme silencieusement une requête « conserver les lignes sans correspondance » en un résultat proche d'une jointure interne. Lors de la revue ou du refactoring, traitez tout déplacement d'un prédicat à travers la frontière ON/WHERE d'une jointure externe comme un changement sémantique, pas cosmétique.

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)

Illustration concrète : LEFT JOIN avec une condition sur t2.value

L'exemple de la documentation utilise deux tables où t1 contient les lignes (1,a), (2,b), (3,c) et t2 contient (1,xxx) et (3,yyy). Avec la restriction dans ON, la jointure ne trouve que la ligne de t2 dont la valeur est 'xxx' ; les lignes 2 et 3 de t1 ne trouvent aucune correspondance et sont renvoyées avec des NULL dans les colonnes de t2, ce qui donne trois lignes. Avec la même restriction dans WHERE, la jointure d'abord ne correspond que sur num, produisant des lignes pour les paires (1,xxx) et (3,yyy), puis WHERE écarte toute ligne où t2.value n'est pas 'xxx' - et il écarte aussi les lignes étendues par NULL, puisque NULL = 'xxx' n'est pas vrai. Une seule ligne survit. Les sorties illustratives ci-dessous suivent l'exemple de la documentation ; exécutez-les sur vos propres données pour confirmer.

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)

Limites d'application

La distinction n'a d'importance que pour les jointures externes LEFT, RIGHT et FULL, car seules leurs clauses ON retirent et ajoutent des lignes à la fois. Pour INNER JOIN, ON et WHERE sont équivalents et l'emplacement est une question de style. Ce conseil concerne uniquement la sémantique du résultat ; il ne dit rien sur le plan ou l'index que le planificateur choisira, et les questions de performance exigent toujours de mesurer le plan réel. Les exemples reflètent la documentation de PostgreSQL 18 ; la syntaxe JOIN est du SQL standard, mais vérifiez le comportement dans d'autres systèmes de bases de données avant de vous y fier. Rappelez-vous aussi que JOIN se lie plus étroitement que la virgule dans la liste FROM, donc des listes mêlant virgules et JOIN peuvent changer les tables auxquelles une condition ON peut faire référence.

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