JIYIK CN >

Have you ever wondered how to include multiple joins in one query in MySQL? You have come to the right place. Remember that joins allow us to access information from other tables. This information is included separately to avoid redundancy. Let's consider the following example. Let's start by creating three tables.

• client(client_id, client_name)defines a customer client_ididentified by and named client_name.

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

• product(product_id, product_name, unit_price, supplier_cost)product_idrepresents the product identified by and named as sold product_nameat in the store unit_price. The cost of purchasing one unit of the product from the supplier is supplier_costgiven by:

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)Represents order_idthe order identified by , referencing client_idthe product purchased by customer product_id, with quantity 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)
);

As you can see, it's pretty minimalistic, but it does the job. Take a moment to notice that there's no redundant information. The product name is not product_orderin the table. If it were, the name of the product would be repeated every time a purchase was made.

Our job here is to return the profit achieved for each customer. From a business perspective, more complex and useful queries can be made, but we are just demonstrating a multi-table join. You can populate the database with the following values ​​to test the query.

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);

Executing multiple joins in one query in MYSQL - Query construction

The profit associated with an order is calculated as follows:

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

As you can see, for our target query, we need three values. product_orderFind the quantity in , productfind the unit price and supplier cost in , and finally, clientfind the customer name in . Therefore, a three-table join is required. We give the query results after each query.

Performing multiple joins in one query in MYSQL - Three table joins vs. natural joins

By design, foreign keys in different tables have the same name as the referenced primary key. We can link the three tables using a natural join in the following way.

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

Output:

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

Performing multiple joins in one query in MYSQL - ONThree table join using keyword

There is another possibility to achieve our goal. We can use ONthe keyword as follows:

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;

Output:

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

Execute multiple joins in one query in MYSQL - WHEREThree table join within a block

Finally, the conditions for executing the join can be incorporated into WHEREthe block itself.

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;

Output:

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

Executing multiple joins in one query in MYSQL - Outer Join Case

Recall that joins are performed on the condition of equality between attributes. If such equality does not exist in some rows of the tables, the merged rows will not be included in the resulting join (called an inner join, which is the default join). This can be problematic. In particular, for the above query, customers who exist in the database but have never purchased any products will not appear in the results. That's because they product_orderhave no associated rows in the table, as shown in the following figure. This situation may arise when using a web application, when some customers have created an account but have not yet purchased anything. One solution is to use LEFT OUTER JOIN, where customers without previous orders NULL product_orderare associated with the attributes. The final query is:

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;

Output:

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

As mentioned above, if the current customer has no orders, product_orderthe table properties are set to NULL, including quantity - productthe same table properties. If we want the profit value to be zero for these customers, we can use IFNULLthe if function to NULLconvert the quantity value to zero. The same unit_priceas supply_cost. You can use 0any other default value except . For IFNULLmore information on the if function, see https://www.w3schools.com/sql/func_mysql_ifnull.asp.

We give an illustration of inner joins compared to outer joins in the figure below.