banner



Mot Qui Commence Par Un Z

LE SQL de A à Z : 3e partie - les jointures

Dans le précédent article, nous avons commencé à décortiquer le simple SELECT. Dans le présent, nous allons nous consacrer aux jointures entre tables. Toutes les jointures sous toutes les coutures !
Autrement dit, annotate faire des requêtes portant sur plusieurs tables. ♪

Commodity lu fois.

I. Préambule▲

La structure de la base de données exemple, ainsi qu'une version des principales bases utilisées sont disponibles dans la page « La base de données exemple »

Two. Les jointures ou comment faire des requêtes sur plusieurs tables▲

Vous trouverez des compléments d'information sur le sujet aux pages 129 à 152 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

Les jointures permettent d'exploiter pleinement le modèle relationnel des tables d'une base de données.
Elles sont faites pour mettre en relation deux (ou plus) tables concourant à rechercher la réponse à des interrogations. Une jointure permet donc de combiner les colonnes de plusieurs tables.

Il existe en fait différentes natures de jointures que nous expliciterons plus en détail. Retenez cependant que la plupart des jointures entre tables s'effectuent en imposant 50'égalité des valeurs d'une colonne d'une tabular array à une colonne d'une autre table. On parle alors de jointure naturelle ou équijointure. Mais on trouve aussi des jointures d'une table sur elle-même. On parle alors d'autojointure.
De même, il arrive que l'on doive procéder à des jointures externes, c'est-à-dire joindre une table à une autre, même si la valeur de liaison est absente dans une table ou l'autre. Enfin, dans quelques cas, on peut procéder à des jointures hétérogènes, c'est-à-dire que fifty'on remplace le critère d'égalité par un critère d'inégalité ou de différence.
Nous verrons au moins un cas de cette espèce.

Une jointure entre tables peut être mise en œuvre, soit à l'aide des éléments de syntaxe SQL que nous avons déjà vus, soit à l'aide d'une clause spécifique du SQL, la clause Bring together. Nous allons commencer par voir annotate à l'aide du SQL de base nous pouvons exprimer une jointure.

Two-A. Premiers essais de jointure▲

Rappel de la syntaxe du SELECT :

                                          SELECT                      [Distinct ou ALL]                      *                      ou liste_de_colonnes                      FROM                      nom_des_tables_ou_des_vues                  

C'est ici le pluriel de la partie FROM qui change tout !

Tâchons donc de récupérer les n° des téléphones associés aux clients.

Exemple 1 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT, T_TELEPHONE                            
                              CLI_NOM  TEL_NUMERO -------  -------------- DUPONT   01-45-42-56-63 DUPONT   01-44-28-52-52 DUPONT   01-44-28-52-50 DUPONT   06-11-86-78-89 DUPONT   02-41-58-89-52 DUPONT   01-51-58-52-l DUPONT   01-54-eleven-43-21 DUPONT   06-55-41-42-95 DUPONT   01-48-98-92-21 DUPONT   01-44-22-56-21 ...                            


Cette requête ne possède pas de critère de jointure entre une table et l'autre. Dans ce cas, le compilateur SQL calcule le produit cartésien des deux ensembles, c'est-à-dire qu'à chaque ligne de la première table, il accole l'ensemble des lignes de la seconde à la manière d'une « multiplication des petits pains » !
Nous verrons qu'il existe une autre manière, normalisée cette fois, de générer ce produit cartésien. Mais cette requête est à proscrire.
Dans notre exemple elle génère 17 400 lignes !

Il faut donc définir absolument un critère de jointure.

Dans le cas présent, ce critère est la correspondance entre les colonnes contenant la référence de l'identifiant du client (CLI_ID).

Exemple ii :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT, T_TELEPHONE                                WHERE                                CLI_ID                                =                                CLI_ID                            
                              CLI_NOM  TEL_NUMERO -------  -------------- DUPONT   01-45-42-56-63 DUPONT   01-44-28-52-52 DUPONT   01-44-28-52-50 DUPONT   06-11-86-78-89 DUPONT   02-41-58-89-52 DUPONT   01-51-58-52-fifty DUPONT   01-54-xi-43-21 DUPONT   06-55-41-42-95 DUPONT   01-48-98-92-21 DUPONT   01-44-22-56-21 ...                            


Nous n'avons pas fait mieux, automobile nous avons créé une clause toujours vraie, un peu à la manière de 1 = 1 !
En fait il nous manque une précision : il southward'agit de déterminer de quelles tables proviennent les colonnes CLI_ID de droite et de gauche. Cela se précise à 50'adjutant d'une notation pointée en donnant le nom de la tabular array.

Il est donc nécessaire d'indiquer au compilateur la provenance de chacune des colonnes CLI_ID et donc d'opérer une distinction entre 50'une et l'autre colonne.
Ainsi, chaque colonne devra être précédée du nom de la tabular array, suivi d'un point.

Exemple 3 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT, T_TELEPHONE                                WHERE                                T_CLIENT.CLI_ID                                =                                T_TELEPHONE.CLI_ID                            
                              CLI_NOM   TEL_NUMERO -------   -------------- DUPONT    01-45-42-56-63 DUPONT    01-44-28-52-52 DUPONT    01-44-28-52-50 BOUVIER   06-eleven-86-78-89 DUBOIS    02-41-58-89-52 DREYFUS   01-51-58-52-50 DUHAMEL   01-54-11-43-21 BOUVIER   06-55-41-42-95 MARTIN    01-48-98-92-21 MARTIN    01-44-22-56-21 ...                            


On tombe ici à 174 enregistrements dans la table !

Mais il existe une autre façon de faire, plus simple encore. On utilise la technique du « surnommage », c'est-à-dire que l'on attribue un surnom à chacune des tables présentes dans la partie FROM du SELECT :

Exemple 4 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT C, T_TELEPHONE T                                WHERE                                C.CLI_ID                                =                                T.CLI_ID                            
                              CLI_NOM   TEL_NUMERO -------   -------------- DUPONT    01-45-42-56-63 DUPONT    01-44-28-52-52 DUPONT    01-44-28-52-50 BOUVIER   06-11-86-78-89 DUBOIS    02-41-58-89-52 DREYFUS   01-51-58-52-50 DUHAMEL   01-54-11-43-21 BOUVIER   06-55-41-42-95 MARTIN    01-48-98-92-21 MARTIN    01-44-22-56-21 ...                            


Ici, la table T_CLIENT a été surnommée « C » et la tabular array T_TELEPHONE « T ».

Bien entendu, et comme dans les requêtes monotabulaires on peut poser des conditions supplémentaires de filtrage dans la clause WHERE. Cherchons par exemple les clients dont les numéros de téléphone correspondent à un fax :

Exemple 5 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT C, T_TELEPHONE T                                WHERE                                C.CLI_ID                                =                                T.CLI_ID                                AND                                TYP_CODE                                =                                'FAX'                                                          
                              CLI_NOM   TEL_NUMERO -------   -------------- DUPONT    01-44-28-52-50 MARTIN    01-44-22-56-21 DUHAMEL   01-54-11-43-89 DUPONT    05-59-45-72-42 MARTIN    01-47-66-29-55 DUBOIS    04-66-62-95-64 DREYFUS   04-92-19-18-58 DUHAMEL   01-55-lx-93-81 PHILIPPE  01-48-44-86-nineteen DAUMIER   01-48-28-17-95 ...                            


Le fait de placer comme critère de jointure entre les tables, fifty'opérateur logique « égal » donne ce que l'on appelle une « équijointure ».

Comme vous pouvez le constater, le nom du customer « BOUVIER » northward'apparaît pas. Il n'a pas été « oublié » par le traitement de la requête, mais le numéro de fax de ce client n'est pas présent dans la table T_TELEPHONE. Or le moteur SQL recherche les valeurs de la jointure par égalité. Comme l'ID_CLI de « BOUVIER » n'est pas présent dans la table T_TELEPHONE, il ne peut effectuer la jointure et ignore donc cette ligne. Nous verrons annotate réparer cette lacune lorsque nous parlerons des jointures externes.

On peut aussi utiliser les surnoms dans la partie qui suit immédiatement le mot clef SELECT. Ainsi l'exemple iv, peut aussi s'écrire :

