Jointures SQL : syntaxe propriétaire Oracle VS syntaxe ANSI92 (celle là, je ne l'aime pas!) - SQL joins: Oracle VS ANSI syntax
Introduction
J'avoue, je déteste la syntaxe des jointures SQL introduite par la norme ANSI92, dite aussi SQL-92 et lui préférer de loin la syntaxe propriétaire d'Oracle!
Pour quelles raisons? On va vite voir ça mais, en résumé, cette syntaxe manque de rigueur car une même jointure peut s'écrire de X façons différentes et possède plusieurs clauses optionnelles alors que la syntaxe Oracle est bien plus stricte. Dit d'une autre façon, il est facile et rapide d'expliquer à quelqu'un comment fonctionne la syntaxe propriétaire d'Oracle mais bien plus long la syntaxe ANSI92, notamment à cause des nombreux pièges à éviter.
Pour rappel, cette syntaxe utilise les mots clés NATURAL JOIN, JOIN USING, JOIN ON, INNER et OUTER.
NATURAL JOIN : c'est Oracle qui décide sur quelles colonnes la jointure se fera, en se basant sur le nom des colonnes mais pas sur leur type . La jointure est une EQUI-JOINTURE : signe = utilisé. Je vous déconseille fortement de l'utiliser car vous ne maîtrisez rien sur comment la jointure se fera.
JOIN USING : on dit à Oracle sur quelles colonnes la jointure doit se faire. C'est utile quand on veut joindre deux tables sur seulement une partie des colonnes en commun, ce que ne permet pas le NATURAL JOIN. La jointure est une EQUI-JOINTURE : signe = utilisé. Attention : les colonnes de jointure doivent avoir le même nom dans les tables jointes.
JOIN ON : on dit à Oracle sur quelles colonnes de même nom ou de nom différent la jointure doit se faire. C'est la seule syntaxe pour joindre des tables avec des noms de colonnes différents ou bien si on veut faire des jointures qui ne soient pas des EQUI-JOINTURES.
INNER : à utiliser pour des jointures internes, à la différence de OUTER pour faire des jointures externes. Ce mot est optionnel.
OUTER : à utiliser pour des jointures externes. Ce mot est optionnel.
Points d'attention
Que les choses soient claires, je ne déconseille pas la syntaxe ANSI92, mon article est juste là pour attirer votre attention sur ses dangers.
Base de tests
N'importe quelle base Oracle.
Exemples
============================================================================================
Liste des raisons qui font que je n'aime pas la syntaxe ANSI92 - SQL92
============================================================================================
Raison 1) JOIN et INNER JOIN sont équivalents; le mot INNER est sous-entendu quand on fait une jointure --> donc deux syntaxes différentes pour la même jointure.
SQL> select count(*) from dba_tables NATURAL JOIN dba_tab_columns;
COUNT(*)
----------
3140
SQL> select count(*) from dba_tables NATURAL INNER JOIN dba_tab_columns;
COUNT(*)
----------
3140
Raison 2) Pour les jointures externes, le mot OUTER est sous-entendu --> donc deux syntaxes différentes pour la même jointure, comme pour le INNER.
SQL> select count(*) from dba_tables LEFT OUTER JOIN dba_tab_columns USING(table_name);
COUNT(*)
----------
22668
SQL> select count(*) from dba_tables LEFT JOIN dba_tab_columns USING(table_name);
COUNT(*)
----------
22668
SQL> select count(*) from dba_tables RIGHT OUTER JOIN dba_tab_columns USING(table_name);
COUNT(*)
----------
109857
SQL> select count(*) from dba_tables RIGHT JOIN dba_tab_columns USING(table_name);
COUNT(*)
----------
109857
SQL> select count(*) from dba_tables FULL OUTER JOIN dba_tab_columns USING(table_name);
COUNT(*)
----------
109884
SQL> select count(*) from dba_tables FULL JOIN dba_tab_columns USING(table_name);
COUNT(*)
----------
109884
Raison 3) Pour NATURAL JOIN, c'est Oracle qui décide des colonnes de jointure et pas l'utilisateur. Donc si la structure de la table évolue, ajout d'une colonne de même nom dans les deux tables ou changement de nom, alors la jointure sera modifiée et la requête pourra ne plus fonctionner.
Exemple de deux tables créées l'une à partir de l'autre avec les mêmes données donc.
SQL> desc zz_test01;
Nom NULL ? Type
------------------------------ -------- -------------------------
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(23)
ID NUMBER
SQL> desc zz_test02
Nom NULL ? Type
------------------------------------------------------------------
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(23)
ID NUMBER
SQL> select count(*) from zz_test01 NATURAL JOIN zz_test02;
COUNT(*)
----------
72584
Maintenant nous ajoutons une colonne de même nom dans les tables, vide, sans valeur par défaut. Que se passe-t-il? Le NATURAL JOIN renvoit cette fois zéro enregistrement puisque, ayant le même nom, ces colonnes entrent naturellement dans la jointure.
SQL> alter table zz_test01 add ID02 NUMBER;
Table modifiée.
SQL> alter table zz_test02 add ID02 NUMBER;
Table modifiée.
SQL> select count(*) from zz_test01 NATURAL JOIN zz_test02;
COUNT(*)
----------
0
Raison 4) Si le NATURAL JOIN ne se fait pas, alors Oracle génère un produit cartésien s'il n'y a aucune colonne de même nom entre les tables. Selon moi il était plus logique de ne ramener aucune donnée plutôt que de faire un produit cartésien puisqu'on insiste bien, en disant NATURAL JOIN, qu'on veut qu'une jointure existe entre les deux tables. En outre il existe une expression spécifique pour dire qu'on veut un produit cartésien et c'est CROSS JOIN.
Exemple avec DBA_TABLES et DBA_SEQUENCES, ces vues ne partagent aucune colonne.
SQL> select column_name from dba_tab_cols where table_name = 'DBA_TABLES' AND column_name IN (select column_name from dba_tab_cols where table_name = 'DBA_SEQUENCES');
aucune ligne sélectionnée
SQL> select count(*) from dba_tables;
COUNT(*)
----------
2106
SQL> select count(*) from dba_sequences;
COUNT(*)
----------
298
Le NATURAL JOIN nous retourne bien un produit cartésien : 2106 * 298 = 627588 éléments
SQL> select count(*) from dba_tables NATURAL JOIN dba_sequences;
COUNT(*)
----------
627588
Raison 5) NATURAL JOIN, JOIN ON, JOIN USING : on peut écrire la même requête avec ces trois mots si le nom de la ou des colonnes de jointure est identique entre les deux tables.
SQL> select count(*) from dba_tables NATURAL JOIN dba_tab_privs;
COUNT(*)
----------
2004
SQL> select count(*) from dba_tables JOIN dba_tab_privs USING(TABLE_NAME, OWNER);
COUNT(*)
----------
2004
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON (dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME AND dba_tables.OWNER = dba_tab_privs.OWNER);
COUNT(*)
----------
2004
Raison 6) JOIN ON : les parenthèses ne sont pas obligatoires pour cette syntaxe ANSI92, même si on joint sur plusieurs colonnes. Vous me direz qu'avec la syntaxe propriétaire d'Oracle c'est pareil mais quitte à créer une nouvelle syntaxe, autant rendre obligatoire les parenthèses pour rendre la lecture bien plus facile. OK, je pinaille mais dieu que c'est parfois difficile de lire des requêtes faisant des dizaines de lignes sans indentation ni parenthèse.
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME AND dba_tables.OWNER = dba_tab_privs.OWNER;
COUNT(*)
----------
2004
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON (dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME AND dba_tables.OWNER = dba_tab_privs.OWNER);
COUNT(*)
----------
2004
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON (dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME) AND dba_tables.OWNER = dba_tab_privs.OWNER;
COUNT(*)
----------
2004
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON (dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME) AND (dba_tables.OWNER = dba_tab_privs.OWNER);
COUNT(*)
----------
2004
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME AND (dba_tables.OWNER = dba_tab_privs.OWNER);
COUNT(*)
----------
2004
Raison 7) JOIN USING : les parenthèses sont obligatoires pour JOIN USING alors qu'elles ne le sont pas pour JOIN ON. Pour quelle raison????? C'est totalement incompréhensible!
SQL> select count(*) from dba_tables JOIN dba_tab_privs USING TABLE_NAME;
Erreur SQL : ORA-00905: mot-clé absent
00905. 00000 - "missing keyword"
SQL> select count(*) from dba_tables JOIN dba_tab_privs USING (TABLE_NAME);
COUNT(*)
----------
2024
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME;
COUNT(*)
----------
2024
Raison 8) JOIN USING et NATURAL JOIN : il ne faut pas mettre de nom de table devant les colonnes de la jointure, même si elles font partie du SELECT. C'est donc l'inverse de la syntaxe propriétaire d'Oracle où on aura le message d'erreur problème de "colonne ambiguë" avec la syntaxe Oracle classique.
Syntaxe propriétaire d'Oracle : il faut préfixer par un nom de table le nom des colonnes existantes dans les deux tables.
SQL> select TABLE_NAME from dba_tables, dba_tab_privs where dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME;
Erreur SQL : ORA-00918: définition de colonne ambigu
00918. 00000 - "column ambiguously defined"
Avec ANSI92, la colonne TABLE_NAME servant à la jointure doit être utilisée sans préfixe de table.
SQL> select TABLE_NAME from dba_tables JOIN dba_tab_privs USING (TABLE_NAME);
2 024 lignes sélectionnées
SQL> select dba_tables.TABLE_NAME from dba_tables JOIN dba_tab_privs USING (TABLE_NAME);
Erreur SQL : ORA-25154: la partie colonne de la clause USING ne peut pas avoir de qualificatif
25154. 00000 - "column part of USING clause cannot have qualifier"
*Cause: Columns that are used for a named-join (either a NATURAL join or a join with a USING clause) cannot have an explicit qualifier.
Même problème avec NATURAL JOIN.
SQL> select dba_tables.TABLE_NAME from dba_tables NATURAL JOIN dba_tab_privs;
Erreur SQL : ORA-25155: la colonne utilisée dans la jointure NATURAL ne peut pas avoir de qualificatif
25155. 00000 - "column used in NATURAL join cannot have qualifier"
*Cause: Columns that are used for a named-join (either a NATURAL join or a join with a USING clause) cannot have an explicit qualifier.
Raison 9) Avec la syntaxe ANSI92, les filtres de la condition WHERE peuvent être déplacés dans le FROM alors que c'est impossible avec la syntaxe propriétaire d'Oracle. ATTENTION, cette possibilité ne fonctionne qu'avec la clause ON. A nouveau, pourquoi avoir permis cela, à quoi ça sert? Pourquoi permettre deux syntaxe différentes? Je ne vous raconte pas la tête d'un développeur qui tombe sur une requête SQL de ce type pour la première fois.
Exemple avec filtre dans la clause WHERE.
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON (dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME AND dba_tables.OWNER = dba_tab_privs.OWNER) WHERE dba_tables.owner <> 'SYS';
COUNT(*)
----------
862
Exemple avec filtre dans la clause FROM.
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON (dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME AND dba_tables.OWNER = dba_tab_privs.OWNER) and dba_tables.owner <> 'SYS';
COUNT(*)
----------
862
Raison 10) La jointure avec N tables peut se faire de deux façons différentes : soit en joignant les tables deux à deux avec une virgule soit en les joignant toutes ensembles les unes à la suite des autres. A nouveau on peut écrire la même requête de plusieurs façons différentes, ce qui est incroyable! ATTENTION : pour rajouter de la confusion, le résultat n'est pas le même entre les deux syntaxes mais celles-ci sont permises avec ANSI92 alors que c'est impossible avec la syntaxe propriétaire d'Oracle.
Exemple de jointure A/B/C avec A/B joint puis B/C joints.
SQL> select count(*) from dba_tables JOIN dba_tab_cols ON dba_tables.owner = dba_tab_cols.owner AND
dba_tables.table_name = dba_tab_cols.table_name AND dba_tables.owner = 'SYSTEM'
JOIN dba_tab_privs ON dba_tab_cols.TABLE_NAME = dba_tab_privs.TABLE_NAME;
COUNT(*)
----------
299
Jointure des tables A, B, C regroupées par bloc de deux: A JOIN B, B JOIN C en utilisant une virgule.
Les parenthèses ne sont pas obligatoires mais rendent le code plus lisible.
SQL> select count(*) from dba_tables JOIN dba_tab_cols ON dba_tables.owner = dba_tab_cols.owner AND
dba_tables.table_name = dba_tab_cols.table_name AND dba_tables.owner = 'SYSTEM', --il y a une virgule
dba_tab_cols JOIN dba_tab_privs ON dba_tab_cols.TABLE_NAME = dba_tab_privs.TABLE_NAME
AND dba_tab_cols.owner = 'SYSTEM';
COUNT(*)
----------
532656
Raison 11) Il est possible de mélanger les syntaxes NATURAL JOIN, USING et ON dans la même clause FROM mais, attention, le résultat n'est pas toujours le même.
SQL> select count(*) from dba_tables JOIN dba_tab_privs ON (dba_tables.TABLE_NAME = dba_tab_privs.TABLE_NAME) NATURAL JOIN dba_tab_cols;
COUNT(*)
----------
2665
SQL> select count(*) from dba_tables JOIN dba_tab_privs USING (TABLE_NAME) NATURAL JOIN dba_tab_cols;
COUNT(*)
----------
1783
Raison 12) Il existe avec ANSI92 deux façons de faire un produit cartésien : CROSS JOIN et NATURAL JOIN, s'il n'existe aucune colonne en commun entre les tables jointes. Avec la syntaxe Oracle, il n'en existe qu'une : ne pas joindre les tables.
SQL> select count(*) from dba_tables NATURAL JOIN dba_sequences;
COUNT(*)
----------
627588
SQL> select count(*) from dba_tables CROSS JOIN dba_sequences;
COUNT(*)
----------
627588
Raison 13) Avec NATURAL JOIN, la jointure se fait uniquement sur le nom des colonnes, sans tenir compte de leur type.
Soit deux tables identiques, avec le même contenu, les mêmes colonnes.
SQL> desc zz01
Nom NULL ? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(23)
ID NUMBER
SQL> desc zz02
Nom NULL ? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(23)
ID NUMBER
SQL> select count(*) from zz01 NATURAL JOIN zz02;
COUNT(*)
----------
72584
On ajoute maintenant aux deux tables une colonne de même nom mais l'une est de type NUMBER dans une table et l'autre est de type VARCHAR2 dans l'autre table. Ensuite on y met les mêmes valeurs, à savoir la pseudo colonne rownum.
SQL> alter table zz01 add COLTEST NUMBER(5);
Table modifiée.
SQL> alter table zz02 add COLTEST VARCHAR2(5);
Table modifiée.
SQL> update zz01 set coltest = rownum;
72584 lignes mises à jour.
SQL> update zz02 set coltest = rownum;
72584 lignes mises à jour.
Le NATURAL JOIN fonctionne encore, Oracle faisant une conversion interne entre les deux types NUMBER et VARCHAR2.
SQL> select count(*) from zz01 NATURAL JOIN zz02;
COUNT(*)
----------
72584
Je change maintenant le type de la colonne COLTEST dans la table zz02 et la transforme en colonne de type DATE.
SQL> update zz02 set coltest = NULL;
72584 lignes mises à jour.
SQL> alter table zz02 modify coltest DATE;
Table modifiée.
SQL> update zz02 set coltest = sysdate;
72584 lignes mises à jour.
Cette fois le NATURAL JOIN échoue puisque les types des colonnes de jointure sont incompatibles. C'est génial vous vous dites, on détecte l'erreur mais le but du NATURAL JOIN n'est-il pas "je laisse entièrement au SGBD la responsabilité de faire la jointure, à lui de se débrouiller pour qu'elle réussisse, je ne veux pas savoir quelles colonnes il utilise ni quel est leur type"? Dans notre exemple je pense qu'Oracle aurait dû exclure la colonne COLTEST de la jointure et la faire uniquement sur les colonnes de même nom et de même type. Avec une jointure de type USING ou ON, je comprendrais que le test soit fait mais pas pour NATURAL JOIN. Il va de soit que c'est une opinion personnelle :-)
SQL> select count(*) from zz01 NATURAL JOIN zz02;
select count(*) from zz01 NATURAL JOIN zz02
*
ERREUR à la ligne 1 :
ORA-00932: types de données incohérents ; attendu : NUMBER ; obtenu : DATE
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)