Oracle Database est très soucieux de compatibilité ascendante.
Avantage : vous navez quasi jamais à modifier votre code applicatif.
Inconvénient : vous navez 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 dutiliser la syntaxe propriétaire dOracle Database pour les jointures externes. Lors dune 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 dutiliser 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 dune moulinette, il est préférable dêtre capable de coder directement en SQL92.
Si ce nest 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 sil nexiste 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.
LOrdre...ou le Chaos ?
Lorsque vous codez des jointures internes avec JOIN ou INNER JOIN elles sont en théorie prises en compte dans lordre où elles apparaissent, de la gauche vers la droite. Cependant, le résultat final ne dépend pas de lordre 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 nen va pas forcément de même pour les jointures externes, les OUTER JOIN. Lordre décriture EST potentiellement important. Démonstration avec PostgreSQL 9.6 :
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 quune ligne soit retenue au niveau des résultats. C ne comprend quune 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 nest 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, cest 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 :
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 lest pas. Les 2 conditions ne sont donc PAS équivalentes et cest ce qui explique les différences au niveau de la 2ème ligne de résultats entre ces 2 requêtes.
Un Bouclier contre lOrdre
Les jointures sont évaluées dans lordre ... mais vous pouvez ajouter des parenthèses pour modifier cet ordre, ce qui ne facilite dailleurs pas forcément la compréhension de la requête. Démonstration :
Lorsque des parenthèses sont ajoutées, lévaluation est effectuée de lintérieur vers lexté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 sapplique à 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 dimportance et linverser ne modifie pas les résultats. Encore une fois, les optimiseurs (planners) des SGBDR tirent parti de cet aspect pour diminuer le coût dexécution et obtenir de meilleurs temps de réponse. Mais attention si la clause FROM inclut des jointures externes. Démonstration :
Avec la requête 1 la clause WHERE annule leffet 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 dannuler leffet 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 :
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 dapprendre à éviter ces pièges !