JOINTURES
1.Données de plusieurs tables
Ce chapitre-ci a le but de présenter l’obtention des données de plusieurs tables.
Après l’avoir étudié, vous devriez savoir :
Parfois, on doit utiliser des données qui se trouvent dans plusieurs tables. L ;exemple ci-dessous fait l’affichage des données stockées dans deux tables différentes.
Le numéro d’identification de l’employée (“empno”) se trouve dans la table EMP.
Le numéro d’identification (“deptno”) du departement se trouve dans la table EMP et aussi dans la table DEPT.
Pour obtenir le rapport souhaité, on doit lier les tables DEPT et EMP et ensuite retrouver des données des deux tables.
La jointure est basée sur l’opération de produit cartésien à laquelle on associe une condition. Quand cette condition de jointure est invalide ou elle manque, le résultat obtenu sera exactement le produit cartésien des deux tables, où il y aura toutes les combinaisons possibles de lignes. Un produit cartésien génère un grand nombre de lignes et le résultat ainsi obtenu est rarement utile. C’est pour ça qu’on devrait inclure une condition valide de jointure dans la clause WHERE, exceptant le cas (improbable) quand on souhaite vraiment obtenir un produit cartésien.
Note: Le produit cartésien peut être utilisé pour tester le comportement de la base de données quand on travaille avec une grande quantité de données à processer.
Conclusions:
Il y a un produit cartésien qui apparaît dans les cas suivants
On omet la condition de jointure
La condition de jointure est invalide
Toutes les lignes de la première table sont réunies avec les lignes de la deuxième.
Pour éviter le produit cartésien veuillez introduire toujours une condition de jointure dans la clause WHERE.
La base de données génère un produit cartésien quand la condition de jointure est omise. L’exemple suivant affiche le nom de l’employée (de la table EMP) et le nom du département dans lequel il travaille (de la table DEPT):
Mais parce que la clause WHERE est absente, toutes les 14 lignes de la table EMP vont être combinées avec toutes les quatre lignes de la table DEPT. Le résultat aura, donc, 56 lignes (probablement inutilisables).
1.3. Types de jointures
La base de données Oracle 9i offre une syntaxe de jointure compatible avec le standard SQL de 1999. Avant la version, la syntaxe ORACLE était différente de celle spécifiée dans le standard ANSI. La modification de la syntaxe n’apporte aucune modification à la performance du moteur de la base de données ORACLE. Il s’agit seulement d’une modification nécessaire pour assurer la compatibilité avec le standard. La table ci-dessous présente en tandem entre l’approche de la jointure du standard SQL et celle propriétaire de la base de données ORACLE:
Join proprietar ORACLE (pana la versiunea 8i) |
Join compatibil SQL 1999 |
Equijointure |
Jointure croisée |
Non-equijointure |
Jointure naturale |
Jointure externe |
Clause USING |
Jointure reflexive |
Jointure externe complète ou TWO-SIDED |
Conditions arbitraires pour Jointure externe |
1.4. Définition de la jointure
Quand on a besoin de données de plusieurs tables de la base de données simultanément, on utilise une condition de jointure. Les tables peuvent être utilisées pour une jointure, en se basant sur les valeurs communes existantes dans des colonnes correspondantes. Il est facile à deviner que d’habitude on utilise les colonnes contenant des clés primaires ou des clés externes.
2. Jointures dans la syntaxe des versions précédentes à Oracle 8i
Pour afficher des données de plusieurs tables on ajoute tout simplement une condition de jointure dans la clause WHERE.
Par exemple, pour retrouver les employées et les départements où ils travaillent, ci-inclus l’adresse des départements, on va faire une jointure entre les tables EMP et DEPT, ayant comme condition de jointure l’égalité nu numéro de département de la table EMP avec celui de la table DEPT:
Quand on formule une requête SELECT où on fait la jointure de quelques tables, on précède le nom de la colonne par le nom de la table pour accroître la lisibilité et pour augmenter la vitesse d’accès aux données.
Si un nom de colonne apparaît dans plusieurs tables, c ;est obligatoire de prefixer les colonnes avec les noms des tables.
Pour faire une jointure entre tables il faut avoir au moins n-1 conditions. Cette observation pourrait ne pas s’appliquer quand la table a une clé concatenée. Dans ce cas-là, pour l’identification unique d’une ligne, il faut avoir plusieurs colonnes.
Pour trouver le nom du département où travaille un employée on doit comparer les valeurs des colonnes deptno appartenant aux tables EMP et DEPT. Les valeurs correspondantes doivent se trouver dans une relation d’égalité. On a donc affaire avec une equijointure. D’habitude, ce type de jointure est liée aux clés primaires et externes des tables.
Note: A la place de l’equijointure on utilise aussi les noms jointure simple ou bien jointure interne.
2.1.1. Comment utiliser l’equijointure pour extraire les enregistrements?
On part de l’exemple suivant :
La clause SELECT spécifie les noms des colonnes qui doivent être extraites de la base de données:
2.1.2. Conditions supplémentaires de recherche
La clause WHERE peut être utilisée aussi pour filtrer les lignes considérées avant l’opération effective de jointure. Par exemple, pour afficher le nom et le prénom de l’employée KING il faut ajouter une condition.
Parfois on pourrait vouloir exécuter une jointure sur plusieurs tables. Par exemple, pour extraire le nom, le département et le degré de salarisation de chaque employée, on doit utiliser simultanément les tables EMP, DEPT et SALGRADE:
2.1.3. Eliminer les ambiguïtés sur le nom des colonnes
Parfois il y a des confusions qui apparaissent, confusions générées par le fait que deux ou plusieurs colonnes peuvent avoir le même nom. Le moteur de données ne peut pas supposer de quelle colonne s’agit-il. Pour ça, les noms de colonnes doivent être préfixés par le nom de la table qui la contient. La syntaxe est se ressemble avec l’adressage directe des éléments d’une structure dans le langage C:
Nume_tabela.nume_coloana
Au cas où le nom de la colonne est unique dans les tables qui font l’objet de la jointure, il n’est plus obligatoire de les préfixer. Cependant tout ça, en préfixant les colonnes on améliore les performances d’utilisation parce que cette opération augmente la vitesse avec laquelle est identifiée la colonne recherchée par le moteur de la base de données.
En préfixant, on améliore beaucoup la lisibilité de la requête pour un utilisateur humain.
Les règles d’élimination des ambiguïtés s’appliquent aussi pour les autres clauses d’une requête SQL.
2.1.4. Nom alternatif de tabela (alias)
Préfixer les colonnes avec le nom de la table peut être une opération prenant beaucouip de temps, surtout si les noms des tables sont longues. Pour éviter ce problème peuvent être introduits des noms alternatifs (alias) pour chaque table. L’utilisation de noms alternatifs peut aider aussi à réduire le code SQL.Pour associer un alias à un nom de table, celui-ci doit être rajouté après le nom original de la clause FROM. Ces deux noms doivent être séparées que par des espaces. Jamais in ne doit pas avoir entre le nom de la table et l’alias le caractère “,”. Dans l’exemple ci-dessous, la table EMP a recu le nom alternatif e et la table DEPT a le nom alternatif d.
Une non-equijointure est une jointure qui est basée sur une condition qui n’est pas d’égalité. La relation existante entre la table d’employées et celle de degrés de salarisation SALGRADE est un bon exemple pour l’utilisation de la non-equijointure. La relation à vérifier est la suivante : le salaire (sal) de la table des employées est entre le salaire minimum (losal) et le salaire maximum (hisal) de la table SALGRADE. La relation est basée sur d’autres opérateurs que celui d’égalité.
L’exemple antérieur fait une non-equijointure pour évaluer le degré de salariasation d’un employée. Son salaire doit être compris entre les deux limites de salarisation.
Il faut observer qu’après cette requête chaque employée est affiché exactement une fois. Il n’y a aucune répétition. Ceci se passe par deux raisons :
2.3. Jointure réflexive – jointure entre une table et elle-même
Parfois il faut faire une jointure réflexive, par exemple pour retrouver le manager de chaque employée, il faut faire une jointure entre la table EMP et elle-même. Pour trouver le manager de l’employée MILLER il faut faire les opérations suivantes:
• trouver l’employée MILLER dans la table EMP, en recherchant d’après le champ ename
• trouver l’identificateur du manager de MILLER, en recherchant d’après le champs mgr .
• trouver le nom correspondant à l’identificateur trouvé précédemment, en recherchant de nouveau dans la table EMP, après le champs ename
En utilisant l’algorithme ci-dessus, on fait la même recherche deux fois, dans la même table. Pour éviter ceci, on utilise une jointure réflexive.
Dans l’exemple précédent, pour simuler deux tables différentes dans la clause FROM, on utilise deux alias : angajat et manager pour la même table EMP.
2.4. Jointure externe
2.4.1. Retrouver des enregistrement sans correspondance directe par la jointure externe.
Si une ligne d’une jointure, ne satisfait pas la condition de jointure, elle ne va pas apparaître dans le résultat de la requête. Par exemple, si on essaie de trouver tous les employées ave leurs managers, on obtiendra un résultat incomplet. On fera une equijointure sur la table EMP. En exécutant la requête
on aura 13 lignes à la place de 14, car le président KING n’a pas de manager.
2.4.2. La syntaxe de la jointure externe
On utilise la jointure externe pour trouver des lignes des tables qui ne satisafont pas la condition de jointure.
L’opérateur pour la jointure externe est le signe plus ( + ).
SELECT tabela1.coloana, tabela2.coloana
FROM tabela1, tabela2
WHERE tabela1.coloana (+) = tabela2.coloana;
SELECT tabela1.coloana, tabela2.coloana
FROM tabela1, tabela2
WHERE tabela1.coloana = tabela2.coloana (+);
2.4.3. Utilisation de la jointure externe
Les lignes qui manquent du résultat de la jointure peuvent être obtenues à l’aide de l’opérateur de jointure externe dans la condition de join. L’opérateur de jointure externe, c'est-à-dire l’opérateur (+), se trouve du côté de la condition de jointure où manquent les lignes contenant l’information. La présence de l’opérateur (+) a comme effet la création de quelques lignes contenant des valeurs nulles, avec lesquelles on va faire le produit cartésien des lignes de la table qui n’a pas d’informations manquantes.
Dans la syntaxe:
| Tabela1.coloana =Tabela2.coloana (+) | condition de jointure entre les deux tables |
(+) |
L’opérateur de jointure externe qui va être placé dans l’un des membres de la condition de jointure, de la clause WHERE, mais pas dans les deux. ATENTION: Utilisez l’opérateur après le nom de la colonne appartenant à la table qui contient des lignes sans correspondence. |
Exemple:
Cet exemple affiche aussi les départements sans employées, en mettant des valeurs nulles pour les champs e.ename et e.deptno.
Un autre exemple serait de retrouver les employées et leurs managers, ci-inclus l’employée KING .
2.4.4. Restrictions pour la jointure externe
• L’opérateur de jointure externe doit apparaître seulement d’un côté de la condition de jointure, du côté de la table qui ne contient pas une information complète concernant la condition de join. Il retrouve les valeurs qui apparaissent dans la table après laquelle il est spécifié et qui n’apparaissent pas dans la table correspondante de la condition de jointure.
• La condition qui contient l’opérateur de jointure externe ne peut pas aussi contenir un opérateur IN et elle ne peut pas être relationnée avec une autre condition par l’opérateur OR.
3. Jointures utilisant la syntaxe de SQL 1999
On utilise la jointure pour retrouver des données d’une ou plusieurs tables.
SELECT tabela1.coloana, tabela2.coloana
FROM tabela1
CROSS JOIN tabela2] |
[NATURAL JOIN tabela2] |
[JOIN tabela2 USING (nume_coloana)] |
[JOIN tabela2
ON (tabela1.nume_coloana=tabela2.nume_coloana)] |
[LEFT | RIGHT | FULL OUTER JOIN tabela2
ON (tabela1.nume_coloana=tabela2.nume_coloana)];
En utilisant la syntaxe de SQL 1999, on obtien les mêmes résultats que ceux présentés dans les pages précédentes.
| Tabela1.coloana | Représente la colonne et la table d’où on extrait les données. |
| CROSS JOIN | Retourne le produit cartésien des tables spécifiées. |
| NATURAL JOIN | Fait une jointure sur le même nom de colonne |
| JOIN tabela2 | |
| USING nume_coloana | Fait une equijointure sur le nom de la colonne. |
| JOIN tabela2 | |
ON tabela1.nume_coloana = tabela2.nume_coloana |
Fait une equijointure basée sur la condition de la clause ON de la jointure. |
LEFT/RIGHT/FULL OUTER |
3.1. Création d’une jointure croisée
La clause CROSS JOIN crée le produit cartésien des deux tables.
Exemple:
Ce qui est équivalent dans les versions de Oracle, avec:
3.2. Création d’une jointure naturelle
• La condition de jointure naturelle est représentée par la comparation de toutes les colonnes qui ont le même nom des deux tables.
La sélection des lignes des deux tables est faite en prenant les données qui ont des valeurs égales sur les colonnes en cause.
ATENTION: Pour pouvoir réaliser une jointure naturelle, les colonnes de la condition de jointure doivent avoir le même nom et le même type de données, sinon on a une erreur.
3.2.1. Extraction des enregistrement par l’opération de jointure naturelle.
Exemple:
Dans l’exemple ci-dessus, la colonne après laquelle on fait la jointure est deptno , car elle est la seule commune aux deux tables. S’il y avait eu plusieurs colonnes communes, on aurait fait la jointure sur toutes les colonnes.
La jointure naturelle se fait dans les versions de Oracle, par une equijointure:
L'implémentation de restrictions pour une jointure naturelle est faite par une clause WHERE.:
3.3. La clause USING
Si certaines colonnes ayant le même nom, mais types de données différents, on peut modifier la jointure naturelle en utilisant la clause USING pour spécifier les colonnes à utiliser pour une equijointure.
Utilisez la clause USING pour avoir une seule colonne quand il y a plusieurs possibles colonnes correspondantes dans la syntaxe de la requête.
N’utilisez pas un nom de table ou un nom alternatif dans la colonne à laquelle fait référence la demande.
La jointure naturelle et la clause USING s’excluent réciproquement.
Exemples:
La requête précédente a une syntaxe qui obéit aux conditions énnoncées ci-dessus, donc elle est correcte.
Cette requite n’est pas correcte parce que laAceasta cerere nu este corecta deoarece coloana care apare in interiorul clauzei USING apare asociata cu aliasul de tabela in clauza WHERE.
ATENTION: Si pentru joinul natural, coloanele care au acelasi nume in cele doua tabele nu trebuie folosite in interiorul cererii cu referinte de tabele sau aliasuri.
Cet exemple fait la jointure entre le numéro de département de la table EMP et le numéro de département de la table DEPT, ayand comme résultat l’extraction des employées et des locations où ils travaillent.
Ceci peut être exprimé dans les versions de Oracle, comme une equijointure:
La condition de jointure pour la jointure naturelle est en fait une equijointure de toutes les colonnes ayant le même nom.
Pour établir des conditions supplémentaires ou de diverses colonnes pour la jointure, on utilise la clause ON.
La condition de jointure est séparé d’autres conditions de selections des données (de recherche de certaines données).
La clause ON crée un code beaucoup plus lisible.
La condition d’après ON est séparée d’autres conditions de recherche ou de filtrage.
Clauza ON poate fi folosita in modul urmator, in vederea realizarii joinului intre coloane cu nume diferite:
Cet exemple représente en fait une jointure réflexive de la table EMP, basé sur les colonnes empno et mgr .
3.4.1. La clause ON pour la jointure de trois tables
En SQL 1999 les jointures sont réalisées de gauche à droite, telle que la premiere jointure effctuée est EMP JOIN DEPT. La première condition de jointure peut faire référence aux colonnes des tables EMP et DEPT, mais dans aucun cas de la table SALGRADE. La deuxième condition de jointure peut faire référence aux colonnes des trois tables.
Cet exemple s’exprime, dans les versions de Oracle, par une jointure de trois tables de la manière suivante::
3.5. Jointure de plusieurs tables
Il faut mentionner qu'on peut faire la liaison de plusieurs tables, en utilisant m ême plusieurs types de jointures. On peut combiner JOIN avec LEFT/RIGHT/FULL OUTER JOIN (voir les paragraphes suivants).
3.6. Jointure interne versus la jointure externe
En SQL: 1999, la jointure des deux tables avec l’extraction des lignes correspondantes est une jointure interne.
Une jointure de deux tables qui retourne les résultats de l’opération de jointure interne mais aussi les colonnes qui ne trouvent pas un correspondant par la jointure interne est un LEFT (RIGHT) OUTER JOIN.
Une jointure entre deux tables qui retourne un résultat équivalent à celui d’une jointure interne et d’un LEFT(RIGHT) OUTER JOIN est un FULL OUTER JOIN.
Jointure – comparation entre la syntaxe de SQL:1999 et celle de Oracle
Cette requête retourne comme résultat toutes les lignes de la table EMP, qui est une table du membre gauche de la jointure, même s’il n’y a aucun correspondent dans la table DEPT.
Dans les versions de Oracle:
3.8. RIGHT OUTER JOIN
Cette requête retourne comme résultat toutes les lignes de la table DEPT, qui est une table du membre gauche de la jointure, même s’il n’y a aucun correspondent dans la table EMP.
Dans les versions de Oracle:
3.9. FULL OUTER JOIN
Cette requête retourne comme résultat toutes les lignes de la table EMP même s’il n’y a aucun correspondant dans la table DEPT. Elle retourne aussi toutes les lignes de la table DEPT, même s’il n’y a aucun correspondent dans la table EMP.
Il n'y a pas de correspondent avec la syntaxe précédente de Oracle.
Conditions supplémentaires:
Les conditions supplémentaires peuvent être utilisées dans le cadre de la clauze WHERE.Comme on peut observer dans l’exemple ci-dessus, on retrouve seulement les employées qui ont comme manager un employée avec mgr=7698.
Après cette lecon vous devriez savoir comment utiliser la jointure pour afficher des données de plusieurs tables en Oracle les versions jusqu’à 8i et en SQL:1999 pour la version 9i.
Il y a plusieurs moyens de réaliser la jointure.
4.1. Les types de jointure sont les suivantes:
Le résultat du produit cartésien représente toutes les combinaisons de lignes. Ceci peut se réaliser, soit en omettant la clause WHERE, soit en spécifiant la clause CROSS JOIN.
4.3. Nom alternatif de table(alias)
L’utilisation des noms alternatifs de table augmente la vitesse avec laquelle on fait l’accès à la base de données et elle aide aussi à maintenir le code SQL dans des limites raisonnables, en économisant la mémoire et en lui offrant lisibilité.
5. Exercices pratiques:
Les exercices de cette section demandent d’écrire des jointures diverses de tables.
Cette partie est conçue pour offrir une variété d’exercices avec des jointures, en utilisant la syntaxe Oracle présentée dans la partie théorique.
Les exercices traitent les aspects suivants:
Ils ont le but de vous aider à maîtriser l’extraction des données de plusieurs tables. Essayez l’utilisation de la syntaxe propriétaire Oracle, tant que celle de SQL:1999.
5.1. Exercices de base
1. Ecrivez une requête qui affiche le nom de famille, le numéro du département et le nom du département pour tous les employées.
2. Créez un affichage unique de tous les boulots qui existent dans le département 80. Affichez aussi la location.
3. Ecrivez une requête qui affiche le nom de famille, le nom du département et la location pour tous les employées qui ont obtenu un comision.
4. Affichez le nom de famille et le nom du département pour tous les employées qui ont un ”A” (lettre majuscule) à l’intérieur de leurs noms de famille.
5.2. Exercices à niveau moyen de difficulté
5. Ecrivez une requête qui affiche le nom de famille, le boulot, le numéro du département et le nom du département pour tous les employées qui travaille à DALLAS.
6. Affichez les noms de famille de l’employée, le numéro ou, le nom de famille du chef et son numéro. Appelez les colonnes Employee, Emp # , Manager si Mgr# .
7. Modifiez la requête de l’exercice 4 pour afficher tous les employées, ci-inclus l’employée KING, qui n’a pas de manager. Ordonnez après leur numéro d’employée. Sauvegardez la requête dans un fichier et exécutez-la.
8. Ecrivez une requête qui affiche le nom de famille de l’employée, le numéro du département et tous les employées qui travaillent dans le même département avec un employée donné. Appelez chaque colonne dans une manière adéquate.
9. Afficher la structure de la table SALGRADE. Créez une requête qui affiche le nom, le boulot, le nom du département, le salaire et le degré de salarisation de chaque employée.
10. Ecrire une requête qui affiche le nom et la date d’embauche de toutes les personnes embauchées après MILLER.
11. Afficher le nom et la date d’embauche pour toutes les personnes embauchées avant leurs managers. Afficher aussi le nom et la date d’embauche de leur manager. Appelez les colonnes Employee, Emp embauche, Manager et Mgr embauche .
12. Afficher les employées, le salaire, le nom du manager et le salaire du manager pour les employées qui ont un salaire plus grand que celui de leur manager.
13. Afficher tous les employées qui ont le degré de salarisation 3 ( nom, salaire et classe de salarisation).
14. Afficher les locations de tous les départements, leur numéro et tous les types d’embauché de ce département-là.
15. Afficher les employées qui ont la même classe de salarisation que celle de leur manager, leur salaire et leur classe de salarisation.
5.3. Exercices supplémentaires
16. Afficher les noms des départements, de leurs locations, le nom, le boulot et le salaire des employées qui travaillent à l’adresse 1800.
17. Afficher le ID, le nom et l’adresse de chaque département et nombre d’employées de chaque département.
18. Quels boulots ont les employées des départements 20 et 30?
19. Quels boulots il y a dans les départements“RESEARCH” et “SALES” et combien d’employées ont ces boulots ? Afficher premièrement le boulot rencontré le plus souvent.
20.Afficher tous les employées dont les managers ont un salaire plus grand que 5000$! Afficher les colonnes suivantes : le nom de l’employée, le nom du manager , le salaire du manager et la gradation salariale de celui-ci.
21.Afficher le numéro du département, le nom, le nombre d’employées et leur salaire moyen dans tous les départements, tant que le nom, le salaire, et le boulot de tous les employées qui travaillent dans le domaine respectif.