Exemple 6 :

                                                              SELECT                                C.CLI_ID, C.CLI_NOM, T.TEL_NUMERO                                FROM                                T_CLIENT C, T_TELEPHONE T                                WHERE                                C.CLI_ID                                =                                T.CLI_ID                                AND                                T.TYP_CODE                                =                                'FAX'                                                          
                              CLI_ID  CLI_NOM   TEL_NUMERO ------  -------   --------------      1  DUPONT    01-44-28-52-fifty     x  MARTIN    01-44-22-56-21      eight  DUHAMEL   01-54-11-43-89      1  DUPONT    05-59-45-72-42      2  MARTIN    01-47-66-29-55      4  DUBOIS    04-66-62-95-64      5  DREYFUS   04-92-nineteen-18-58      viii  DUHAMEL   01-55-threescore-93-81     xiii  PHILIPPE  01-48-44-86-19     fifteen  DAUMIER   01-48-28-17-95 ...                            


C'est particulièrement pratique lorsque l'on veut récupérer une colonne qui se retrouve dans les deux tables, ce qui est souvent le cas de la (ou les) colonne(s) de clef étrangère qui permet justement d'assurer la jointure.

Cascade joindre plusieurs tables, on peut utiliser le même processus de manière répétitive…

Exemple seven :

                                                              SELECT                                C.CLI_ID, C.CLI_NOM, T.TEL_NUMERO, E.EML_ADRESSE, A.ADR_VILLE                                FROM                                T_CLIENT C, T_TELEPHONE T, T_ADRESSE A, T_EMAIL Due east                                WHERE                                C.CLI_ID                                =                                T.CLI_ID                                AND                                C.CLI_ID                                =                                A.CLI_ID                                AND                                C.CLI_ID                                =                                Eastward.CLI_ID                            
                              CLI_ID   CLI_NOM  TEL_NUMERO      EML_ADRESSE                        ADR_VILLE ------  --------  --------------  -----------------------            ---------      1  DUPONT    01-45-42-56-63  alain.dupont@wanadoo.fr            VERSAILLES      one  DUPONT    05-59-45-72-42  alain.dupont@wanadoo.fr            VERSAILLES      1  DUPONT    05-59-45-72-24  alain.dupont@wanadoo.fr            VERSAILLES      i  DUPONT    01-44-28-52-l  alain.dupont@wanadoo.fr            VERSAILLES      1  DUPONT    01-44-28-52-52  alain.dupont@wanadoo.fr            VERSAILLES      two  MARTIN    01-47-66-29-29  mmartin@transports_martin_fils.fr  VERGNOLLES CEDEX 452      2  MARTIN    01-47-66-29-55  mmartin@transports_martin_fils.fr  VERGNOLLES CEDEX 452      ii  MARTIN    01-47-66-29-29  plongeur@aol.com                   VERGNOLLES CEDEX 452      2  MARTIN    01-47-66-29-55  plongeur@aol.com                   VERGNOLLES CEDEX 452      5  DREYFUS   04-92-19-18-58  pdreyfus@gild-internet.fr          PARIS ...                            

De même que nous l'avons vu dans l'exemple 2.4, ne sont visibles ici que les lignes clients ayant à la fois, au moins une adresse, un electronic mail et au moins un numéro de téléphone. Si nous avions voulu une liste complète des clients avec toutes les coordonnées disponibles, nous aurions du faire une requête externe sur les tables.

II-B. Différents types de jointures (naturelles, équi, not equi, auto, externes, hétérogènes, croisées et marriage)▲

