IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Nouvelle syntaxe des jointures : une clause "WHERE" dans le FROM? [11gR2]


Sujet :

SQL Oracle

  1. #1
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut 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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  2. #2
    Membre averti
    Homme Profil pro
    Ingénieur en études décisionnelles
    Inscrit en
    Février 2013
    Messages
    134
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Ingénieur en études décisionnelles

    Informations forums :
    Inscription : Février 2013
    Messages : 134
    Points : 351
    Points
    351
    Par défaut
    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 ?

  3. #3
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 088
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 088
    Points : 38 393
    Points
    38 393
    Billets dans le blog
    9
    Par défaut
    Citation Envoyé par Ikebukuro Voir le message
    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.

  4. #4
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    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

    J'ai un jeu de tests avec les tables suivantes
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  5. #5
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    Citation Envoyé par Ikebukuro Voir le message
    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 Voir le message
    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 Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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;
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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.

  6. #6
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    ORA-01417:
    De mémoire la restriction a été soulevé en 12c.

    Sur le reste je vais me auto citer
    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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 6
    Dernier message: 28/01/2008, 12h29
  2. [Syntaxe] Lien d'une frame qui s'ouvre dans une autre
    Par Petrucci dans le forum Balisage (X)HTML et validation W3C
    Réponses: 1
    Dernier message: 02/04/2007, 23h14
  3. Syntaxe des jointures a droite/gauche dans le WHERE
    Par gregb34 dans le forum Langage SQL
    Réponses: 4
    Dernier message: 23/01/2007, 12h28

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo