Exécuter plusieurs jointures dans une requête dans MYSQL

1. Exécuter plusieurs jointures dans une requête dans MYSQL - Construction de requête
2. Exécuter plusieurs jointures dans une requête dans MYSQL - Jointure à trois tables avec une jointure naturelle
3. Exécuter plusieurs jointures en une seule requête dans MYSQL - Jointure à trois tables avec le mot-clé ON
4. Exécuter plusieurs jointures dans une requête dans MYSQL - Jointure à trois tables dans le bloc WHERE
5. Exécuter plusieurs jointures dans une requête dans MYSQL - Le cas de jointure externe

Vous êtes-vous déjà demandé comment inclure plusieurs jointures dans une requête dans MySQL ? Vous êtes arrivé au bon endroit. N’oubliez pas que les jointures nous permettent d’atteindre des informations dans d’autres tables. Ces informations sont contenues séparément pour éviter la redondance. Considérons l’exemple suivant. Commençons par créer trois tables.

• client(client_id, client_name) définit un client identifié par client_id et nommé client_name :

CREATE TABLE client (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(255)
);

• product(product_id, product_name, unit_price, supplier_cost) représente un produit dans le magasin identifié par product_id et nommé product_name vendu à unit_price. Le coût d’achat d’une unité du produit auprès du fournisseur est donné par supplier_cost :

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    unit_price INT,
    supplier_cost INT
);

• product_order(order_id, product_id, client_id, quantity) représente une commande identifiée par order_id faisant référence au produit product_id acheté par le client client_id avec une quantité de quantity :

CREATE TABLE product_order (
    order_id INT PRIMARY KEY,
    product_id INT NOT NULL,
    client_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES product(product_id),
    FOREIGN KEY (client_id) REFERENCES client(client_id)
);

Comme vous pouvez le voir, il est assez minimaliste, mais il fera le travail. Prenez un moment pour remarquer qu’il n’y a pas d’informations redondantes. Le nom du produit n’est pas présent dans la table product_order. Si tel était le cas, le nom du produit aurait été répété à chaque fois qu’il était acheté.

Notre travail ici est de rendre le profit réalisé pour chaque client. D’un point de vue commercial, des requêtes plus complexes et utiles peuvent être proposées, mais nous ne montrons qu’une jointure multi-tables. Vous pouvez remplir votre base de données avec les valeurs suivantes pour tester les requêtes.

INSERT INTO client VALUES (1, 'John');
INSERT INTO client VALUES (2, 'Mehdi');
INSERT INTO client VALUES (3, 'Ali');
INSERT INTO product VALUES (1, 'laptop', 500, 250);
INSERT INTO product VALUES (2, 'tablet', 600, 550);
INSERT INTO product_order VALUES (1, 1, 1, 3);
INSERT INTO product_order VALUES (2, 1, 1, 3);
INSERT INTO product_order VALUES (3, 2, 2, 6);

Exécuter plusieurs jointures dans une requête dans MYSQL - Construction de requête

Le bénéfice associé à une commande est calculé de la manière suivante :

$$profit = quantity * (unit\_price - supplier\_cost)$$

Comme vous pouvez le voir, pour notre requête cible, nous avons besoin de trois valeurs. La quantité se trouve dans product_order, le prix unitaire et le coût fournisseur se trouvent dans product, et enfin, le nom du client se trouve dans client. D’où la nécessité d’une jointure à trois tables. Nous donnons les résultats de la requête après chaque requête.

Exécuter plusieurs jointures dans une requête dans MYSQL - Jointure à trois tables avec une jointure naturelle

De par leur conception, les clés étrangères des différentes tables portent le même nom que les clés primaires référencées. Nous pouvons utiliser une jointure naturelle pour lier les trois tables de la manière suivante.

SELECT client_name, SUM(quantity * (unit_price - supplier_cost)) AS profit
FROM product_order 
     NATURAL JOIN product
     NATURAL JOIN client
GROUP BY client_id;

Production :

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

Exécuter plusieurs jointures en une seule requête dans MYSQL - Jointure à trois tables avec le mot-clé ON

Il existe une autre possibilité pour atteindre notre objectif. On peut utiliser le mot-clé ON comme dans :

SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order 
     JOIN product
        ON product_order.product_id = product.product_id 
     JOIN client
        ON product_order.client_id = client.client_id
GROUP BY client.client_id;

Production :

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

Exécuter plusieurs jointures dans une requête dans MYSQL - Jointure à trois tables dans le bloc WHERE

Enfin, les conditions sur lesquelles les jointures sont effectuées peuvent être incorporées dans le bloc WHERE lui-même.

SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order 
     JOIN product
     JOIN client
WHERE product_order.product_id = product.product_id 
      AND product_order.client_id = client.client_id
GROUP BY client.client_id;

Production :

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

Exécuter plusieurs jointures dans une requête dans MYSQL - Le cas de jointure externe

Rappelons qu’une jointure est effectuée sur des conditions d’égalité entre les attributs. S’il n’y a pas une telle égalité dans certaines lignes des tables, la ligne combinée ne sera pas incluse dans la jointure résultante (la jointure interne est la jointure par défaut).

Cela peut être problématique.

Pour la requête ci-dessus, les clients qui existent dans la base de données mais n’ont jamais acheté de produit n’apparaîtraient pas dans le résultat. Ils n’ont pas de lignes associées dans la table product_order, comme le montre l’image ci-dessous.

Un tel cas peut se produire lorsqu’une application Web est utilisée où certains clients ont créé un compte mais n’ont encore rien acheté. Une solution serait d’utiliser un LEFT OUTER JOIN où les clients n’ayant aucune commande précédente sont associés à des attributs NULL product_order.

La requête finale est :

SELECT client_name, SUM(IFNULL(quantity, 0) * (IFNULL(unit_price, 0) - IFNULL(supplier_cost, 0))) AS profit
FROM client
     LEFT OUTER JOIN product_order
         ON product_order.client_id = client.client_id
     LEFT OUTER JOIN product
         ON product.product_id = product_order.product_id
GROUP BY client.client_id;

Production :

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |
| Ali         | 0      |

Comme indiqué ci-dessus, s’il n’y a pas de commande pour le client courant, les attributs de la table product_order sont mis à NULL, y compris la quantité - idem pour les attributs de la table product. Si nous voulons une valeur de profit nulle pour ces clients, nous pouvons transformer la valeur de quantité NULL en zéro en utilisant la fonction IFNULL.

Idem avec unit_price et supply_cost. Toute autre valeur par défaut autre que 0 peut être utilisée. Cliquez ici pour plus de détails sur la fonction IFNULL.

Nous donnons dans l’image ci-dessous une illustration de la comparaison entre la jointure interne et la jointure externe.