Nouvelle syntaxe des jointures : une clause "WHERE" dans le FROM?

Bonjour,

Sous Oracle j'utilise de préférence la syntaxe classique où les jointures sont mises dans la clause WHERE.
Avec la nouvelle syntaxe, les jointures sont dans la clause FROM avec les mots clés JOIN, INNER JOIN, NATURAL JOIN, USING, ON...

OK, je n'aime pas trop pour diverses raisons mais j'arrive à lire le code.
SAUF que hier, j'ai eu le code suivant :

Code:

---

1 2 3 4 5 6 7

select a.bic_name from bic_code a JOIN bic_instruct_client b ON (a.BIC_BANK_CODE = b.BIC_BANK_CODE and a.BIC_BRANCH_CODE = b.BIC_BRANCH_CODE AND a.BIC_NAME like 'W%') Order by 1;

---

C'est quoi ce truc? La jointure se fait sur les deux tables avec deux colonnes MAIS pourquoi avoir ajouté AND a.BIC_NAME like 'W%'? Je ne me suis pas démonté et j'ai lancé la requête suivante où j'ai déporté le like dans le where.

Code:

---

1 2 3 4 5 6 7

select a.bic_name from bic_code a JOIN bic_instruct_client b ON (a.BIC_BANK_CODE = b.BIC_BANK_CODE and a.BIC_BRANCH_CODE = b.BIC_BRANCH_CODE) WHERE a.BIC_NAME like 'W%’ order by 1;

---

Le résultat est le MEME dans les deux cas :aie:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

