How to Execute Multiple Joins in One Query in MYSQL

1. Execute Multiple Joins in One Query in MYSQL - Query Construction
2. Execute Multiple Joins in One Query in MYSQL - Three-Table Join With a Natural Join
3. Execute Multiple Joins in One Query in MYSQL - Three-Table Join With the ON Keyword
4. Execute Multiple Joins in One Query in MYSQL - Three-Table Join Within WHERE Block
5. Execute Multiple Joins in One Query in MYSQL - The Outer Join Case

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

• client(client_id, client_name) defines a client identified by client_id 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) represents a product in the store identified by product_id and named as product_name sold at unit_price. The cost of purchasing one unit of the product from the supplier is given by 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) represents an order identified by order_id referencing the product product_id purchased by the client client_id with a quantity of 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 is pretty minimalistic, but it will do the job. Take a moment to notice that there is no redundant information. The product’s name is not present in the product_order table. If that were the case, the product’s name would’ve been repeated every time it was purchased.

Our job here is to return the profit realized for each client. From a business perspective, more complex and useful queries can be proposed, but we are just showing a multi-table join. You can populate your database with the following values to test the queries.

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

Execute Multiple Joins in One Query in MYSQL - Query Construction

The profit associated with an order is calculated in the following way:

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

As you can see, for our target query, we need three values. The quantity is found in product_order, the unit price and supplier cost are found in product, and finally, the client name is found in client. Hence, the need for a three-table join. We give the query results after each query.

Execute Multiple Joins in One Query in MYSQL - Three-Table Join With a Natural Join

By design, the foreign keys in the different tables have the same name as the referenced primary keys. We can use a natural join to link the three tables 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    |

Execute Multiple Joins in One Query in MYSQL - Three-Table Join With the ON Keyword

There is another possibility to meet our goal. We can use the ON keyword as in:

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 - Three-Table Join Within WHERE Block

Finally, the conditions on which the joins are performed can be incorporated in the WHERE 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    |

Execute Multiple Joins in One Query in MYSQL - The Outer Join Case

Recall that a join is performed on conditions of equality between attributes. If there is no such equality in certain rows of the tables, the combined row will not be included in the resulting join (the inner join is the default one).

This can be problematic.

For the above query, the clients who exist in the database but never bought any product would not appear in the result. They have no associated lines in the product_order table, as shown in the image below.

Such a case could arise when a web application is used where some clients created an account but didn’t purchase anything yet. A solution would be using a LEFT OUTER JOIN where clients having no previous orders are associated with NULL product_order 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 stated above, if there is no order for the current client, the product_order table attributes are set to NULL, including the quantity- the same for the product table attributes. If we want a zero profit value for those clients, we can transform the NULL quantity value to zero using the IFNULL function.

The same with unit_price and supply_cost. Any other default value other than 0 can be used. Click here for details on the IFNULL function.

We give in the image below an illustration of how the inner join compares to the outer one.