11. Joindre plusieurs tables

Nous avons vu dans le premier chapitre que les bases de données étaient souvent composées de plusieurs tables. Ces tables sont interconnectées à travers des colonnes qui permettent de créer des liens entre les différentes tables.

Pour répondre à une question business du type:
Quelle est la tranche d'âge qui achète le plus de smartphone?

Il est nécessaire de combiner des informations provenant des 2 tables ci-dessus. Cette opération est appelée une jointure. Elle est réalisée en associant les lignes des tables qui partagent une relation commune. Pour comprendre plus en détail, il est important de comprendre les notions de clés primaires et clés étrangères.

Introduction aux Clés Primaires et Clés Étrangères

Clé Primaire (Primary Key) : Chaque table possède une colonne spécifique permettant d'identifier de manière unique chaque ligne dans une table. Par exemple, dans la table Utilisateur, la colonne ID est la clés primaires. Chaque valeur est unique et permet d'identifier une ligne, ici, un utilisateur.

Clé Étrangère (Foreign Key) : Une clé étrangère est la colonne d'une table qui fait référence à la clé primaire d'une autre table. Dans la table Commande, la colonne UtilisateurID est une clé étrangère. Elle fait référence à la clé primaire ID de la table Utilisateur. Cela établit une relation entre les 2 tables. Une clé étrangère peut apparaître plusieurs fois, reflétant les relations multiples avec une autre table.

Les jointures entre les tables se font en associant les valeurs correspondantes des clés. Par exemple, pour obtenir les détails des commandes avec les noms des utilisateurs, une jointure sera effectuée sur les colonnes ID de la table Utilisateur et UtilisateurID de la table Commande. Cela permet de lier les informations pertinentes des 2 tables en fonction des valeurs de leurs clés.

SELECT Utilisateur.ID, Utilisateur.Nom, Utilisateur.Age, Utilisateur.Ville, 
       Commande.Produit, Commande.Quantité
FROM Utilisateur
JOIN Commande ON Utilisateur.ID = Commande.UtilisateurID ;

Dans cet exemple, Utilisateur.ID signifie que tu sélectionnes la colonne ID de la table Utilisateur et Commande.Produit signifie que tu sélectionnes la colonne Produit de la table Commande. Le point "." sépare le nom de la table et le nom de la colonne. Lorsqu'une jointure est effectuée, il est commun d'utiliser cette écriture afin de préciser la table de provenance de la colonne.

Le résultat de la jointure est le suivant :

Remarque : Une bonne pratique est d'utiliser le point "." dès que 2 colonnes provenant de tables différentes porte le même nom.

Type de jointure

Chaque type de jointure répond à des besoins spécifiques en termes de récupération de données. Le choix du type de jointure dépend des résultats souhaités. Ci-dessous, voici un exemple générique de la syntaxe d'une jointure :

SELECT A.column1, B.column2, column3
FROM table1 AS A
  JOIN table2 AS B
  ON A.column1 = B.column1 ;

Dans la clause SELECT sont spécifiées les colonnes qui doivent être affichées.

• A.column1 fait référence à la colonne column1 de la table renommée table1 comme A.
• B.column2 fait référence à la colonne column2 de la table renommée table2 comme B.
• column3 fait référence à la colonne column3 sans spécifier la table, ce qui signifie qu'elle n'est présente que dans une seule des 2 tables, ici, on ne précise pas laquelle.

Dans la clause FROM :

• Les tables table1 et table2 sont renommées en A et B, ce qui simplifie les références aux colonnes.
• Dans cet exemple, l'opérateur JOIN spécifie que la requête effectuera une jointure entre les tables spécifiées. JOIN est utilisé avec d'autres mots-clés pour spécifier le type de jointure: INNER JOIN, LEFT JOIN, FULL JOIN ou CROSS JOIN.

Selon le type de jointure, le résultat peut aboutir à la création de valeurs NULL pour les colonnes qui n'ont pas de correspondance dans la seconde table.

Spécification de la Condition de Jointure :
La clause ON spécifie la condition de jointure, indiquant comment les lignes des tables doivent être combinées. Dans cet exemple, les lignes sont combinées lorsque la valeur de column1 de A est égale à la valeur de column1 de B.

ON A.column1 = B.column1

Pour chaque paire de lignes qui satisfait la condition, une ligne résultante est créée dans l'ensemble des résultats combinés.

Un aperçu de la table order1

OrderID : Identifiant unique de la commande.
CustomerID : Identifiant unique du client associé à la commande.
EmployeeID : Identifiant unique de l'employé traitant la commande.
OrderDate : Date de la commande.
RequiredDate : Date à laquelle la commande doit être livrée.
ShippedDate : Date d'expédition de la commande.
ShipperID : Identifiant unique du fournisseur d'expédition.
Freight : Coût de livraison.

Les tables order2 et order3 ont le même format.

Un aperçu de la table orderDetail

OrderID : Identifiant unique de la commande.
ProductID : Identifiant unique du produit dans la commande.
UnitPrice : Prix unitaire du produit au moment de la commande.
Quantity : Quantité du produit commandé.
Discount : Montant de la réduction appliquée au produit.

Un aperçu de la table product

ProductID : Identifiant unique du produit.
ProductName : Nom du produit.
QuantityPerUnit : Quantité de produit par unité (par exemple, "12 bouteilles par caisse").
UnitPrice : Prix unitaire du produit.
Discontinued : Indicateur indiquant si le produit est interrompu (1 pour oui, 0 pour non).
CategoryID : Identifiant de la catégorie du produit.

Un aperçu de la table employee

EmployeeID : Identifiant unique de l'employé.
EmployeeName : Prénom et Nom de famille de l'employé.
Title : Titre de l'employé au sein de l'entreprise.
BirthDate : Date de naissance de l'employé.
HireDate : Date d'embauche de l'employé.
City : Ville où est situé l'employé.
Country : Pays où est situé l'employé.
ReportsTo : Identifiant de l'employé auquel l'employé rapporte (supérieur hiérarchique).

INNER

/*Afficher les clients, les frais de livraison et le détail de la commande*/
SELECT o.customerId, o.freight,od.orderId, od.productId, od.unitPrice, od.quantity, od.discount
FROM northWind.order1 AS o
  INNER JOIN northWind.orders_detailsorderDetail AS od
  ON o.orderId=od.orderId

Le nom de la table dans laquelle se trouve la colonne est précisé avec l'alias "o" pour la table order1 et "od" pour la table orderDetail.

Question 11.1

Que va signifie od.* dans la requête ci-dessous? Pour accéder à la console SQL.

SELECT o.customerId, o.freight, od.*
FROM order1 AS o
  INNER JOIN northWind.orders_details AS od
  ON o.orderId=od.orderId

Question 11.2

Afficher les numéros de commandes de la table order1 ainsi que le nom et prénom de l'employé qui l'a préparé. Pour accéder à la console SQL.

Question 11.3

Afficher le prénom, nom, role, matricule ainsi que le nombre total de commandes préparées (rester sur order1). Pour accéder à la console SQL.

Question 11.4

Afficher le prénom, nom, role, ainsi que le nom et le prénom et le rôle du supérieur direct hiérarchique (la colonne reportTo). Pour accéder à la console SQL.

LEFT JOIN

SELECT productName, quantityPerUnit, od.*
FROM northWind.orders_details AS od
  LEFT JOIN northWind.products AS pr
  ON od.productID=pr.productID
WHERE od.unitPrice > 10
ORDER BY od.unitPrice

La requête ci-dessus affiche les produits à plus de 10€ qui ont été vendus.

Uniquement les produits qui ont été commandés et qui coûtent plus de 10 € apparaissent. Tous les produits qui n'ont jamais été commandées ne sont pas visibles car la table de référence (celle de gauche) est la table des commandes, orderDetail.

Question 11.5

Afficher uniquement le nom des employés (employeeName) qui ont préparé des commandes en juin 2015. Pour accéder à la console SQL.

A savoir : La clause RIGHT JOIN existe et elle fonctionne comme LEFT JOIN. La différence réside dans l'ordre des tables concernées. Dans LEFT JOIN toutes les lignes de la table à gauche (première table spécifiée) seront présentes dans le résultat final alors que dans RIGHT JOIN, la table de droite devient la référence, toutes les lignes de la table à droite sont présentes dans le résultat final.

FULL JOIN

SELECT e.employeeID, e.employeeName, o.orderID, 
FROM northWind.employees AS e
  FULL JOIN northWind.order3 AS o
  ON e.employeeID = o.employeeID
ORDER by o.orderDate

La requête ci-dessus affiche toutes les commandes de la table order3 ainsi que les vendeurs. Les vendeurs qui n'ont pas préparé de commandes sont également inclus dans les résultats.

Question 11.6

Quels sont les vendeurs ("Sales Representative") qui n'ont pas préparé de commandes dans order3 ? Pour accéder à la console SQL.

Question 11.7

Quels sont les commandes dans order3 qui n'ont pas été préparées par des Sales Representative ? Donner l'id de commande, id de l'employé, son nom et son titre. Pour accéder à la console SQL.

Nous avons vu comment utiliser LEFT JOIN, FULL JOIN et INNER JOIN pour joindre 2 tables. Pour joindre plus de 2 tables, la syntaxe reste la même.

SELECT e.employeeName, od.*
FROM northWind.employees AS e
  FULL JOIN northWind.order1 AS o
  ON e.employeeID = o.employeeID
  FULL JOIN northWind.orders_details AS od
  ON o.orderId=od.orderId

La requête ci-dessus affiche l'ensemble des informations des commandes ainsi que le nom de l'employé qui l'a préparé.

Voici une explication détaillée de la requête en 3 étapes :

• FROM : Les sources de données sont spécifiées dans la clause FROM. Dans ce cas, les tables employee (alias e), order1 (alias o) et orderDetail (alias od) sont sélectionnées comme sources.
• FULL JOIN : Dans ce cas, les jointures sont effectuées entre les employés (e) et les commandes (o) sur la colonne employeeID puis entre les résultats de la première jointure et les détails de commande (od) sur la colonne orderId. Les jointures complètes garantissent que toutes les lignes des fichiers sources sont incluses dans le résultat.
• SELECT : Les colonnes spécifiées dans la clause SELECT sont sélectionnées pour apparaître dans le résultat final. Dans ce cas, le nom complet de l'employé ( employeeName) et toutes les colonnes des détails de commande (od.*) sont incluses.

L'ensemble du processus permet de combiner les données des tables en utilisant FULL JOIN. Par conséquent, même les lignes qui n'ont pas de correspondance dans les autres tables sont incluses dans le résultat final. Le résultat de la requête est une table combinée qui inclut le nom de l'employé et toutes les colonnes des détails de commande.

Question 11.8

Utiliser les tables order1, orderDetail et employee situées dans la base de donnée northWind pour afficher le nom des employés, leur ID ainsi que la valeur totale de chacune des commandes préparées ? Pour accéder à la console SQL.

Jointure sur Plusieurs Colonnes

Jusqu'à présent, nous avons vu les jointures qui s'effectuaient sur une colonne mais il est possible d'effectuer des jointures en se basant sur plusieurs critères simultanément pour une correspondance plus précise. Voici comment cela fonctionne dans un contexte business.

Tu as 2 tables dans une base de données d'entreprise. Une table Salarié et une table Projet.

Ci-dessous, la table Salarié :

A présent, la table Projet :

Pour déterminer quel salarié peut être assigné à quel projet, il faut effectuer une jointure à la fois sur la colonne DepartementID (pour s'assurer que le salarié appartient au même département que le projet) et sur la colonne RoleID (pour vérifier que le rôle du salarié correspond au rôle requis par le projet). Cette jointure permet de filtrer les associations possibles en fonction des critères combinés de département et de rôle.

SELECT S.Nom, P.NomProjet, S.DepartementID, S.RoleID
FROM Salarie AS S
    JOIN Projets AS P 
    ON S.DepartementID = P.DepartementID AND S.RoleID = P.RoleRequisID

Le résultat est le suivant:

CROSS JOIN

Le CROSS JOIN renvoie le produit cartésien de 2 tables, c'est-à-dire toutes les combinaisons possibles des lignes entre les tables. Il n'y a pas besoin de créer des conditions de correspondance.

Supposons que nous ayons 2 tables, employee et department avec les données suivantes :

La table employee

La table department

SELECT *
FROM employees
CROSS JOIN departments ;

Résultats du CROSS JOIN :

Dans l'exemple donné, chaque ligne de employee est combinée avec chaque ligne de department grâce à CROSS JOIN, créant un produit cartésien. Le résultat montre toutes les associations possibles entre employés et départements.

Résumé

En résumé, voici les types de JOIN les plus utilisés :

où la colonne service est spécifiée dans la clause GROUP BY pour créer des groupes et AVG(salaire) correspond à la fonction d'agrégation appliquée pour obtenir le salaire moyen.

Une manière de généraliser la syntaxe ci-dessus est d'écrire :

SELECT column1, column2, aggregate_function(column3)
FROM table
GROUP BY column1, column2 ;

column1, column2 : Colonnes spécifiées pour créer des groupes distincts.
aggregate_function(column3) : Fonction d'agrégation appliquée à chaque groupe.

Question 11.9

Lister toutes les combinaisons possibles entre les catégories de produits et les fournisseurs.

Sur mon site, je donne accès à des bases de données open source sur des domaines différents. Pour y accéder, voici le lien : Accéder aux bases de données.

SQL Console

Question 11.1
Question 11.2
Question 11.3
Question 11.4
Question 11.5
Question 11.6
Question 11.7
Question 11.8