BIC_NAME W AND W ADVISORY DUBLIN LIMITED W AND W ASSET MANAGEMENT DUBLI W FINANCE W FINANCE ADM W FINANCE ASS WATERFORD CAPITAL INVEST PLC WAVES FINANCIAL INVESTMENTS PLC WELLS FARGO GLOBAL FUND SERVICES UK WEST MERCHANT LONDON (GLOBAL D WILLIAMS DE BROE PLC WILLIAMS DE BROE PLC LONDON

---

J'en conclus qu'on peut mettre les conditions de sélection dans la jointure avec cette nouvelle syntaxe ou bien dans le where; le gros bordel quoi; c'est une découverte et je l'aime de moins en moins cette syntaxe...

Est-ce que j'ai bien interprété la chose ou bien je me suis planté?
Sinon, est-ce que vous auriez un lien présentant de manière exhaustive mais pas trop longue cette syntaxe car en cherchant sur le net je n'ai pas trouvé de site mentionnant la fonctionnalité ci-dessus.

Bonjour,

Je suis d'accord avec toi, il vaut mieux mettre les filtres dans le where, sauf dans le cas particulier des jointures externes où alors cette syntaxe devient utile.

A voir ce lien.


Peut-être ta requête avait initialement été écrite en jointure externe et qu'elle a été modifiée par la suite ?

Citation:

---

Envoyé par Ikebukuro

J'en conclus qu'on peut mettre les conditions de sélection dans la jointure avec cette nouvelle syntaxe ou bien dans le where; le gros bordel quoi; c'est une découverte et je l'aime de moins en moins cette syntaxe...

---

Avec l'ancienne syntaxe, c'était pire puisqu'il n'y avait pas le choix : les critères de jointure et de filtrage étaient obligatoirement dans la clause where
Désormais (depuis 1992 je crois), on peut avantageusement distinguer les 2, ce qui permet de clarifier les requêtes
Après, que certains par méconnaissance ou à cause de modifs rapides mal analysées, ne respectent cette nouvelle philosophie, ne doit pas vous conduire à la rejeter en bloc.

Le problème avec cette syntaxe est qu'on peut écrire N requêtes différentes en utilisant plusieurs mots clés pour écrire la même chose et avoir le même résultat :aie::aie:

J'ai un jeu de tests avec les tables suivantes

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

TABLE bic_code Name Null? Type -------------------------------------- -------- ----------------------- BIC_BANK_CODE NOT NULL VARCHAR2(8) BIC_BRANCH_CODE NOT NULL VARCHAR2(3) BIC_NAME VARCHAR2(35) IC_BIC_CAC_TO_CAC_AUTH VARCHAR2(3) IC_BIC_TYPE NOT NULL VARCHAR2(3) BIC_END_DATE DATE     TABLE bic_instruct_client Name Null? Type ----------------------------------------- -------- ------------------------ BIC_BANK_CODE NOT NULL VARCHAR2(8) BIC_BRANCH_CODE NOT NULL VARCHAR2(3) ENT_CLI_ID NOT NULL NUMBER(12,)     TABLE bic_bank_code_transco Name Null? Type ----------------------------------------- -------- ------------------------ CODE_BIC NOT NULL VARCHAR2(11) CODE_BANQUE NOT NULL VARCHAR2(15) NOM_ETABLISSEMENT VARCHAR2(105) DATE_DEBUT_VALIDITE NOT NULL DATE DATE_FIN_VALIDITE NOT NULL DATE

---

JOIN et INNER JOIN sont equivalent; INNER est sous-entendu --> donc deux syntaxes différentes .
select count(*) from bic_code NATURAL JOIN bic_instruct_client;
select count(*) from bic_code NATURAL INNER JOIN bic_instruct_client;


NATURAL JOIN : Oracle décide des colonnes de jointure donc si la structure de la table évolue (ajout d'une colonne de même nom et type dans les deux tables), alors la jointure sera modifiée et notre requête peut ne plus fonctionner.

Natural join, Join on, Join using : on peut écrire la même requête en joignant deux tables avec ces trois mots clés si le nom de la colonne de jointure est identique entre les deux tables.
select count(*) from bic_code NATURAL JOIN bic_instruct_client;
select count(*) from bic_code JOIN bic_instruct_client USING (BIC_BANK_CODE);
select count(*) from bic_code a JOIN bic_instruct_client b ON (a.BIC_BANK_CODE = b.BIC_BANK_CODE);

Condition WHERE dans la jointure (voir mon post ci-dessus) : c'est pas top, avec cette syntaxe on a mis dans le from tout ce qui concernait les tables (noms, jointures), ce qui est bien MAIS on peut mettre les critères de sélection dans le FROM... il doit y avoir une raison mais bon...

La jointure avec N tables peut se faire de deux façons différentes : soit en joignant les tables deux à deux 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 "confusant".

Jointure A/B/C avec A/B et B/C

Code:

---

1 2 3 4 5 6 7 8 9 10 11

SELECT A.BIC_NAME FROM BIC_CODE A JOIN BIC_INSTRUCT_CLIENT B ON (A.BIC_BANK_CODE = B.BIC_BANK_CODE AND A.BIC_BRANCH_CODE = B.BIC_BRANCH_CODE) JOIN BIC_BANK_CODE_TRANSCO C ON B.BIC_BANK_CODE = C.CODE_BIC WHERE A.BIC_NAME LIKE 'W%' ORDER BY 1;

---

Jointure des tables A, B, C regroupées par bloc de deux: A JOIN B, B JOIN C.
Les parenthèses ne sont pas obligatoires mais rendent le code plus lisible.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT A.BIC_NAME FROM ( BIC_CODE A JOIN BIC_INSTRUCT_CLIENT B ON (A.BIC_BANK_CODE = B.BIC_BANK_CODE AND A.BIC_BRANCH_CODE = B.BIC_BRANCH_CODE) ), -- Il faut une virgule pour séparer les blocs dans cette syntaxe ( BIC_INSTRUCT_CLIENT D JOIN BIC_BANK_CODE_TRANSCO C ON D.BIC_BANK_CODE = C.CODE_BIC ) WHERE A.BIC_NAME LIKE 'W%' ORDER BY 1;

---

A nouveau je n'aime pas cette syntaxe car elle est trop "ouverte", je trouve qu'elle manque de rigueur dans sa syntaxe mais bon, elle est utilisée donc il faut la connaître.

En revanche elle permet de faire une FULL jointure externe en seulement un SELECT alors qu'avec la syntaxe classique il faut deux select et un UNION.

Citation:

---

Envoyé par Ikebukuro

NATURAL JOIN : Oracle décide des colonnes de jointure donc si la structure de la table évolue (ajout d'une colonne de même nom et type dans les deux tables), alors la jointure sera modifiée et notre requête peut ne plus fonctionner.

---

Oracle n'a rien décidé, il a implémenté les spécifications ANSI.

N'utilisez jamais NATURAL JOIN, c'est une mauvaise idée tout court.
Derrière cette syntaxe il y a eu je pense une volonté de coller à la jointure algébrique où on ne précise pas le contenu des jointures.
Dans le monde réel il ne faut jamais l'utiliser car les applications et les bases de données évoluent.

Citation:

---

Envoyé par Ikebukuro

La jointure avec N tables peut se faire de deux façons différentes : soit en joignant les tables deux à deux 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 "confusant".
Jointure A/B/C avec A/B et B/C

---

Je ne vois pas la différence avec la syntaxe non-ANSI.
Si vous voulez écrire select * from A, B, B, C, vous pouvez.
Les deux requêtes que vous écrivez ensuite sont différentes en terme de résultat, la deuxième étant plus que probablement très fausse.

Citation:

---

Envoyé par Ikebukuro

Les parenthèses ne sont pas obligatoires mais rendent le code plus lisible.

---

Je ne suis pas d'accord, une bonne indentation fait largement l'affaire, en reprenant votre premier exemple :

Code:

---

1 2 3 4 5 6 7

select a.bic_name from bic_code a join bic_instruct_client b on b.bic_bank_code = a.bic_bank_code and b.bic_branch_code = a.bic_branch_code join bic_bank_code_transco c on c.code_bic = b.bic_bank_code where a.bic_name like 'W%' order by 1;

---

Les jointures ANSI : que vous les utilisiez ou pas, c'est à vous de voir sachant que ça n'impliquera rien sur les performances, Oracle réécrivant les requêtes ANSI en non-ANSI pour ensuite les traiter.

En revanche, elles vous permettent d'écrire des requêtes que vous ne pouvez pas écrire avec la syntaxe non-ANSI, ou alors moins facilement.
Considérant ce jeu de données :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

with cte_tab1 (c1, c2) as ( select 1, 'A' from dual union all select 2, 'B' from dual union all select 3, 'C' from dual ) , cte_tab2 (c1, c3) as ( select 1, 100 from dual union all select 2, 200 from dual union all select 4, 400 from dual ) , cte_tab3 (c2, c3, c4) as ( select 'A', 100, date '2011-01-01' from dual union all select 'C', 300, date '2013-01-01' from dual )

---

La requête suivante est valide, simple à comprendre écrire et à relire :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

select t1.c1, t1.c2, t2.c3, t3.c4 from cte_tab1 t1 left join cte_tab2 t2 on t2.c1 = t1.c1 left join cte_tab3 t3 on t3.c2 = t1.c2 and t3.c3 = t2.c3 order by t1.c1 asc;   C1 C2 C3 C4 -- -- ---- --------- 1 A 100 2011-01-01 2 B 200 3 C

---

Si on l'essaie directement en non-ANSI, on attrape une erreur :

Code:

---

1 2 3 4 5 6 7 8

select t1.c1, t1.c2, t2.c3, t3.c4 from cte_tab1 t1 , cte_tab2 t2 , cte_tab3 t3 where t2.c1(+) = t1.c1 and t3.c2(+) = t1.c2 and t3.c3(+) = t2.c3 order by t1.c1 asc;

---

Citation:

---

ORA-01417: une table peut être de jointure externe pour une autre table au moins
01417. 00000 - "a table may be outer joined to at most one other table"
*Cause: a.b (+) = b.b and a.c (+) = c.c is not allowed
*Action: Check that this is really what you want, then join b and c first
in a view.

---

Le message d'erreur explique la solution, il faut écrire :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

, cte_temp (c1, c2, c3) as ( select t1.c1, t1.c2, t2.c3 from cte_tab1 t1 , cte_tab2 t2 where t2.c1(+) = t1.c1 ) select tp.c1, tp.c2, tp.c3, t3.c4 from cte_temp tp , cte_tab3 t3 where t3.c2(+) = tp.c2 and t3.c3(+) = tp.c3 order by tp.c1;

---

Vous m'accorderez qu'on se fait un peu plus de nœuds au cerveau, surtout en condition réelle.

Allez, à vous de trouver l'équivalence de cette syntaxe :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

select t1.c1, t1.c2, t2.c3, t3.c4 from cte_tab1 t1 left join cte_tab2 t2 on t2.c1 = t1.c1 left join cte_tab3 t3 on t3.c3 = t2.c3 or t3.c2 = t1.c2 order by t1.c1 asc;   C1 C2 C3 C4 -- -- ---- --------- 1 A 100 2011-01-01 2 B 200 3 C 2013-01-01

---

Un autre conseil, si vous devez faire plusieurs jointures externes, faites les toujours dans le même sens : ne mélangez pas les LEFT et RIGHT, ça complique toujours la relecture.

Citation:

---

ORA-01417:

---

De mémoire la restriction a été soulevé en 12c.

Sur le reste je vais me auto citer

Citation:

---

Rien n'empêche d'écrire des requêtes erronées peu importe que c'est la norme ANSI ou pas!
Rien n'empêche d'écrire des requêtes compréhensible peu importe que c'est la norme ANSI ou pas!
Malheureusement, avec chaque version d'Oracle (y compris la 12c) on répertorie des bugs avec l'écriture ANSI

---