Lorsque nous étudions le modèle relationnel de notre base de données exemple nous avons vu que le modèle physique des données, répercute les clefs des tables maîtres en tant que clefs étrangères des tables pour lesquelles une jointure est nécessaire. En utilisant la jointure entre clefs primaires et clefs secondaires basée sur l'égalité des valeurs des colonnes, nous exécutons ce que les professionnels du SQL appellent une jointure naturelle.
Il est aussi possible de faire des équijointures qui ne sont pas naturelles, soit par accident (une erreur !), soit par nécessité.
Il est aussi possible de faire des not équi-jointures, c'est-à-dire des jointures basées sur un critère différent de l'égalité, mais aussi des autojointures, c'est-à-dire de joindre la table sur elle-même. Le cas le plus délicat à comprendre est celui des jointures externes, c'est-à-dire exiger que le résultat comprenne toutes les lignes des tables (ou d'au moins une des tables de la jointure), même southward'il n'y a pas correspondance des lignes entre les différentes tables mises en œuvre dans la jointure.
La jointure d'spousal relationship consiste à ajouter toutes les données des deux tables à condition qu'elles soient compatibles dans leurs structures.
La jointure croisée permet de faire le produit cartésien des tables.
Enfin on peut effectuer des requêtes hétérogènes, c'est-à-dire de joindre une table d'une base de données, à une ou plusieurs autres bases de données éventuellement même sur des serveurs différents, voire même sur des serveurs de différents types (par exemple joindre une tabular array T_CLIENT de la base BD_COMMANDE d'un serveur Oracle à la table T_PROSPECT de la base BD_COMMERCIAL d'united nations serveur Sybase !).

Dans la mesure du possible, utilisez toujours un opérateur de jointure normalisé Sql2 (mot clef JOIN).

En effet :

  • les jointures faites dans la clause WHERE (ancienne syntaxe de 1986 !) ne permettent pas de faire la stardom de prime abord entre ce qui relève du filtrage et ce qui relève de la jointure ;
  • il est à priori absurde de vouloir filtrer dans le WHERE (ce qui restreint les données du résultat) et de vouloir « élargir » ce résultat par une jointure dans la même clause WHERE de filtrage ;
  • la lisibilité des requêtes est plus grande en utilisant la syntaxe à base de JOIN, en isolant ce qui est du filtrage et de la jointure, mais aussi en isolant avec clarté chaque condition de jointures entre chaque couple de tables ;
  • l'optimisation d'exécution de la requête est souvent plus pointue du fait de 50'utilisation du Join ;
  • lorsque 50'on utilise fifty'ancienne syntaxe et que l'on supprime la clause WHERE à des fins de tests, le moteur SQL réalise le produit cartésien des tables ce qui revient la plupart du temps à mettre à genoux le serveur !

Iii. Syntaxe normalisée des jointures▲

Image non disponible

Vous trouverez des compléments d'information sur le sujet aux pages 136 à 152 de 50'ouvrage « SQL », drove « La Référence », Campus Press éditeur.

Les jointures normalisées south'expriment à l'aide du mot clef JOIN dans la clause FROM. Suivant la nature de la jointure, on devra préciser sur quels critères se base of operations la jointure.

Voici un tableau résumant les différents types de jointures normalisées :

Jointure interne

                                                          SELECT                              ...                              FROM                              <                              tabular array                              gauche>                              [INNER]                              JOIN                              <                              table                              droite>                              ON                              <                              condition                              de jointure>                                                      

Jointure externe

                                                          SELECT                              ...                              FROM                              <                              table                              gauche>                              LEFT                              |                              RIGHT                              |                              FULL                              OUTER                              JOIN                              <                              table                              droite>                              ON                              condition                              de jointure                          

Jointure naturelle

                                                          SELECT                              ...                              FROM                              <                              table                              gauche>                              NATURAL                              JOIN                              <                              table                              droite>                              [USING <noms de colonnes>]                                                      

Jointure croisée

                                                          SELECT                              ...                              FROM                              <                              table                              gauche>                              Cantankerous                              JOIN                              <                              tabular array                              droite>                                                      

Jointure d'union

                                                          SELECT                              ...                              FROM                              <                              table                              gauche>                              Spousal relationship                              JOIN                              <                              table                              droite>                                                      


Nous allons décrire en détail toutes ces jointures.

Iii-A. Opérateur de jointure naturelle▲

Image non disponible

Il existe united nations opérateur normalisé pour effectuer en SQL la jointure naturelle des tables :

                                          SELECT                      [DISTINCT ou ALL]                      *                      ou liste de colonnes                      FROM                      table1                      NATURAL                      Bring together                      table2                      [USING (colonne1 [, colonne2 ...]                      )]                  


50'opérateur NATURAL JOIN permet d'éviter de préciser les colonnes concernées par la jointure.
Dans ce cas, le compilateur SQL va rechercher dans les deux tables, les colonnes dont le nom est identique. Bien entendu, le type de données doit être le même !

NOTA : on veillera au niveau de la modélisation et notamment au niveau du MPD (Modèle Physique de Données) que les noms des colonnes de clefs en relation avec d'autres tables par l'intermédiaire des clefs étrangères soient strictement identiques.

Exemple 8 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT                                NATURAL                                JOIN                                T_TELEPHONE                            
                              CLI_NOM  TEL_NUMERO -------  -------------- DUPONT    01-45-42-56-63 DUPONT    01-44-28-52-52 DUPONT    01-44-28-52-l BOUVIER   06-11-86-78-89 DUBOIS    02-41-58-89-52 DREYFUS   01-51-58-52-50 DUHAMEL   01-54-11-43-21 BOUVIER   06-55-41-42-95 MARTIN    01-48-98-92-21 MARTIN    01-44-22-56-21 ...                            


Mais cette syntaxe est rarement acceptée par les moteurs SQL actuels !

La partie optionnelle USING permet de restreindre les colonnes concernées, lorsque plusieurs colonnes servent à définir la jointure naturelle. Ainsi la commande SQL :

                                          SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_CLIENT                      NATURAL                      JOIN                      T_TELEPHONE                      USING                      (CLI_ID)                                      

Revient au même que la commande SQL de l'exemple 8.

Iii-B. Les jointures internes▲

Image non disponible

Comme il s'agit de la plus commune des jointures, c'est celle qui s'exerce par défaut si on ne précise pas le blazon de jointure. Après le mot clef ON on doit préciser le critère de jointure.

Reprenons notre exemple de départ :

Exemple 9 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT                                INNER                                JOIN                                T_TELEPHONE                                ON                                T_CLIENT.CLI_ID                                =                                T_TELEPHONE.CLI_ID                            
                              CLI_NOM   TEL_NUMERO -------   -------------- DUPONT    01-45-42-56-63 DUPONT    01-44-28-52-52 DUPONT    01-44-28-52-50 BOUVIER   06-11-86-78-89 DUBOIS    02-41-58-89-52 DREYFUS   01-51-58-52-50 DUHAMEL   01-54-eleven-43-21 BOUVIER   06-55-41-42-95 MARTIN    01-48-98-92-21 MARTIN    01-44-22-56-21 ...                            


Ou en utilisant le surnommage :

Exemple 10 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT C                                INNER                                Join                                T_TELEPHONE T                                ON                                C.CLI_ID                                =                                T.CLI_ID                            
                              CLI_NOM   TEL_NUMERO -------   -------------- DUPONT    01-45-42-56-63 DUPONT    01-44-28-52-52 DUPONT    01-44-28-52-fifty BOUVIER   06-11-86-78-89 DUBOIS    02-41-58-89-52 DREYFUS   01-51-58-52-fifty DUHAMEL   01-54-11-43-21 BOUVIER   06-55-41-42-95 MARTIN    01-48-98-92-21 MARTIN    01-44-22-56-21 ...                            


Plus pratique à écrire et aussi lisible sinon plus !

NOTA : le mot clef INNER est facultatif. Par défaut 50'absence de précision de la nature de la jointure la fait due south'exécuter en jointure interne.
Ainsi on peut reformuler la requête ci-dessus en :

Exemple 11 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT C                                JOIN                                T_TELEPHONE T                                ON                                C.CLI_ID                                =                                T.CLI_ID                            
                              CLI_NOM   TEL_NUMERO -------   -------------- DUPONT    01-45-42-56-63 DUPONT    01-44-28-52-52 DUPONT    01-44-28-52-50 BOUVIER   06-11-86-78-89 DUBOIS    02-41-58-89-52 DREYFUS   01-51-58-52-50 DUHAMEL   01-54-11-43-21 BOYER     06-55-41-42-95 MARTIN    01-48-98-92-21 MARTIN    01-44-22-56-21 ...                            

III-C. Les jointures externes▲

Image non disponible

Les jointures externes sont extrêmement pratiques cascade rapatrier le maximum d'informations disponibles, même si des lignes de tabular array ne sont pas renseignées entre les différentes tables jointes.

Procédons à l'adjutant d'united nations exemple pour mieux comprendre la différence entre une jointure interne et une jointure externe. Nous avons vu à fifty'exemple 9 que seuls les clients dotés d'un numéro de téléphone étaient répertoriés dans la réponse. Ainsi, le client « BOUVIER » était absent-minded.

Exemple 12 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT C                                INNER                                Join                                T_TELEPHONE T                                ON                                C.CLI_ID                                =                                T.CLI_ID                                WHERE                                TYP_CODE                                =                                'FAX'                                                          
                              CLI_NOM   TEL_NUMERO -------   -------------- DUPONT    01-44-28-52-50 MARTIN    01-44-22-56-21 DUHAMEL   01-54-xi-43-89 DUPONT    05-59-45-72-42 MARTIN    01-47-66-29-55 DUBOIS    04-66-62-95-64 DREYFUS   04-92-19-eighteen-58 DUHAMEL   01-55-lx-93-81 PHILIPPE  01-48-44-86-19 DAUMIER   01-48-28-17-95 ...                            


Que faut-il modifier dans la requête cascade obtenir une ligne « BOUVIER » avec aucune référence de téléphone associée dans la réponse ?
Il suffit en fait d'opérer à l'aide d'une jointure externe :

Exemple 13 :

                                                              SELECT                                CLI_NOM, TEL_NUMERO                                FROM                                T_CLIENT C                                LEFT                                OUTER                                JOIN                                T_TELEPHONE T                                ON                                C.CLI_ID                                =                                T.CLI_ID                                WHERE                                TYP_CODE                                =                                'FAX'                                OR                                TYP_CODE                                IS                                NULL                                                          
                              CLI_NOM   TEL_NUMERO -------   -------------- DUPONT    01-44-28-52-fifty DUPONT    05-59-45-72-42 MARTIN    01-47-66-29-55 BOUVIER   NULL  DUBOIS    04-66-62-95-64 DREYFUS   04-92-xix-18-58 FAURE     Zero LACOMBE   NULL  DUHAMEL   01-54-11-43-89 DUHAMEL   01-55-threescore-93-81 ...                            


ou encore :

                                          SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_CLIENT C                      LEFT                      OUTER                      JOIN                      T_TELEPHONE T                      ON                      C.CLI_ID                      =                      T.CLI_ID                      AND                      TYP_CODE                      IS                      NULL                                      

La syntaxe de la jointure externe est la suivante :

                                          SELECT                      ...                      FROM                      <                      table                      gauche>                      LEFT                      |                      Correct                      |                      FULL                      OUTER                      Bring together                      <                      table                      droite                      i                      >                      ON                      <                      condition                      de jointure>                      [LEFT | RIGHT | FULL OUTER JOIN <tabular array droite 2>                      ON                      <                      condition                      de jointure                      2                      >] ...                  


Les mots clefs LEFT, RIGHT et FULL indiquent la manière dont le moteur de requête doit effectuer la jointure externe. Ils font référence à la tabular array située à gauche (LEFT) du mot clef JOIN ou à la table située à droite (Correct) de ce même mot clef. Le mot FULL indique que la jointure externe est bilatérale.

                                                              SELECT                                colonnes                                FROM                                TGauche                                LEFT                                OUTER                                Bring together                                TDroite                                ON                                condition                                de jointure                            

On recherche toutes les valeurs satisfaisant la condition de jointure précisée dans prédicat, puis on rajoute toutes les lignes de la table TGauche qui n'ont pas été prises en compte au titre de la satisfaction du critère.

                                                              SELECT                                colonnes                                FROM                                TGauche                                RIGHT                                OUTER                                JOIN                                TDroite                                ON                                condition                                de jointure                            

On recherche toutes les valeurs satisfaisant la condition de jointure précisée dans prédicat, puis on rajoute toutes les lignes de la table TDroite qui n'ont pas été prises en compte au titre de la satisfaction du critère.

                                                              SELECT                                colonnes                                FROM                                TGauche                                Full                                OUTER                                JOIN                                TDroite                                ON                                status                                de jointure                            

On recherche toutes les valeurs satisfaisant la condition de jointure précisée dans prédicat, puis on rajoute toutes les lignes de la table TGauche et TDroite qui n'ont pas été prises en compte au titre de la satisfaction du critère.


NOTA : il existe des équivalences entre différentes expressions logiques à base of operations de jointures externes. Les principales sont :

  • la jointure externe droite peut être obtenue par une jointure externe gauche dans laquelle on inverse l'ordre des tables ;
  • la jointure externe bilatérale peut être obtenue par la combinaison de deux jointures externes unilatérales avec 50'opérateur ensembliste Union.

Remplacement d'une jointure externe droite par une jointure externe gauche.
L'équivalent logique de :

Exemple 14 :

                                          SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_CLIENT C                      Correct                      OUTER                      Join                      T_TELEPHONE T                      ON                      C.CLI_ID                      =                      T.CLI_ID                      WHERE                      TYP_CODE                      =                      'FAX'                                      


est :

                                          SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_TELEPHONE T                      LEFT                      OUTER                      JOIN                      T_CLIENT C                      ON                      C.CLI_ID                      =                      T.CLI_ID                      WHERE                      TYP_CODE                      =                      'FAX'                                      

Remplacement d'un Total OUTER JOIN avec jointures externes gauche et droite :
L'équivalent logique de…

Exemple 15 :

                                          SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_CLIENT C                      Full                      OUTER                      Join                      T_TELEPHONE T                      ON                      C.CLI_ID                      =                      T.CLI_ID                      WHERE                      TYP_CODE                      =                      'FAX'                                      


est :

                                          SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_CLIENT C                      LEFT                      OUTER                      JOIN                      T_TELEPHONE T                      ON                      C.CLI_ID                      =                      T.CLI_ID                      WHERE                      TYP_CODE                      =                      'FAX'                      UNION                      SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_CLIENT C         RIGTH                      OUTER                      JOIN                      T_TELEPHONE T                      ON                      C.CLI_ID                      =                      T.CLI_ID                      WHERE                      TYP_CODE                      =                      'FAX'                                      

Remplacement d'un FULL OUTER JOIN avec jointures externes gauche uniquement :
L'équivalent logique de…

Exemple 16 :

                                          SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_CLIENT C                      FULL                      OUTER                      Join                      T_TELEPHONE T                      ON                      C.CLI_ID                      =                      T.CLI_ID                      WHERE                      TYP_CODE                      =                      'FAX'                                      


est :

                                          SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_CLIENT C                      LEFT                      OUTER                      Join                      T_TELEPHONE T                      ON                      C.CLI_ID                      =                      T.CLI_ID                      WHERE                      TYP_CODE                      =                      'FAX'                      Marriage                      SELECT                      CLI_NOM, TEL_NUMERO                      FROM                      T_TELEPHONE T                      LEFT                      OUTER                      Bring together                      T_CLIENT C                      ON                      C.CLI_ID                      =                      T.CLI_ID                      WHERE                      TYP_CODE                      =                      'FAX'                                      

III-D. Différence entre jointure externe et jointure interne▲

Pour bien comprendre la distinction entre les jointures internes et externes, nous devons consacrer quelques instants à aborder des problèmes de logique ensembliste sous un œil pragmatique.

Iii-D-1. Fifty'hypothèse du monde clos▲

Les jointures externes sont extrêmement pratiques cascade rapatrier le maximum d'informations disponibles, même si des lignes de tabular array ne sont pas renseignées entre les différentes tables jointes.

Sans le savoir, nous faisons assez systématiquement l'hypothèse du monde clos. C'est-à-dire que nous considérons que l'absence d'information, due north'est pas une data. Si vous demandez à une secrétaire de vous communiquer les coordonnées des clients qui sont domiciliés à Paris, elle vous donnera une liste où figurera autant de fois le nom « Paris » qu'il y a de clients dans la liste, et ceci paraît bien normal ! Sauf que, comme 50'aurait fait tout un chacun, votre secrétaire a fait fifty'hypothèse du monde clos sans le savoir en présumant que les clients pour lesquels fifty'adresse n'est pas renseignée ne sont pas domiciliés à PARIS !
C'est cela l'hypothèse du monde clos : considérer que fifty'absenteeism d'information doit être synonyme de critère de bigotry… La jointure externe permet de contrer fifty'hypothèse du monde clos en considérant qu'en cas d'absence de jointure entre une table et l'autre, on ne supprime pas pour autant l'information.

Three-D-2. Mécanisme en jeu▲

Lorsqu'une ligne d'une table figurant dans une jointure northward'a pas de correspondance dans les autres tables, le critère d'équijointure n'est pas satisfait et la ligne est rejetée. C'est la jointure interne. Au contraire, la jointure externe permet de faire reckoner dans le résultat les lignes satisfaisant la condition d'équijointure ainsi que celles n'ayant pas de correspondances.

Ainsi, si je veux contacter tous mes clients, quel que soit le manner de contact que je veux utiliser dans le core d'une campagne publicitaire, j'ai intérêt à obtenir une réponse contenant tous les clients, même ceux qui north'ont pas de téléphone, d'electronic mail ou d'adresse.

Exemple 17 :

                                                                  SELECT                                  CLI_ID, CLI_NOM, TYP_CODE                                  ||                                  ' : '                                  ||                                  TEL_NUMERO                                  AS                                  TEL_CONTACT, EML_ADRESSE, ADR_VILLE                                  FROM                                  T_CLIENT C                                  LEFT                                  OUTER                                  Bring together                                  T_TELEPHONE T                                  ON                                  C.CLI_ID                                  =                                  T.CLI_ID                                  LEFT                                  OUTER                                  JOIN                                  T_EMAIL Due east                                  ON                                  C.CLI_ID                                  =                                  East.CLI_ID                                  LEFT                                  OUTER                                  JOIN                                  T_ADRESSE A                                  ON                                  C.CLI_ID                                  =                                  A.CLI_ID                              
                                CLI_ID   CLI_NOM  TEL_CONTACT           EML_ADRESSE                        ADR_VILLE ------  --------  --------------        -----------------------            ---------      1  DUPONT    01-45-42-56-63        alain.dupont@wanadoo.fr            VERSAILLES      1  DUPONT    05-59-45-72-42        alain.dupont@wanadoo.fr            VERSAILLES      1  DUPONT    05-59-45-72-24        alain.dupont@wanadoo.fr            VERSAILLES      i  DUPONT    01-44-28-52-fifty        alain.dupont@wanadoo.fr            VERSAILLES      1  DUPONT    01-44-28-52-52        alain.dupont@wanadoo.fr            VERSAILLES      two  MARTIN    01-47-66-29-29        mmartin@transports_martin_fils.fr  VERGNOLLES CEDEX 452      ii  MARTIN    01-47-66-29-55        mmartin@transports_martin_fils.fr  VERGNOLLES CEDEX 452      2  MARTIN    01-47-66-29-29        plongeur@aol.com                   VERGNOLLES CEDEX 452      2  MARTIN    01-47-66-29-55        plongeur@aol.com                   VERGNOLLES CEDEX 452      3  BOUVIER   GSM : 06-11-86-78-89  NULL                               MONTMAIZIN ...                              


NOTA : Sur certains moteurs SQL la jointure bilatérale externe (Full OUTER Join) s'exprime :

                                              SELECT                        colonnes                        FROM                        TGauche                        Full                        JOIN                        TDroite                        ON                        condition                        de jointure                    


D'anciennes syntaxes permettent de faire des jointures externes unilatérales. Par exemple, il n'est pas rare de rencontrer les syntaxes suivantes :

                                              SELECT                        colonnes                        FROM                        table_1 t1, table_2 t2                        WHERE                        t1.id1                        *=                        t2.id2                    


ou encore :

                                              SELECT                        colonnes                        FROM                        table_1 t1, table_2 t2                        WHERE                        t1.id1                        (+)=                        t2.id2                    


Elles sont bien évidemment à proscrire si la syntaxe SQL 2 est disponible !

3-D-three. Discussion sur la jointure externe▲

La jointure externe est rarement bien comprise du premier insurrection. Si je vous suggest de lire cette discussion qui a eu lieu sur united nations forum Internet, c'est parce qu'elle permet de mieux la comprendre.

                                Objet : Optimisation Jointure Date :  26 Dec 2002 11:54:07 +0100 De :    Laurent Moreau  Bonjour a tous, Je me pose une petite question de syntaxe SQL sur les jointures. Et comme Frédéric va surement me répondre, je prends united nations exemple de son site. Y a-t-il une syntaxe meilleure que l'autre (si oui pourquoi) ?  SELECT CLI_NOM, TEL_NUMERO FROM   T_CLIENT C        INNER JOIN T_TELEPHONE T                 ON C.CLI_ID = T.CLI_ID WHERE  TYP_CODE = 'FAX'  SELECT CLI_NOM, TEL_NUMERO FROM   T_CLIENT C        INNER Join T_TELEPHONE T              ON (C.CLI_ID = T.CLI_ID  AND  T.TYP_CODE = 'FAX' )  Merci,                              
                                                                  Date : 26 Dec 2002 14:20:39 -0800    De :   Med Bouchenafa     Au niveau operation, il due north'y a aucune différence, 50'optimiseur de    SQL/Server construit le même plan    Par contre, au niveau conceptuel il est intéressant de différencier ce qui    appartient à la jointure et ce qui appartient à la limitation du jeu de    résultat.    Cela facilite la maintenance et la lecture du source    [...]                              
                                                                  Date : 26 Dec 2002 16:31:14 +0100       De :   Frédéric BROUARD         Pour comprende la différence entre le prédicat de la clause de jointure       et le prédicat du filtre WHERE, je vous suggest le petit test suivant :        -- test de jointure        CREATE TABLE TEST_JOIN1       (COL1 INT,        COL2 CHAR(ii))        CREATE TABLE TEST_JOIN2       (COL1 INT,        COL2 CHAR(2))        INSERT INTO TEST_JOIN1 VALUES (101, 'AA')       INSERT INTO TEST_JOIN1 VALUES (102, 'AA')       INSERT INTO TEST_JOIN1 VALUES (103, 'BB')        INSERT INTO TEST_JOIN2 VALUES (101, 'AA')       INSERT INTO TEST_JOIN2 VALUES (102, 'AA')       INSERT INTO TEST_JOIN2 VALUES (201, 'BB')        -- équivalente ??? En apparence seulement !        SELECT TJ1.COL1, TJ1.COL2       FROM   TEST_JOIN1 TJ1              JOIN TEST_JOIN2 TJ2                   ON TJ1.COL1 = TJ2.COL1       WHERE  TJ1.COL2 = 'AA'         SELECT TJ1.COL1, TJ1.COL2       FROM   TEST_JOIN1 TJ1              JOIN TEST_JOIN2 TJ2                   ON TJ1.COL1 = TJ2.COL1 AND TJ1.COL2 = 'AA'         -- d'accord... même résultat !!!        --changeons le lien interne en lien externe...        SELECT TJ1.COL1, TJ1.COL2       FROM   TEST_JOIN1 TJ1              LEFT OUTER Join TEST_JOIN2 TJ2             ON TJ1.COL1 = TJ2.COL1       WHERE  TJ1.COL2 = 'AA'         SELECT TJ1.COL1, TJ1.COL2       FROM   TEST_JOIN1 TJ1              LEFT OUTER Join TEST_JOIN2 TJ2                   ON TJ1.COL1 = TJ2.COL1 AND TJ1.COL2 = 'AA'         -- pas d'accord !!! résultat différent...        -- POURQUOI ???       -- je vous laisse méditer la chose !                              
                                                                  Date : 26 December 2002 xvi:55:13 +0100          De :   Lionel Pénuchot           Salut Fred,           Tu peux alors m'expliquer pourquoi cette requête renvoie le même résultat que ta dernière ?           SELECT TJ1.COL1, TJ1.COL2          FROM   TEST_JOIN1 TJ1                 LEFT OUTER JOIN TEST_JOIN2 TJ2                      ON TJ1.COL1 = TJ2.COL1 AND TJ1.COL2 = 'taratata' and ane=0           Cordialement                              
                                                                  Appointment : 27 Dec 2002 10:17:10 +0100             De :   Frédéric BROUARD              La clause WHERE est united nations filtre d'élimination. Il suppose que les              différentes tables sont déjà jointes en une seule et parcourt              l'ensemble des résultats à la recherche des lignes correspondant aux             critères donnés.              En revanche la clause Bring together se comporte différemment. Elle agit AVANT             que la jointure soit constructive.              Dans le cadre d'une jointure INTERNE le comportement est similaire à             celui d'une clause WHERE.              Dans le cas d'une jointure externe, il faut décomposer la logique en              plusieurs étapes :             1) évaluation des éléments du prédicat             2) awarding de la jointure : lignes jointes en 1)                                             + lignes sans correspondance              Reprenons la clause de jointure de la requête donnée par Laurent :                TJ1.COL1 = TJ2.COL1 AND TJ1.COL2 = 'taratata' and 1=0                ^^^^^^^^^^^^^^^^^^^     ^^^^^^^^^^^^^^^^^^^^^     ^^^               prédicat de jointure         prédicats hors jointure              1° 1=0 =&gt; aucune ligne n'est récupérée                donc aucune ligne de TEST_JOIN1 due north'a de correspondance avec TEST_JOIN2                conclusion : toutes les lignes de TEST_JOIN1 seront reprises du fait                de la jointure externe               ii° TJ1.COL2 = 'taratata' =&gt; encore une fois aucune ligne due north'est récupérée                donc aucune ligne de TEST_JOIN1 n'a de correspondance avec TEST_JOIN2                determination : toutes les lignes de TEST_JOIN1 seront reprises du fait                de la jointure externe               3° TJ1.COL1 = TJ2.COL1 =&gt; 2 lignes sont en correspondance avec TEST_JOIN2                 conclusion, ces deux lignes seront récupérées du fait de la jointure                externe et la 3e ligne aussi puisqu'elle due north'a aucune correspondance               ???              Pour comprendre la différence, il suffit de demander à voir les colonnes             de la table TEST_JOIN2 dans les différents cas :                SELECT TJ1.COL1, TJ1.COL2, TJ2.COL1, TJ2.COL2             FROM   TEST_JOIN1 TJ1                    LEFT OUTER JOIN TEST_JOIN2 TJ2                         ON TJ1.COL1 = TJ2.COL1 AND TJ1.COL2 = 'AA'              COL1        COL2 COL1        COL2              ----------- ---- ----------- ----              101         AA   101         AA             102         AA   102         AA             103         BB   NULL        NULL                SELECT TJ1.COL1, TJ1.COL2, TJ2.COL1, TJ2.COL2             FROM   TEST_JOIN1 TJ1                    LEFT OUTER JOIN TEST_JOIN2 TJ2                         ON TJ1.COL1 = TJ2.COL1              WHERE TJ1.COL2 = 'AA'              COL1        COL2 COL1        COL2              ----------- ---- ----------- ----              101         AA   101         AA             102         AA   102         AA                SELECT TJ1.COL1, TJ1.COL2, TJ2.COL1, TJ2.COL2             FROM   TEST_JOIN1 TJ1                    LEFT OUTER JOIN TEST_JOIN2 TJ2                         ON TJ1.COL1 = TJ2.COL1 AND TJ1.COL2 = 'taratata' and 1=0              COL1        COL2 COL1        COL2              ----------- ---- ----------- ----              101         AA   Aught        Aught             102         AA   NULL        Zippo             103         BB   Naught        NULL              et voilà !                              

III-Due east. La jointure croisée▲

Image non disponible

La jointure croisée north'est autre que le produit cartésien de deux tables. Rappelons que le produit cartésien de deux ensembles n'est autre que la multiplication généralisée. Dans le cas des tables, c'est le fait d'associer à chacune des lignes de la première tabular array, toutes les lignes de la seconde. Ainsi, si la première table compte 267 lignes et la seconde 1214, on se retrouve avec un résultat contenant 324 138 lignes. Bien entendu, due south'il n'y a aucun doublon dans les tables, toutes les lignes du résultat seront aussi uniques.

La jointure croisée peut s'écrire de deux manières différentes :

  • à fifty'adjutant de l'opérateur normalisé :

                                                      SELECT                          colonnes                          FROM                          table_1                          Cantankerous                          JOIN                          table_2                      
  • ou à l'adjutant d'une clause FROM simplifiée :
                                          SELECT                      colonnes                      FROM                      table_1, table_2                  

Ce qui est certainement l'expression la plus minimaliste de tous les ordres SELECT du SQL !

Nous voudrions savoir si notre table des tarifs de chambre (TJ_TRF_CHB) est complète, c'est-à-dire si l'on a bien toutes les chambres (T_CHAMBRE) pour toutes les dates de début de tarif (T_TARIF) :

Exemple 18 :

                                                              SELECT                                CHB_ID, TRF_DATE_DEBUT,                                0                                Equally                                TRF_CHB_PRIX                                FROM                                T_TARIF, T_CHAMBRE                                Society                                BY                                CHB_ID, TRF_DATE_DEBUT                            
                              CHB_ID  TRF_DATE_DEBUT  TRF_CHB_PRIX ------  --------------  ------------      one  1999-01-01              0,00       ane  1999-09-01              0,00      1  2000-01-01              0,00      1  2000-09-01              0,00      1  2001-01-01              0,00      2  1999-01-01              0,00      2  1999-09-01              0,00      2  2000-01-01              0,00      2  2000-09-01              0,00      2  2001-01-01              0,00 ...                            


En comparant rapidement le contenu des deux tables, on peut south'assurer que tous les tarifs ont bien été renseignés.

Avec la syntaxe normalisée, cette requête southward'écrit :

Exemple 19 :

                                                              SELECT                                CHB_ID, TRF_DATE_DEBUT,                                0                                Equally                                TRF_CHB_PRIX                                FROM                                T_TARIF                                Cross                                Join                                T_CHAMBRE                                Club                                By                                CHB_ID, TRF_DATE_DEBUT                            
                              CHB_ID  TRF_DATE_DEBUT  TRF_CHB_PRIX ------  --------------  ------------      ane  1999-01-01              0,00       1  1999-09-01              0,00      i  2000-01-01              0,00      i  2000-09-01              0,00      one  2001-01-01              0,00      2  1999-01-01              0,00      two  1999-09-01              0,00      two  2000-01-01              0,00      2  2000-09-01              0,00      ii  2001-01-01              0,00 ...                            


et donne le même résultat !

III-F. La jointure d'matrimony▲

Image non disponible

La jointure d'union permet de faire l'union de deux tables de structures quelconque. Elle southward'exprime qu'à fifty'aide de fifty'opérateur normalisé SQL2 suivant :

                                          SELECT                      colonnes                      FROM                      table_1                      Union                      Join                      table_2                  

Effectuons par exemple la jointure d'matrimony entre les tables T_TITRE (« M. », « Mme. », « Melle. » ) et T_TYPE (« FAX », « GSM », « TEL ») :

Exemple 20 :

                                                              SELECT                                *                                FROM                                T_TITRE                                Wedlock                                Bring together                                T_TYPE                            
                              TIT_CODE TIT_LIBELLE                      TYP_CODE TYP_LIBELLE  -------- -------------------------------- -------- ------------------- One thousand.       Monsieur                         NULL     NULL Melle.   Mademoiselle                     Null     Null Mme.     Madame                           NULL     NULL NULL     NULL                             FAX      Télécopie Zero     Cipher                             GSM      Téléphone portable NULL     Zero                             TEL      Téléphone fixe                            


En fait c'est comme si l'on avait listé la première table, puis la seconde en évitant toute colonne commune et compléter les espaces vides des valeurs Goose egg.

Mais cette jointure est très rarement implantée dans les moteurs SQL.

NOTA : si l'opérateur UNION JOIN n'est pas présent dans votre moteur, vous pouvez le fabriquer comme suit, car elle (fifty'union) peut être facilement remplacée par united nations autre ordre SQL presque aussi elementary :

                                          SELECT                      *                      FROM                      <                      table                      gauche>                      Total                      OUTER                      JOIN                      <                      table                      droite>                      ON                      <critère>                                      


Où la condition <critère> est n'importe quel prédicat valant toujours faux comme « 1=2 ».

En dehors du fait de linéariser des tables hétérogènes, il est franchement permis de douter de 50'utilité d'united nations tel opérateur.

IV. Nature des weather de jointures▲

Nous allons maintenant analyser les différentes jointures basées sur la nature des conditions pour bien les distinguer.

IV-A. Équijointure▲

L'équijointure consiste à opérer une jointure avec une condition d'égalité. Cette condition d'égalité dans la jointure peut ne pas porter nécessairement sur les clefs (primaires et étrangères).
Recherchons par exemple les clients dont le nom est celui d'une ville contenu dans la tabular array des adresses :

Exemple 21 :

                                                              SELECT                                DISTINCT                                C.CLI_ID, C.CLI_NOM, A.ADR_VILLE                                FROM                                T_CLIENT C                                Bring together                                T_ADRESSE A                                ON                                C.CLI_NOM                                =                                A.ADR_VILLE                            
                              CLI_ID  CLI_NOM  ADR_VILLE ------  -------  ---------     92  PARIS    PARIS ...                            


Nous avons donc bien réalisé une équijointure, mais elle n'est pas naturelle parce qu'elle ne repose pas sur les clefs des tables.

Bien entendu, il existe united nations opérateur normalisé SQL two permettant de traiter le cas de l'équijointure :

                                          SELECT                      [DISTINCT ou ALL]                      *                      ou liste de colonnes                      FROM                      table1                      [INNER]                      Bring together                      table2                      ON                      condition                      de jointure                  

Le mot clef INNER due north'étant pas obligatoire, mais voulant south'opposer aux mots clefs OUTER, UNION et CROSS.
Ainsi, la requête précédente southward'écrit à 50'aide de cette syntaxe :

Exemple 22 :

                                                              SELECT                                Singled-out                                C.CLI_ID, C.CLI_NOM, A.ADR_VILLE                                FROM                                T_CLIENT C                                INNER                                Bring together                                T_ADRESSE A                                ON                                C.CLI_NOM                                =                                A.ADR_VILLE                            
                              CLI_ID  CLI_NOM  ADR_VILLE ------  -------  ---------     92  PARIS    PARIS ...                            

IV-B. Non-équijointure▲

Il s'agit là d'utiliser due north'importe quelle condition de jointure entre deux tables, excepté la stricte égalité. Ce peuvent être les conditions suivantes :

>

supérieur

>=

supérieur ou égal

<

inférieur

<=

inférieur ou égal

<>

différent de

IN

dans un ensemble

Similar

correspondance partielle

BETWEEN … AND …

entre deux valeurs

EXISTS

dans une table


En règle générale on trouve des non-équijointures dans le cadre de comparaisons temporelles ou de mesures physiques. Par exemple on pourrait rechercher une pièce mécanique dans united nations stock qui soit de même nature ou de même fonction qu'une pièce donnée, mais plus légère.

Nous voulons obtenir les factures qui ont été émises avant que le prix des petits déjeuners n'atteigne 6 €.

Exemple 23 :

                                                              SELECT                                F.*                                FROM                                T_FACTURE F                                INNER                                Bring together                                T_TARIF T                                ON                                F.FAC_DATE                                <                                T.TRF_DATE_DEBUT                                WHERE                                TRF_PETIT_DEJEUNE                                >=                                half-dozen                                                          
                              FAC_ID  CLI_ID  PMT_CODE  FAC_DATE    FAC_PMT_DATE ------  ------  --------  ----------  ------------      1       1  CB        1999-01-31  1999-02-fourteen      iii       1  CHQ       1999-02-28  1999-03-12      5       1  CB        1999-03-31  1999-04-23      vii       1  CHQ       1999-04-30  1999-05-14      ix       1  CHQ       1999-05-31  1999-06-fourteen     11       one  CB        1999-06-30  1999-07-14     xiii       1  CHQ       1999-07-31  1999-08-12     15       1  CB        1999-08-31  1999-09-23     25       2  CB        1999-01-31  1999-02-23     27       ii  CHQ       1999-02-28  1999-03-eighteen ...                            


NOTA : pour récupérer toutes les colonnes d'une tabular array, on peut utiliser 50'opérateur * suffixé par le nom de table, comme nous l'avons fait ici pour la tabular array des factures.

Si notre table des tarifs avait été organisée par tranches, comme ceci :

                    TRF_DATE_DEBUT  TRF_DATE_FIN  TRF_TAUX_TAXES  TRF_PETIT_DEJEUNE --------------  ------------  --------------  ----------------- 1999-01-01      1999-08-31             18,sixty             6,00 E 1999-09-01      1999-12-31             20,60             7,00 E  2000-01-01      2000-08-31             20,60             8,00 Eastward 2000-09-01      2000-12-31             20,60             9,00 Due east 2001-01-01      2001-12-31             20,60            10,00 E                  


alors,récupérer le tarif des chambres cascade chacune des dates du planning devient united nations exercice très unproblematic :

Exemple 24 :

                                                              SELECT                                CPC.CHB_ID, CPC.PLN_JOUR, TC.TRF_CHB_PRIX                                FROM                                TJ_CHB_PLN_CLI CPC                                INNER                                Join                                T_TARIF T                                ON                                CPC.PLN_JOUR                                BETWEEN                                T.TRF_DATE_DEBUT                                AND                                T.TRF_DATE_FIN                                INNER                                Join                                TJ_TRF_CHB TC                                ON                                T.TRF_DATE_DEBUT                                =                                TC.TRF_DATE_DEBUT                                AND                                CPC.CHB_ID                                =                                TC.CHB_ID                            
                              CHB_ID  PLN_JOUR    TRF_CHB_PRIX ------  ----------  ------------      1  2000-01-22       xl,00 East      1  2000-01-28       40,00 E      one  1999-01-02       33,50 E      1  1999-01-03       33,fifty E      one  1999-01-05       33,fifty E      1  1999-01-07       33,50 Eastward      i  1999-01-09       33,fifty E      1  1999-01-x       33,50 E      1  1999-01-11       33,50 Due east      1  1999-01-13       33,50 E ...                            


Nous avons donc à nouveau un exemple remarquable de non-equijointure.

Constatons que la colonne TRF_DATE_FIN de cette nouvelle version de la table des tarifs implique une redondance de 50'data. En effet, cette date de fin est déductible de la date de début de la ligne qui contient la date immédiatement postérieure avec united nations jour de moins. De plus le problème induit par cette organisation des données fait qu'il faut obligatoirement définir une date de fin des tarifs, même dans le futur, sinon certaines tarifications ne pourront être établies par cette requête.

Il ne s'agit donc pas d'une modélisation correcte !

IV-C. Autojointure▲

Le problème consiste à joindre une tabular array à elle-même. Il est assez fréquent que 50'on ait besoin de telles autojointures, car elles permettent notamment de modéliser des structures de données complexes comme des arbres. Voici quelques exemples de relation nécessitant une autojointure de tables :

  • dans une table des employées, connaître le supérieur hiérarchique de tout employé ;
  • dans une table de classification savoir quels sont les composants nécessaires à la réalisation d'un module, ou les modules nécessaires à la réalisation d'un appareil ;
  • dans une table de personnes, retrouver l'autre moitié d'un couple marié.

La représentation d'une telle jointure dans le modèle de données, se fait par le rajout d'une colonne contenant une pseudo clef étrangère basée sur la clef de la tabular array.
Dans ce cas, une syntaxe possible pour l'autojointure est la suvante :

                                          SELECT                      [Singled-out ou ALL]                      *                      ou liste de colonnes                      FROM                      laTable t1                      INNER                      Join                      laTable t2                      ON                      t1.laClef                      =                      t2.laPseudoClefEtrangère                  

C'est l'exemple typique ou l'utilisation de surnoms pour les tables est obligatoire, sinon il y a risque de confusion cascade le moteur SQL.

Pour donner un exemple concret à nos propos, nous allons modéliser le fait qu'une chambre puisse communiquer avec une autre (par une porte). Dès lors, le challenge est de trouver quelles sont les chambres qui communiquent entre elles par exemple pour réaliser une sorte de suite. Cascade ce faire, nous allons ajouter à notre table des chambres une colonne de clef étrangère basée sur la clef de la tabular array.
Dans ce cas, cette colonne doit obligatoirement accepter des valeurs nulles !

Voici fifty'ordre SQL pour rajouter la colonne CHB_COMMUNIQUE dans la table T_CHAMBRE :

                                          ALTER                      Tabular array                      T_CHAMBRE                      ADD                      CHB_COMMUNIQUE                      INTEGER                                      

Alimentons là de quelques valeurs exemples en considérant que la 7 communique avec la 9 et la 12 avec la 14 :

                                          UPDATE                      T_CHAMBRE                      SET                      CHB_COMMUNIQUE                      =                      9                      WHERE                      CHB_ID                      =                      7                      UPDATE                      T_CHAMBRE                      SET                      CHB_COMMUNIQUE                      =                      7                      WHERE                      CHB_ID                      =                      9                      UPDATE                      T_CHAMBRE                      Prepare                      CHB_COMMUNIQUE                      =                      12                      WHERE                      CHB_ID                      =                      xiv                      UPDATE                      T_CHAMBRE                      Prepare                      CHB_COMMUNIQUE                      =                      fourteen                      WHERE                      CHB_ID                      =                      12                                      

Pour formuler la recherche de chambres communicantes, il suffit de faire la requête suivante :

Exemple 25 :

                                                              SELECT                                Distinct                                c1.CHB_ID, c1.CHB_COMMUNIQUE                                FROM                                T_CHAMBRE c1                                INNER                                Join                                T_CHAMBRE c2                                ON                                c1.CHB_ID                                =                                c2.CHB_COMMUNIQUE                            
                              CHB_ID  CHB_COMMUNIQUE  ------  --------------       7               9      9               seven      12              14     14              12                            


où la tabular array T_CHAMBRE figure deux fois par 50'entremise de deux surnommages différents c1 et c2.

Notons que cette présentation northward'est pas pratique, auto elle dédouble le nombre de couples de chambres communicantes, donnant l'impression qu'il y a 4 couples de chambres communicantes ce qui est imitation. Il manque juste un petit quelque chose pour que cette requête soit parfaite. Il suffit en effet de ne retenir les solutions que pour des identifiants inférieurs ou supérieurs d'une tabular array par rapport à fifty'autre :

Exemple 26 :

                                                              SELECT                                Singled-out                                c1.CHB_ID, c1.CHB_COMMUNIQUE                                FROM                                T_CHAMBRE c1                                INNER                                JOIN                                T_CHAMBRE c2                                ON                                c1.CHB_ID                                =                                c2.CHB_COMMUNIQUE                                AND                                c1.CHB_ID                                <=                                c2.CHB_ID                            
                              CHB_ID  CHB_COMMUNIQUE  ------  --------------       vii               9     12              14                            

IV-D. La jointure hétérogène▲

Une jointure hétérogène consiste à joindre dans une même requête, des tables provenant de bases de données différentes, voire de serveurs de données différents. Un tel type de jointure due north'est possible que :

  • entre deux bases d'united nations même serveur : que si le SQL du serveur le permet (par exemple deux bases Oracle, deux bases SQL Server, etc.) ;
  • entre deux bases de deux serveurs différents : qu'en passant par united nations outil tiers de type « middleware » (par exemple une entre une base Oracle et une base Sybase). L'united nations des rares middleware à faire ce type de jointure est le BDE (Borland Database Engine) d'Inprise.

Dans ces deux cas, il faut bien vérifier la compatibilité des types, car il se peut que des résultats surprenants apparaissent notamment dans le traitement des nombres réels.

Pour effectuer de telles requêtes, la syntaxe est toujours spécifique au moteur employé. Avec le BDE le niveau de SQL est SQL 2, cantonné aux fonctions de base.

Voici un exemple de requête entre deux serveurs à l'aide du BDE :

Exemple 27 :

                                                              SELECT                                C.CLI_NOM, T.TEL_NUMERO                                FROM                                ":ORACLE_CLIBD:T_CLIENT"                                C,                                ":SYBASE_TELBD:T_TELEPHONE"                                T                                WHERE                                C.CLI_ID                                =                                T.CLI_ID                            
                              NOM_CLI  TEL_NUMERO -------  -------------- DUPONT   01-45-42-56-63 DUPONT   01-44-28-52-52 DUPONT   01-44-28-52-50 BOUVIER  06-11-86-78-89 DUBOIS   02-41-58-89-52 DREYFUS  01-51-58-52-50 DUHAMEL  01-54-11-43-21 BOYER    06-55-41-42-95 MARTIN   01-48-98-92-21 MARTIN   01-44-22-56-21 ...                            


En fait le BDE se sert d'alias permettant de définir à la fois le serveur et la base concerné. Ici les alias sont : « :ORACLE_CLIBD: » et « :SYBASE_TELBD: ».

V. Récapitulatif des jointures normalisées▲

V-A. Terminologie et syntaxe des jointures▲

Image non disponible
  • Jointure naturelle : la jointure south'effectue sur les colonnes communes, c'est-à-dire celles de même nom et type :
                                          SELECT                      colonnes                      FROM                      table1                      NATURAL                      JOIN                      table2                      [USING col1, col2 ... ]                      [WHERE prédicat]                      ...                  

Le mot clef USING permet de restreindre les colonnes communes à prendre en considération.

  • Jointure interne : la jointure south'effectue entre les tables sur les colonnes précisées dans la condition de jointure :
                                          SELECT                      colonnes                      FROM                      table1 t1                      [INNER ]                      JOIN                      table2 t2                      ON                      status                      [WHERE prédicat]                      ...                  
  • Jointure externe : la jointure permet de récupérer les lignes des tables correspondant au critère de jointure, mais aussi celles pour lesquelles il n'existe pas de correspondance.
                                          SELECT                      colonnes                      FROM                      table1 t1                      [RIGHT OUTER | LEFT OUTER | Total OUTER ]                      JOIN                      table2 t2                      ON                      status                      [WHERE prédicat]                      ...                  
  • Right OUTER : la table à droite de l'expression clef « RIGHT OUTER » renvoie des lignes sans correspondance avec la table à gauche.
  • LEFT OUTER : la tabular array à gauche de l'expression clef « LEFT OUTER » renvoie des lignes sans correspondance avec la table à droite.
  • FULL OUTER : les deux tables renvoient des lignes sans correspondance entre elles.
  • Jointure croisée : la jointure effectue le produit cartésien (la « multiplication ») des deux tables.
    Il due north'y a pas de condition.
                                          SELECT                      colonnes                      FROM                      table1 t1                      Cantankerous                      JOIN                      table2 t2                      [WHERE prédicat]                      ...                  
  • Jointure d'marriage : la jointure concatène les tables sans aucune correspondance de colonnes.
                                          SELECT                      colonnes                      FROM                      table1                      Marriage                      JOIN                      table2                  

Il n'y a pas de critère de jointure.

Si votre SGBDR n'implémente pas la jointure externe droite, inverser 50'ordre des tables et faire une jointure externe gauche lorsque cela est possible.

V-B. Arbre de jointure▲

La jointure de multiples tables peut se représenter sous la forme d'un arbre. Cet arbre possède donc une racine, c'est la table principale, celle d'où l'on veut que l'information parte. Elle possède aussi des feuilles, c'est-à-dire des tables d'entités. Les tables situées entre la racine et les feuilles sont souvent des tables de jointure, possédant en principe deux clefs étrangères. Dans le principe toute table de jointure devrait être un nœud de l'arbre.
La représentation arborescente d'une jointure est un excellent moyen pour visualiser si la clause de jointure de votre requête est à priori correcte. En effet, une référence circulaire dans la clause de jointure ne peut pas être représentée sous la forme d'un arbre et il y a fort à parier que la requête soit incorrecte.

Voici par exemple la requête qui « met à plat », la base hotel :

                                          SELECT                      *                      FROM                      T_CLIENT CLI                      -- le customer (racine de fifty'arbre)                      Join                      T_ADRESSE ADR                      -- adresse, tabular array d'entité (feuille de l'arbre)                      ON                      CLI.CLI_ID                      =                      ADR.CLI_ID                      JOIN                      T_TITRE TIT                      -- titre, tabular array d'entité (feuille de fifty'arbre)                      ON                      CLI.TIT_CODE                      =                      TIT.TIT_CODE                      Join                      T_EMAIL EML                      -- mail, table d'entité (feuille de l'arbre)                      ON                      CLI.CLI_ID                      =                      EML.CLI_ID                      Join                      T_TELEPHONE TEL                      -- téléphone, table d'entité retainer de jointure (noeud dans l'arbre)                      ON                      CLI.CLI_ID                      =                      TEL.CLI_ID                      Bring together                      T_TYPE TYP                      -- type de téléphone, table d'entité  (feuille de l'arbre)                      ON                      TEL.TYP_CODE                      =                      TYP.TYP_CODE                      JOIN                      TJ_CHB_PLN_CLI CPC                      -- table de jointure (noeud dans l'arbre)                                            ON                      CLI.CLI_ID                      =                      CPC.CLI_ID                      JOIN                      T_PLANNING PLN                      -- appointment du planning, table d'entité (feuille de 50'arbre)                      ON                      CPC.PLN_JOUR                      =                      PLN.PLN_JOUR                      JOIN                      T_CHAMBRE CHB                      -- chambre, tabular array d'entité servant de jointure (noeud dans l'arbre)                      ON                      CPC.CHB_ID                      =                      CHB.CHB_ID                      Join                      TJ_TRF_CHB TC                      -- table de jointure (noeud dans l'arbre)                      ON                      CHB.CHB_ID                      =                      TC.CHB_ID                      Join                      T_TARIF TRF                      -- tarif, table d'entité (feuille de l'arbre)                      ON                      TC.TRF_DATE_DEBUT                      =                      TRF.TRF_DATE_DEBUT                      Bring together                      T_FACTURE FAC                      -- facture, table d'entité servant de jointure                      ON                      CLI.CLI_ID                      =                      FAC.CLI_ID                      Join                      T_LIGNE_FACTURE LIF                      -- ligne de facture, table d'entité (feuille de l'arbre)                      ON                      FAC.FAC_ID                      =                      LIF.FAC_ID                      Bring together                      T_MODE_PAIEMENT PMT                      -- mode de paiement, table d'entité (feuille de l'arbre)                                            ON                      FAC.PMT_CODE                      =                      PMT.PMT_CODE                  

Correctement indenté on distingue déjà la structure arborescente. On peut la mettre en évidence en supprimant tout ce qui due north'est pas un nom de table :

                                          T_CLIENT CLI                      -- le client (racine de 50'arbre)                      T_ADRESSE ADR                      -- adresse, tabular array d'entité (feuille de l'arbre)                      T_TITRE TIT                      -- titre, table d'entité (feuille de l'arbre)                      T_EMAIL EML                      -- mail, table d'entité (feuille de l'arbre)                      T_TELEPHONE TEL                      -- téléphone, table d'entité retainer de jointure (noeud dans 50'arbre)                      T_TYPE TYP                      -- type de téléphone, table d'entité  (feuille de l'arbre)                      TJ_CHB_PLN_CLI CPC                      -- table de jointure (noeud dans 50'arbre)                                            T_PLANNING PLN                      -- date du planning, tabular array d'entité (feuille de l'arbre)                      T_CHAMBRE CHB                      -- chambre, table d'entité retainer de jointure (noeud dans l'arbre)                      TJ_TRF_CHB TC                      -- table de jointure (noeud dans l'arbre)                      T_TARIF TRF                      -- tarif, tabular array d'entité (feuille de l'arbre)                      T_FACTURE FAC                      -- facture, table d'entité servant de jointure                      T_LIGNE_FACTURE LIF                      -- ligne de facture, tabular array d'entité (feuille de fifty'arbre)                      T_MODE_PAIEMENT PMT                      -- mode de paiement, table d'entité (feuille de fifty'arbre)                                      

Par cette indentation, il est facile de repérer les jointures entre les tables.

VI. Annotation importante▲

Les jointures ne sont pas la seule manière de mettre en relation différentes tables au sein d'une même requête SQL. On peut aussi joindre plusieurs tables à 50'aide des sous-requêtes ainsi qu'à l'aide des opérateurs ensemblistes que nous allons voir aux deux prochains chapitres.

VII. Résumé▲

Voici les différences entre les moteurs des bases de données :

Paradox

Access

PostGreSQL

Sybase

SQL Server 7

Oracle viii

DB2 (400)

INNER JOIN

Oui

Oui

Oui

Oui

Oui

Non (ane)

Oui

OUTER JOIN

LEFT, RIGHT, Full

LEFT, Correct

LEFT, Right, Full

LEFT, RIGHT, FULL

LEFT, RIGHT, FULL

Not (1)

LEFT (4)

Marriage Join

Non (2)

Not

Non

Non (2)

Not (2)

Non (1) (2)

Non

CROSS JOIN

Non (3)

Non (3)

Oui

Not (three)

Oui

Non (ane) (3)

Oui

(ane) Oracle ne connaît toujours pas le JOIN (ça fait quand même plus de dix ans de retard pour cet éditeur pionnier qui semble south'endormir sur ses lauriers). Il faut donc utiliser une syntaxe propriétaire. Exception : la version 9 supporte enfin les jointures normalisées.

(2) Possible avec un Total OUTER Join

(3) Possible avec l'ancienne syntaxe sans précision de critère WHERE

(4) de plus IBM DB2 (400) dispose d'united nations très intéressant « exception join » équivalent à :

                                      SELECT                    *                    FROM                    tablegauche                    LEFT                    OUTER                    Bring together                    tabledroite                    ON                    références de jointure                    WHERE                    tabledroite.clef                    IS                    Cypher                                  

Données concernant DB2 400 aimablement communiquées par kerigan.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : ViadeoTwitterFacebookShare on Google+ 

Copyright © 2004 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site ni de fifty'ensemble de son contenu : textes, documents, images, etc. sans fifty'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Source: https://sqlpro.developpez.com/cours/sqlaz/jointures/

Posted by: grimmtordely.blogspot.com

0 Response to "Mot Qui Commence Par Un Z"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel