Les pièges des jointures externes

      Oracle Database est très soucieux de compatibilité ascendante.
      Avantage : vous n’avez quasi jamais à modifier votre code applicatif.
      Inconvénient : vous n’avez quasi jamais à vous remettre en cause dans l’écriture du code applicatif.
      Cet aspect est particulièrement notable au niveau des jointures externes. Les (+) sont encore employés par les développeurs SQL codant pour Oracle alors que les développeurs SQL codant pour SQL Server ont abandonné les *= depuis plusieurs années.
      Et PostgreSQL ? La version communautaire ne permet pas d’utiliser la syntaxe propriétaire d’Oracle Database pour les jointures externes. Lors d’une migration, il est donc nécessaire de convertir le code vers une syntaxe SQL-92. Vous pouvez pour cela utiliser TOAD par exemple. Une autre idée est d’utiliser ora2pg. Cet outil, développé par Gilles Darold de Dalibo, est en effet de plus en plus efficace sur cet aspect.
      Ensuite, pour ne pas dépendre ad vitaam æternam d’une moulinette, il est préférable d’être capable de coder directement en SQL92.
      Si ce n’est déjà fait, vous pouvez découvrir ici les aspects basiques du passage de la syntaxe propriétaire Oracle à la syntaxe standard SQL en ce qui concerne les jointures externes. En gros, si j’écris "FROM A LEFT OUTER JOIN B ON (A.a1 = B.b1)", je compte bien conserver toutes les lignes de la table de gauche, A, même s’il n’existe pas de ligne de B respectant la condition de jointure (A.a1 = B.b1).
      Facile ? Pas si sûr, je vais présenter dans cet article 5 pièges à éviter concernant les jointures externes.

L’Ordre...ou le Chaos ?

      Lorsque vous codez des jointures internes avec JOIN ou INNER JOIN elles sont en théorie prises en compte dans l’ordre où elles apparaissent, de la gauche vers la droite. Cependant, le résultat final ne dépend pas de l’ordre de prise en compte des jointures. Le planner (optimiseur) du SGBD ne se gêne donc pas pour modifier en pratique cet ordre afin de fournir le résultat plus rapidement.
      Il n’en va pas forcément de même pour les jointures externes, les OUTER JOIN. L’ordre d’écriture EST potentiellement important. Démonstration avec PostgreSQL 9.6 :

select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit create table A(a1 smallint, a2 smallint); CREATE TABLE insert into A values(1, 1), (2, 2); INSERT 0 2 create table B(b1 smallint, b2 smallint); CREATE TABLE insert into B values(1, 1), (2, 2), (3, 3); INSERT 0 3 create table C(c1 smallint, c2 smallint); CREATE TABLE insert into C values(1,1); INSERT 0 1 select * from A right outer join B on (A.a1 = B.b1) inner join C on (C.c1 = A.a1); a1 | a2 | b1 | b2 | c1 | c2 ----+----+----+----+----+---- 1 | 1 | 1 | 1 | 1 | 1 select * from A inner join C on (C.c1 = A.a1) right outer join B on (A.a1 = B.b1); a1 | a2 | c1 | c2 | b1 | b2 ----+----+----+----+----+---- 1 | 1 | 1 | 1 | 1 | 1 | | | | 2 | 2 | | | | 3 | 3 select * from C right outer join B on (C.c1 = B.b1) inner join A on (A.a1 = B.b1); c1 | c2 | b1 | b2 | a1 | a2 ----+----+----+----+----+---- 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2

      Requête 1 - A comprend 2 lignes respectant la condition de la 1ère jointure mais nous conservons les 3 lignes provenant de B car cette jointure une jointure externe droite vers B effectuée avec RIGHT OUTER JOIN. La 2nde jointure est en revanche une jointure interne. Il faut que la condition de jointure soit respectée pour qu’une ligne soit retenue au niveau des résultats. C ne comprend qu’une ligne pouvant respecter la condition et nous obtenons UNE ligne au niveau des résultats.
      Requête 2 - La première jointure est une jointure interne entre A et C et nous obtenons UNE ligne respectant la condition de jointure. Cependant la 2ème jointure est une jointure externe droite vers B. Nous retenons donc toutes les lignes de B, à savoir TROIS lignes. Notez que lorsque la condition de jointure n’est pas vraie, les colonnes a1, a2, c1, c2 sont nulles.
      Requête 3 - Je vous laisse chercher pourquoi la requête ramène cette fois DEUX lignes, c’est toujours la même logique.

Tu es nul ? Tu as faux !

      Attention aussi au choix des colonnes utilisées dans les conditions des jointures externes, démonstration :

select * from C right join B on (C.c1 = B.b1) left join A on (C.c1 = A.a1); c1 | c2 | b1 | b2 | a1 | a2 ----+----+----+----+----+---- 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | | | | 3 | 3 | | select * from C right join B on (C.c1 = B.b1) left join A on (B.b1 = A.a1); c1 | c2 | b1 | b2 | a1 | a2 ----+----+----+----+----+---- 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | | 3 | 3 | |

      Si les jointures étaient des jointures internes, il importerait peu d’écrire (C.c1 = A.a1) ou (B.b1 = A.a1) pour la condition de la seconde jointure puisque nous serions certains que b.b1 = c.c1. Mais, la 1ère jointure étant une jointure externe droite vers B, nous pouvons aussi avoir C.c1 IS NULL alors que B.b1 ne l’est pas. Les 2 conditions ne sont donc PAS équivalentes et c’est ce qui explique les différences au niveau de la 2ème ligne de résultats entre ces 2 requêtes.

Un Bouclier contre l’Ordre

      Les jointures sont évaluées dans l’ordre ... mais vous pouvez ajouter des parenthèses pour modifier cet ordre, ce qui ne facilite d’ailleurs pas forcément la compréhension de la requête. Démonstration :

select C2.c1 as C2c1, C2.c2 as C2c2, B1.b1 as B1b1, B1.b2 as B1b2, C1.c1 as C1c1, C1.c2 as C1c2, A2.a1 as A2a1, A2.a2 as A2a2 from C C2 join (C C1 right join B B1 on (C1.c1 = B1.b1)) on (C2.c1 = B1.b1) right join A A2 on (A2.a1 = B1.b1) ; c2c1 | c2c2 | b1b1 | b1b2 | c1c1 | c1c2 | a2a1 | a2a2 ------+------+------+------+------+------+------+------ 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | | | | 2 | 2

      Lorsque des parenthèses sont ajoutées, l’évaluation est effectuée de l’intérieur vers l’extérieur, puis de la gauche vers la droite.
      La jointure externe droite entre C1 et B1 est donc réalisée en premier et nous donne TROIS lignes.
      Ensuite, la jointure interne avec C2 est réalisée et nous obtenons UNE ligne.
      Enfin une jointure externe droite est effectuée avec A ce qui donne au final DEUX lignes.
      Notez au passage la position des conditions de jointures : chaque condition s’applique à la jointure la plus proche située en amont (à sa gauche) et au même niveau de parenthèses.

La vague peut détruire le château de sable

      La clause de filtrage, la clause WHERE, est en théorie prise en compte après la clause FROM. Si la clause FROM ne comporte que des jointures internes alors cet ordre a peu d’importance et l’inverser ne modifie pas les résultats. Encore une fois, les optimiseurs (planners) des SGBDR tirent parti de cet aspect pour diminuer le coût d’exécution et obtenir de meilleurs temps de réponse. Mais attention si la clause FROM inclut des jointures externes. Démonstration :

select * from C right join B on (C.c1 = B.b1) where C.c2 in (1,2,3); c1 | c2 | b1 | b2 ----+----+----+---- 1 | 1 | 1 | 1 with C_filtree as (select c1, c2 from C where C.c2 in (1,2,3)) select * from C_filtree right join B on (C_filtree.c1 = B.b1); c1 | c2 | b1 | b2 ----+----+----+---- 1 | 1 | 1 | 1 | | 2 | 2 | | 3 | 3 select * from (select c1, c2 from C where C.c2 in (1,2,3)) C_filtree right join B on (C_filtree.c1 = B.b1); c1 | c2 | b1 | b2 ----+----+----+---- 1 | 1 | 1 | 1 | | 2 | 2 | | 3 | 3 select * from C right join B on (C.c1 = B.b1 and C.c2 in (1,2,3)); c1 | c2 | b1 | b2 ----+----+----+---- 1 | 1 | 1 | 1 | | 2 | 2 | | 3 | 3

      Avec la requête 1 la clause WHERE annule l’effet que nous voulions probablement obtenir en choisissant une jointure externe dans la clause FROM. La jointure ramène 3 lignes mais la clause WHERE élimine les lignes pour lesquelles C.c2 est nulle et nous obtenons au final UNE ligne.
      Que faire pour obtenir toutes les lignes provenant de B alors ?
      Les requêtes 2 et 3 présentent en fait la même solution. Au lieu de joindre B et C, nous joignons B et (C préalablement filtrée).
      Il est aussi possible de remonter le test d’égalité que nous avions placé au niveau de la clause de filtrage vers la condition de jointure comme le montre la requête 4.

Attention à la remontada

      Dans la requête 4 du test précédent, nous avons remonté un test de la clause WHERE vers la condition de jointure de la clause FROM pour éviter d’annuler l’effet de la jointure externe. Mais, parfois, nous voulons vraiment filtrer sur la table protégée par la jointure externe donc attention à ne pas tout remonter vers la condition de jointure sans discernement :

select B.b1, B.b2, C.c1, C.c2 from B left join C on (B.b1 = C.c1 and B.b1 = 2); b1 | b2 | c1 | c2 ----+----+----+---- 1 | 1 | | 2 | 2 | | 3 | 3 | | select B.b1, B.b2, C.c1, C.c2 from B left join C on (B.b1 = C.c1) where B.b1 = 2; b1 | b2 | c1 | c2 ----+----+----+---- 2 | 2 | | select B_filtree.b1, B_filtree.b2, C.c1, C.c2 from (select b1, b2 from B where B.b1 = 2) B_filtree left join C on (B_filtree.b1 = C.c1); b1 | b2 | c1 | c2 ----+----+----+---- 2 | 2 | | with B_filtree(b1, b2) as (select b1, b2 from B where B.b1 = 2) select B_filtree.b1, B_filtree.b2, C.c1, C.c2 from B_filtree left join C on (B_filtree.b1 = C.c1); b1 | b2 | c1 | c2 ----+----+----+---- 2 | 2 | |

      Requête 1 : nous avons fait une jointure externe gauche entre B et C, cela signifie que nous allons conserver TOUTES les lignes de B, même celles ne respectant pas la condition de jointure. Le test B.b1 = 2 sert donc en fait ici seulement à écarter la ligne provenant de C qui ne répont pas à la condition (B.b1 = C.c1 = 2)
      Si nous voulons que le filtrage concerne la table B alors il faut faire redescendre le test d’égalité B.b1 = 2 vers la clause WHERE comme le montre la requête 2.
      Encore une fois, il est aussi possible de joindre des ensembles préalablement filtrés comme dans les requêtes 3 et 4.

Conclusion

      Les jointures externes comportent quelques pièges, souvent liés à la manipulation des valeurs nulles. A nous d’apprendre à éviter ces pièges !

Mise à jour : 03/06/2017