Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
30 octobre 2009 5 30 /10 /octobre /2009 11:04

La question du jour :
Est-il équivalent de placer son prédicat (mono table) de jointure externe dans la clause LEFT OUTER JOIN, ou dans le WHERE en permettant les NULL ?
(En fait, c'est même la question du mois vu que je n'ai pas été capable de finir un article depuis longtemps...)


Ben non !

Et il y a plusieurs raisons, que nous illustrerons par l'exemple.

Dans la suite de l'article, il y aura :

0) Mise en jambes, jeux de tests

1) Si la colonne que vous voulez filtrer sur le résultat est nullable (peut prendre la "valeur" NULL), alors il peut y avoir confusion entre le NULL de "il n'y a pas de lignes qui matchent la correspondance" et "la colonne de la ligne répondant au critère de jointure est NULL"

2) Si une et une seule ligne de la table de droite (dans une jointure LEFT) répond au critère de jointure mais prend une valeur non NULL pour la colonne filtrée : 0 lignes renvoyées au lieu d'une !

3) Ca n'a rien à voir, mais un petit plébiscite pour la syntaxe ANSI contre la version dépréciée Oracle


0) C'est parti !


Les jeux de tests : une table maître, une table détail. Une seule ligne de détail pour Marcel, 3 lignes de détail pour Robert, 0 pour Squelettor.


CREATE TABLE TEST_LJ_MAITRE AS
SELECT 1 AS idm, 'Robert' as nom FROM DUAL UNION ALL
SELECT 2 AS idm, 'Marcel' as nom FROM DUAL UNION ALL
SELECT 3 AS idm, 'Squelettor' as nom FROM DUAL;

CREATE TABLE TEST_LJ_DETAIL AS
SELECT 1 AS ids, 1 as idm, 'M' as s FROM DUAL UNION ALL
SELECT 2 AS ids, 1 as idm, 'F' as s  FROM DUAL UNION ALL
SELECT 3 AS ids, 1 as idm, NULL as s  FROM DUAL UNION ALL
SELECT 4 AS ids, 2 as idm, 'F' as s  FROM DUAL;


La jointure externe, c'est comme une jointure normale, sauf que les lignes de gauche (pour LEFT, et droite pour RIGHT...) sont dans le résultat même si aucune ligne de la table de droite ne correspond au critères spécifiés dans la clause "ON".


SQL>  SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4   /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         1 Robert              2          1 F
         1 Robert              3          1
         2 Marcel              4          2 F
         3 Squelettor

Au passage, on rappelle que :
- INNER JOIN est strictement équivalent à JOIN
- LEFT OUTER JOIN est strictement équivalent à LEFT JOIN

Personnellement, j'utilise JOIN et LEFT OUTER JOIN. Non pas par paresse, mais parce que dans la théorie des ensembles, une "relation" est naturellement "INNER"... alors que la jointure externe est plus difficile à rapprocher d'un concept mathématique simple.
Ainsi, je trouve que LEFT JOIN peut donner l'illusion pour les débutants que l'ordre des tables dans une jointure "INNER" à une signification, alors que ce n'est bien évidemment pas le cas. C'est pour cela que je précise le OUTER.

Après, c'est une histoire de goûts...


1) Il y a NULL et NULL !



On veut à présent récupérer les relations des MAITREs avec des DETAILs masculins (DETAIL.s = 'M'), et ceux qui ne sont pas en relation avec des DETAILs masculins (qui du coup auront les colonnes DETAIL à NULL)

Première tentative :


SQL> SELECT *
  2  FROM TEST_LJ_MAITRE a
  3    LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4  WHERE b.s = 'M'
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M


Mon dieu, tout a disparu !
Ben oui, pour les MAITRE sans détail, le prédicat b.s = 'M' est faux !

Deuxième tentative :

SQL> SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4   WHERE b.s <> 'F'
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M


Ca ne marche pas mieux. Ben ouais, NULL, ce n'est pas vraiment une valeur.
Quand b.s IS NULL, la question b.s <> 'F' a pour réponse : indéterminé !


Troisième tentative :

SQL> SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4   WHERE b.s = 'M' OR b.s IS NULL
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         1 Robert              3          1
         3 Squelettor

On a certes ramené Squelettor... mais on ramené une deuxième ligne pour Robert, avec une relation de s. indéterminé !


2) Mon dieu, ils ont encore tué ma jointure externe !


Quatrième tentative :
On se dit que ce qui caractérise le côté ouvert, c'est que l'identifiant de la table externe soit NOT NULL. Et donc, on va compléter la condition précédente :

SQL> SELECT *
  2    FROM TEST_LJ_MAITRE a
  3      LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4    WHERE b.s = 'M' OR (b.s IS NULL AND b.ids IS NULL)
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         3 Squelettor


Presque !
Mais le pauvre Marcel est passé à la trappe. Et je trouve que c'est le plus gros piège, car on pense souvent à inclure le NULL dans la clause WHERE... mais c'est vraiment insuffisant.
Ici, Marcel est en relation avec un seul DETAIL, mais de s = F.
Et donc, il passe la jointure
  "LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm"
Mais est refoulé dans le WHERE, quelle que soit la manière dont on le fasse !


Et du coup, quand un critère est vraiment "externe", il faut le mettre dans la clause ON qui est faite pour cela !

SQL>  SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4                      AND b.s = 'M';

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         2 Marcel
         3 Squelettor

Pas de discrimination, quelle que soit l'orientation !


3) Suivez la norme, tout le monde le dit !



Pour finir, la syntaxe "old school" :
Mettre un (+) dans les prédicats du WHERE.

SQL>  SELECT *
  2   FROM TEST_LJ_MAITRE a, TEST_LJ_DETAIL b
  3   WHERE a.idm = b.idm(+)
  4     AND b.s(+) = 'M';

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         2 Marcel
         3 Squelettor

Et effectivement, ça marche aussi.

Mais il faut rappeler que si cette syntaxe est disponible sous Oracle, ce n'est pas juste pour se démarquer des autres... mais simplement parce qu'elle a été mise en place avant la norme SQL 92 qui a standardisé les JOIN. (Attention, je n'ai pas dit légalisé, ni dépénalisé)


La preuve, c'est que même Oracle recommande la syntaxe normalisée :

Oracle Corporation recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause join syntax:[...]



(Ok, c'est un peu agressif le rouge, mais c'est pour bien insister !)

Tiré d'ici :
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2054625


Voilà, Voilà !

 

Partager cet article

Published by Pacman - dans SQL
commenter cet article

commentaires