Plan du site  
français  English
pixel
pixel

Articles - Étudiants SUPINFO

Chapitre 04 - Récupérer des données depuis plusieurs tables

Les jointures : Présentation

Parfois, vous avez besoin d’utiliser des données de plus d’une seule table.

Dans l’exemple suivant, le rapport affiche des données de deux tables différentes :

  • Les IDs des employés existent dans la table EMPLOYEES.

  • Les IDs des départements existent dans les tables EMPLOYEES et DEPARTMENTS.

  • Les noms des départements existent dans la table DEPARTMENTS.

Pour créer un tel rapport, vous avez besoin de lier les tables EMPLOYEES et DEPARTMENTS et accéder aux données de chacune d’elles.

Types de jointures

Afin de joindre les tables, vous pouvez utiliser la syntaxe de jointure conforme au standard SQL : 1999.

  • Cross joins (produit cartésien)

  • Natural joins (jointures naturelles)

  • Clause USING

  • Full (or two-sided) outer joins (jointures externes)

  • Arbitrary join condition for outer joins (conditions supplémentaires)

Utilisez une jointure pour récuperer les données à partir de plusieurs tables.

SELECT table1.colonne, table2.colonne
FROM   table1
       [NATURAL JOIN table2] |
       [JOIN table2 USING (nom_colonne)] |
       [JOIN table2
ON     (table1.nom_colonne = table2nom_colonne)]|
       [LEFT|RIGHT|FULL OUTER JOIN table2
ON     (table1.nom colonne = table2.nom colonne)]|
       [CROSS JOIN table2];

Syntaxe :

  • table1.colonne : indique la table et la colonne à partir desquelles les données sont récupérées

  • NATURAL JOIN : joint deux tables basées sur le même nom de colonne

  • JOIN table USING nom_colonne : crée une équi-jointure basée sur le nom de la colonne

  • JOIN table ON table1.nom_colonne = table2.nom_colonne : crée une équijointure basée sur la condition dans la clause ON

  • LEFT/RIGHT/FULL OUTER : est utilisé pour créer des jointures externes

  • CROSS JOIN : retourne un produit cartésien de deux tables

Qualifier des noms de colonne ambiguës

En utilisant des préfixes de table

Vous aurez besoin de qualifier les noms de colonne avec le nom de table pour éviter certaines ambiguïtés.

Sans préfixes de table, la colonne DEPARTMENT_ID dans la liste de la clause SELECT peut provenir autant de la table DEPARTMENTS que de la table EMPLOYEES. Il sera donc nécessaire d'ajouter le préfixe de la table pour exécuter votre requête :

SELECT employees.employee_id, employees.last_name,
       departments.department_id, departments.location_id
FROM   employees JOIN departments
ON     employees.department_id = departments.department_id;

S'il n'y a aucune colonne commune entre les tables, il n'est pas nécessaire de qualifier les colonnes. Cependant, utiliser les préfixes de table accroît les performances car, vous indiquez précisément au serveur Oracle où trouver les colonnes.

Quand vous réalisez une jointure en utilisant la clause USING, vous ne pouvez pas qualifier une colonne utilisée dans cette même clause USING. En outre, si cette colonne est utilisée n'importe où dans le dans l‘ordre SQL, vous ne pouvez pas lui donner d'alias.

En utilisant des alias de table

Qualifier des noms de colonne avec des noms de table peut devenir une perte de temps, particulièrement si les noms de table sont longs. Vous pouvez utiliser des alias de table plutôt que des noms de table.

De la même façon qu'un alias de colonne donne à la colonne choisie un autre nom, un alias de table donne à la table un autre nom. Les alias de table aident à rendre le code SQL plus compacte et reduisent la quantité de mémoire utilisée.

SELECT e.employee_id, e.last_name, d.location_id, department_id
FROM   employees e JOIN departments d
USING  (department_id);

Remarquez la façon dont les alias de table sont utilisés dans la clause FROM de l'exemple. Le nom de table est spécifié entièrement, suivi d'un espace et finalement de l'alias de table. La table EMPLOYEES possède l'alias e et la table DEPARTMENTS possède l'alias d.

Règles :

  • Les alias de table peuvent avoir 30 caractères de longueur au maximum, mais les alias plus courts sont préférables.

  • Si un alias de table est utilisé pour une table particulière dans la clause FROM, alors l'alias de table doit être utilisé à la place du nom de la table dans toute la requête SELECT.

  • Les alias de table doivent être explicits.

  • Un alias de table est valide uniquement pour la clause SELECT courante.

Natural Joins (Jointures Naturelles)

Définition

Vous pouvez joindre automatiquement deux tables ayant chacune une colonne de même nom et de même type de données. Pour ce faire, utilisez les mots clé NATURAL JOIN.

La jointure peut être réalisée seulement sur les colonnes qui possèdent le même nom et le même type de données dans chacune des tables. Si les colonnes possèdent le même nom mais n’ont pas le même type de données, la syntaxe NATURAL JOIN renvois une erreur.

Récupérer des enregistrements avec les jointures naturelles

SELECT department_id, department_name, location_id, city
FROM   departments
NATURAL JOIN locations;

Dans cet exemple, la table LOCATIONS est jointe à la table DEPARTMENTS grâce à la colonne LOCATION_ID qui est la seule colonne de même nom et de même type dans les deux tables. Si d’autres colonnes communes existent, la jointure les utilise également.

Natural Joins avec la clause WHERE

Des restrictions supplémentaires peuvent être implémentées avec une clause WHERE lors de l’utilisation des jointures naturelles. Seules les lignes contenant l’ID de département 20 ou 50 sont retournées :

SELECT department_id, department_name, location_id, city
FROM   departments
NATURAL JOIN locations
WHERE  department_id IN (20, 50);

La clause USING

Les jointures naturelles utilisent toutes les colonnes avec le nom et le type de données concordants pour joindre les tables. La clause USING peut être utilisée pour spécifier seulement les colonnes qui doivent être utilisées pour l’équijointure.

Les colonnes référencées dans la clause USING ne doivent pas posséder de qualificatif (nom de table ou alias) dans la requête SQL.

Par exemple, la requête suivante est valide :

SELECT l.city, d.department_name
FROM   locations l JOIN departments d USING (location_id)
WHERE  location_id = 1400;

La requête suivante est invalide parce que LOCATION_ID a un qualificatif dans la clause WHERE :

SELECT l.city, d.department_name
FROM   locations l JOIN departments d USING (location_id)
WHERE  d.location_id = 1400;

ORA-25154: column part of USING clause cannot have qualifier

La même restriction est valable pour les jointures naturelles (NATURAL joins). Donc les colonnes qui ont le même nom dans chacune des tables doivent être utilisées sans qualificatif.

Pour déterminer le nom du département d’un employé, vous comparez les valeurs dans la colonne DEPARTMENT_ID de la table EMPLOYEES avec celles dans la colonne DEPARTMENT_ID de la table DEPARTMENTS. La relation entre les tables EMPLOYEES et DEPARTMENTS est une équijointure; ce qui signifie que les valeurs dans la colonne DEPARTMENT_ID de chaque table doivent être égales. Habituellement, ce type de jointure implique une clé primaire et une clé étrangère complémentaires.

Les équijointures sont également appelées simple joins (jointures simples) ou inner joins (jointures internes).

SELECT employees.employee_id, employees.last_name,
       departments.location_id, department_id
FROM   employees JOIN departments
USING  (department_id);

Cet exemple joint la colonne DEPARTMENT_ID de table table EMPLOYEES avec celle de la table DEPARTMENTS, et ainsi montre le lieu de travail des employés.

La clause ON

Utilisez la clause ON pour spécifier une condition de jointure. Ceci vous permet de spécifier les conditions de jointure séparément des conditions de recherche ou de restriction dans la clause WHERE.

Récupérer des enregistrements avec la clause ON

Dans cet exemple, les colonnes DEPARTMENT_ID des tables EMPLOYEES et DEPARTMENTS sont jointes en utilisant la clause ON. Lorsqu'un ID de département de la table EMPLOYEES est le même que l'ID de département de la table DEPARTMENTS, la ligne est retournée.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

Vous pouvez également utiliser la clause ON pour joindre des colonnes qui ont des noms différents.

Self-Joins (Auto-jointures) avec la clause ON

Parfois, vous avez besoin de joindre une table à elle-même. Pour trouver le nom de manager de chaque employé, vous devez joindre la table EMPLOYEES à elle-même, ou encore de réaliser une auto-jointure.

Par exemple, pour trouver le nom du manager de Lorentz, vous devez :

  • Trouver Lorentz dans la table EMPLOYEES en recherchant dans la colonne LAST_NAME.

  • Trouver le numéro de son manager de Lorentz en regardant dans la colonne MANAGER_ID. Le numéro de manager de Lorentz est 103.

  • Trouver le nom du manager grâce à l'EMPLOYEE_ID 103 en regardant dans la colonne LAST_NAME. Le numéro d'employé d'Hunold est 103, donc Hunold est le manager de Lorentz.

La clause ON peut être également utilisée pour joindre des colonnes ayant les noms différents à l'intérieur d'une même table ou dans des tables différentes.

L'exemple ci-dessous est une auto-jointure de la table EMPLOYEES basée sur les colonnes EMPLOYEE_ID et MANAGER_ID.

SELECT e.last_name emp, m.last_name mgr
FROM   employees e JOIN employees m
ON     (e.manager_id = m.employee_id);

Appliquer des conditions supplémentaires à une jointure

Vous pouvez appliquer des conditions supplémentaires à une jointure.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id)
AND    e.manager_id = 149;

L'exemple précédent crée une jointure entre les tables EMPLOYEES et DEPARTMENTS et renvoie uniquement les employés dont l'ID du manager est 149. Pour ajouter des conditions supplémentaires à la clause ON, vous pouvez ajouter la clause AND.

En alternative, vous pouvez utiliser une clause WHERE pour appliquer des conditions supplémentaires:

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id)
WHERE  e.manager_id = 149;

Créer des jointures Three-Way avec la clause ON

Une jointure three-way est une jointure de trois tables. Dans la syntaxe conforme SQL:1999, les jointures sont réalisées de gauche à droite.

SELECT employee_id, city, department_name
FROM   employees e
JOIN   departments d
ON     d.department_id = e.department_id
JOIN   locations l
ON     d.location_id = l.location_id;

La première jointure réalisée est EMPLOYEES JOIN DEPARTMENTS. La première condition de jointure peut faire référence aux colonnes des tables EMPLOYEES et DEPARTMENTS mais ne peut pas faire référence aux colonnes de la table LOCATIONS. La seconde condition de jointure peut faire référence à toutes les colonnes des trois tables.

Non-Equijoins (Non-Equijointures)

Définition

Une non-equijointure est une condition de jointure qui contient autre chose qu'un opérateur d'égalité.

La relation entre les tables EMPLOYEES et JOB_GRADES en est un exemple de non-équijointure. Une relation entre les deux tables est que la valeure de la colonne SALARY de la table EMPLOYEES doit être comprise entre les valeurs dans les colonnes LOWEST_SALARY et HIGHEST_SALARY de la table JOB_GRADES. La relation est obtenue en utilisant un opérateur autre que celui de l'égalité (=).

Récupérer des enregistrements avec les non-equijointures

L'exemple crée une non-équijointure pour évaluer le grade du salaire d'un employé. Le salaire doit être compris entre une paire LOWEST_SALARY et HIGHEST_SALARY qui détermine un rang.

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e JOIN job_grades j
ON     e.salary BETWEEN j.lowest_sal AND j.highest_sal;

Remarquez que chaque employé apparaît juste une fois lors de l'exécution de la requête.

Aucun employé n'est répété dans la liste. Il y a deux raisons à cela :

  • Aucune ligne dans la table de rangs (JOB_GRADES) ne contient des grades qui se superposent. Le salaire d'un employé peut se trouver uniquement entre le salaire le plus bas (LOWEST_SALARY) et le salaire le plus élevé (HIGHEST_SALARY) d'une seule ligne dans la table de rangs (JOB_GRADES).

  • Tous les salaires des employés sont compris entre les limites fournit par la table de rangs. Donc, aucun employé ne gagne moins que la valeur la plus basse contenue dans la colonne LOWEST_SAL et aucun ne gagne plus que la plus forte valeur contenue dans la colonne HIGHEST_SAL.

D'autres conditions (comme <= et >=) peuvent être utilisées, mais BETWEEN est la plus simple.

Souvenez-vous de spécifier la limite inférieure d'abord et la limite supérieure ensuite quand vous utilisez BETWEEN.

Les alias de table ont été spécifiés dans l'exemple pour des raisons de performance, pas pour éliminer des ambiguïtés.

Outer joins (Jointures Externes)

Définition

Si une ligne ne satisfait pas une condition de jointure, la ligne n'apparaît pas dans le résultat de la requête.

Par exemple, dans la condition d'équijointure des tables EMPLOYEES et DEPARTMENTS, l'ID du département 190 n'apparaît pas parce qu'il n'y a pas d'employés enregistré dans la table EMPLOYEES dans département en question (190). Au lieu de voir 20 employés dans le résultat, on n'obtient que 19 enregistrements.

De même, un employé peut n'être assigné à aucun département.

Joindre des tables en utilisant les clauses NATURAL JOIN, USING, ou ON résulte en une jointure interne (inner join).

Toute ligne ne remplissant pas une condition de jointure n'est pas affichée à la sortie. Pour retourner les lignes non vérifiées, vous pouvez utiliser une jointure externe.

Une jointure externe renvoie toutes les lignes satisfaisant la condition de jointure mais également certaines, voire toutes les lignes d'une table pour lesquelles aucune ligne de l'autre table ne satisfait pas condition de jointure.

Il existe trois types de jointures externes :

  • LEFT OUTER

  • RIGHT OUTER

  • FULL OUTER

LEFT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e LEFT OUTER JOIN departments d
ON     (e.department_id = d.department_id);

Cette requête renvoie toutes les lignes de la table EMPLOYEES, qui est la table de gauche, même s'il n'y a pas de résultat correspondant dans la table DEPARTMENTS (l'employé Grant n'est assigné à aucun département).

RIGHT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e RIGHT OUTER JOIN departments d
ON     (e.department_id = d.department_id);

Cette requête renvoie toutes les lignes de la table DEPARTMENTS, qui est la table de droite, même s'il n'y a pas de résultat correspondant dans la table EMPLOYEES (aucun employé n'est assigné au département 190).

FULL OUTER JOIN

SELECT e.last_name, d.department_id, d.department_name
FROM   employees e FULL OUTER JOIN departments d
ON     (e.department_id = d.department_id);

Cette requête renvoie toutes les lignes de la table EMPLOYEES, même s'il n'y a pas de résultat correspondant dans la table DEPARTMENTS. Elle renvoie également toutes les lignes de la table DEPARTMENTS, même s'il n'y a pas de résultat correspondant dans la table EMPLOYEES.

Produits cartésiens

Définition

Lorsqu'une condition de jointure est invalide ou complètement omise, le résultat est un produit cartésien dans lequel toutes les combinaisons de lignes sont affichées. Toutes les lignes de la première table sont jointes à toutes les lignes de la seconde table.

Un produit cartésien tend à générer un grand nombre de lignes et le résultat est rarement utile.

Vous devez toujours inclure une condition de jointure valide tant que vous n'avez pas un besoin spécifique de combiner toutes les lignes de toutes les tables.

Les produits cartésiens sont utiles pour certains tests, quand vous avez besoin de générer un grand nombre de lignes pour simuler une quantité de données importante.

Cet exemple affiche le nom d’employé et le nom de département à partir des tables EMPLOYEES et DEPARTMENTS. Aucune condition de jointure n’a été spécifiée, donc toutes les lignes de la table EMPLOYEES (20 lignes) sont jointes à toutes les lignes de la table DEPARTMENTS (8 lignes), et ainsi 160 lignes sont générées en sortie.

Cross Joins

SELECT last_name, department_name
FROM   employees
CROSS JOIN departments;

Cet exemple crée un produit cartésien des tables EMPLOYEES et DEPARTMENTS.

A propos de SUPINFO | Contacts & adresses | Enseigner à SUPINFO | Presse | Conditions d'utilisation & Copyright | Respect de la vie privée | Investir
Logo de la société Cisco, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société IBM, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sun-Oracle, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Apple, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sybase, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Novell, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Intel, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Accenture, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société SAP, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Prometric, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo du IT Academy Program par Microsoft, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management

SUPINFO International University
Ecole d'Informatique - IT School
École Supérieure d'Informatique de Paris, leader en France
La Grande Ecole de l'informatique, du numérique et du management
Fondée en 1965, reconnue par l'État. Titre Bac+5 certifié au niveau I.
SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels