Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How to Use MySQL Joins on Alibaba Cloud MySQL Database

How to Use MySQL Joins on Alibaba Cloud MySQL Database

In this tutorial, we will go over the different MySQL joins that you can implement on your application to craft better, faster, optimized and high performing databases on Alibaba Cloud.

By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

MySQL is one of the most popular relational database management systems (RDBMS). It is secure, scalable and has high performance with around-the-clock uptime guarantee for many websites and applications.

Apart from its comprehensive transactions support, reduced cost of ownership and flexibility associated with open source technology, MySQL supports different types of joins.

A Structured Query Language (SQL) join is used to fetch data from multiple tables in just a single statement. Such queries reduce the server overhead and eliminate the need for sending data multiple times from the application to the MySQL server.

This optimizes the speed and performance of the front-end application and the server. Joins allow developers to craft normalized databases where facts are only represented once. Normalization is the key to reducing data redundancy and storage space. Without joins, normalization would not work.

Another great advantage of joins is the ability to perform filters, computations and sorting prior to displaying the results of an SQL statement. From a programmer's perspective, joins make SQL statements easier to read and maintain.

In this tutorial, we will go over the different MySQL joins that you can implement on your application to craft better, faster, optimized and high performing databases on Alibaba Cloud.

Prerequisites

To follow along with our tutorial, you will require the following in order to test the syntax and run our clear MySQL join examples:

  1. A valid Alibaba Cloud account. You can signup now to get free credit and test over 40 Alibaba Cloud articles.
  2. An Alibaba Cloud ECS instance running MySQL server or an ApsaraDB for MySQL instance
  3. A username and password for your MySQL server

Types of MySQL Joins

A join occurs when two or more tables are joined in an SQL statement. We are going to discuss three types of MySQL joins in this guide:

  1. Inner join
  2. Left join
  3. Right join

?
The syntax of the join command is shown below:
mysql>Select 
columns    
from table1
join_type table2
on join_condition

The join condition should follow after the ON keyword. To better understand how MySQL joins works, we will create a test database and populate it with some sample data. For the sake of simplicity, we are going to use a hypothetical store known as mystore.

This store sells products to multiple customers who can register on any of the 5 offices that are spread across the country. Each store has a unique store_id while customers are identified by a customer_id. A customer can only belong to one office.

Orders are recorded in sales table that captures the customer_id, sales _id and amount for the sale. We have prepared the SQL commands for creating this database to make it easy for you.

So login to your MySQL server and enter the command below to create the database:

$ mysql> Create database mystore;

Then run the command below to switch to the database:

mysql> Use mystore;

We will create the offices table first. So run the command below:

mysql> create table offices (office_id INT PRIMARY KEY, office_name VARCHAR(50) NOT NULL) Engine = InnoDB;

Then, insert the following sample data by entering the below SQL statements one by one:

mysql> insert into offices (office_id, office_name) values ('1', 'Hangzhou');
mysql> insert into offices (office_id, office_name) values ('2', 'Shenzhen');
mysql> insert into offices (office_id, office_name) values ('3', 'Shanghai');
mysql> insert into offices (office_id, office_name) values ('4', 'Guangzhou');
mysql> insert into offices (office_id, office_name) values ('5', 'Hongkong');

You can confirm the presence of the data by running the command below:

mysql> select * from offices;

You should see an output similar to the one shown below:

+-----------+-------------+
| office_id | office_name |
+-----------+-------------+
|         1 | Hangzhou    |
|         2 | Shenzhen    |
|         3 | Shanghai    |
|         4 | Guangzhou   |
|         5 | Hongkong    |
+-----------+-------------+

Next, we are going to create the customers table using the syntax below:

mysql> create table customers (customer_id INT PRIMARY KEY, office_id INT, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;

Just like we did with the offices table, run the commands below one by one to populate the customers table with sample data:

mysql> insert into customers (customer_id,  office_id, customer_name) values ('5435' ,'3','John Doe');
mysql> insert into customers (customer_id, office_id, customer_name) values ('6436', '1','Mary Smith');
mysql> insert into customers (customer_id, office_id, customer_name) values ('7828', '6','Richard Roe');
mysql> insert into customers (customer_id, office_id, customer_name) values ('8536', '2', 'Jane Frank');
mysql> insert into customers (customer_id, office_id,  customer_name) values ('9567', '8', 'Joe Bloggs');

To confirm the presence of records in the customers table, run the query below:

mysql> select * from customers;

The customers' list shown be displayed as shown below:

+-------------+-----------+---------------+
| customer_id | office_id | customer_name |
+-------------+-----------+---------------+
|        5435 |         3 | John Doe      |
|        6436 |         1 | Mary Smith    |
|        7828 |         6 | Richard Roe   |
|        8536 |         2 | Jane Frank    |
|        9567 |         8 | Joe Bloggs    |
+-------------+-----------+---------------+

After creating the offices and customers table, we can now create the sales table by running the command below:

mysql> create table sales (customer_id INT PRIMARY KEY, sales_id BIGINT, amount DOUBLE) Engine = InnoDB;

We will populate the sales table with some sample orders. So, run the commands below on the MySQL server:

mysql> insert into sales (customer_id, sales_id, amount) values ('5435', '1', '25000');
mysql> insert into sales (customer_id, sales_id, amount) values ('9567', '1', '17490');
mysql> insert into sales (customer_id, sales_id, amount) values ('7828', '1', '82560');
mysql> insert into sales (customer_id, sales_id, amount) values ('8930', '1', '49120');

Run the query below to confirm the presence of data in the sales table:

mysql> select * from sales;

An output similar to the one below should be displayed:

+-------------+----------+--------+
| customer_id | sales_id | amount |
+-------------+----------+--------+
|        5435 |        1 |  25000 |
|        7828 |        1 |  82560 |
|        8930 |        1 |  49120 |
|        9567 |        1 |  17490 |
+-------------+----------+--------+

With the sample data in place, we can go ahead and discuss the 3 types of MySQL joins that we listed above and illustrate the same by executing some examples.

MySQL Inner Join

This is the simplest form of MySQL join. When an inner join is executed it only returns records that meet the join condition.

For instance, if we run the inner join statement against the offices and customers table with the office_id as the matching column, MySQL will only return the customers who have a matching office_id from the offices table. Similarly, only the offices with associated customers will be returned.

The SQL syntax for the inner join should look like this:

mysql> select * from offices inner join customers on offices.office_id=customers.office_id;

Output:

+-----------+-------------+-------------+-----------+---------------+
| office_id | office_name | customer_id | office_id | customer_name |
+-----------+-------------+-------------+-----------+---------------+
|         3 | Shanghai    |        5435 |         3 | John Doe      |
|         1 | Hangzhou    |        6436 |         1 | Mary Smith    |
|         2 | Shenzhen    |        8536 |         2 | Jane Frank    |
+-----------+-------------+-------------+-----------+---------------+

As you can see above, some records were omitted from the offices and customers table because they don't satisfy the join condition.

To be specific, two offices(Guangzhou and Hongkong) were not displayed because they don't have any associated customer. On the other hand, Richard Roe and Joe Bloggs records were skipped on the output as the customers were not set with an office_id that exists on the offices table.

The traditional way of writing this statement is shown below:

mysql> select * from offices, customers where offices.office_id=customers.office_id;

We can also represent MySQL inner join visually using the diagram below. The area shaded with red shows the records that meet the join condition:

1

MySQL Left Join

MySQL left join returns all records from the left table with the associated records from the right table that match the join condition.

However, unlike an inner join, records from the left table are returned irrespective of whether they have a matching row on the right or not.

Since all records on the left table are returned, any left side record that does not have associated records on the right displays NULL values.

To put this into perspective, if we run a left join against the offices and customers table, the list of all offices will be returned irrespective of whether there is a customer associated with that office_id or not.

The syntax looks as follows:

mysql> select * from offices left join customers on offices.office_id=customers.office_id;

Output:

+-----------+-------------+-------------+-----------+---------------+
| office_id | office_name | customer_id | office_id | customer_name |
+-----------+-------------+-------------+-----------+---------------+
|         3 | Shanghai    |        5435 |         3 | John Doe      |
|         1 | Hangzhou    |        6436 |         1 | Mary Smith    |
|         2 | Shenzhen    |        8536 |         2 | Jane Frank    |
|         4 | Guangzhou   |        NULL |      NULL | NULL          |
|         5 | Hongkong    |        NULL |      NULL | NULL          |
+-----------+-------------+-------------+-----------+---------------+

As you can see above, Guangzhou and Hongkong do not have associated customers, hence NULL values are displayed on the right side of the output.

MYSQL left join can be illustrated by the diagram below in a pictorial manner. The area shaded in red displays the records that are returned:

2

MySQL Right Join

MySQL right join would respond just in the exact opposite manner as a left join. This query returns all data from the right side table and the matching records from the left side table. However, records from the right table are returned irrespective of whether there is a matching record on the left table.

To examine this behaviour, let us run a right join statement against our offices and customers table:

mysql> select * from offices right join customers on offices.office_id=customers.office_id;

Output:

+-----------+-------------+-------------+-----------+---------------+
| office_id | office_name | customer_id | office_id | customer_name |
+-----------+-------------+-------------+-----------+---------------+
|         3 | Shanghai    |        5435 |         3 | John Doe      |
|         1 | Hangzhou    |        6436 |         1 | Mary Smith    |
|      NULL | NULL        |        7828 |         6 | Richard Roe   |
|         2 | Shenzhen    |        8536 |         2 | Jane Frank    |
|      NULL | NULL        |        9567 |         8 | Joe Bloggs    |
+-----------+-------------+-------------+-----------+---------------+

As you can see from the output above, the right join query was able to return all customers(right side) irrespective of whether the customers are associated with an office based on the office_id field. Records from the left side table with NULL values show customers without a matching office.

Here is a pictorial representation of the right join:

3

Joining 3 or More Tables with MySQL

The maximum number of tables that can be referenced in a single join is 61. However, for better performance, you should try to limit the number to around a dozen.

Just like we did with two tables, we are going to join 3 tables using the syntax below:

mysql>Select 
columns    
from table1
join_type table2
on join_condition1
join_type table3
on join_condition_2

So, let's run the command above on all the 3 tables:

mysql>Select *
from offices
inner join customers
on offices.office_id=customers.office_id
inner join sales
on customers.customer_id=sales.customer_id;

The above statement should return a list of offices together with the associated data that meets the two different join conditions.

Sample output:

+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+
| office_id | office_name | customer_id | office_id | customer_name | customer_id | sales_id | amount |
+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+
|         3 | Shanghai    |        5435 |         3 | John Doe      |        5435 |        1 |  25000 |
+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+

As you can see above, only one record met the two conditions that we set for the inner join.

We can run the same query on the 3 tables using the left join command to examine the records:

mysql>Select *
from offices
left join customers
on offices.office_id=customers.office_id
left join sales
on customers.customer_id=sales.customer_id;

Sample output:

+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+
| office_id | office_name | customer_id | office_id | customer_name | customer_id | sales_id | amount |
+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+
|         3 | Shanghai    |        5435 |         3 | John Doe      |        5435 |        1 |  25000 |
|         1 | Hangzhou    |        6436 |         1 | Mary Smith    |        NULL |     NULL |   NULL |
|         2 | Shenzhen    |        8536 |         2 | Jane Frank    |        NULL |     NULL |   NULL |
|         4 | Guangzhou   |        NULL |      NULL | NULL          |        NULL |     NULL |   NULL |
|         5 | Hongkong    |        NULL |      NULL | NULL          |        NULL |     NULL |   NULL |
+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+

To make the output clear to read, we can run the query without repeating duplicate columns used on the join condition like this:

mysql>Select offices.office_id, offices.office_name, customers.customer_id, customers.customer_name, sales.sales_id, sales.amount
from offices
left join customers
on offices.office_id=customers.office_id
left join sales
on customers.customer_id=sales.customer_id;

Output:

+-----------+-------------+-------------+---------------+----------+--------+
| office_id | office_name | customer_id | customer_name | sales_id | amount |
+-----------+-------------+-------------+---------------+----------+--------+
|         3 | Shanghai    |        5435 | John Doe      |        1 |  25000 |
|         1 | Hangzhou    |        6436 | Mary Smith    |     NULL |   NULL |
|         2 | Shenzhen    |        8536 | Jane Frank    |     NULL |   NULL |
|         4 | Guangzhou   |        NULL | NULL          |     NULL |   NULL |
|         5 | Hongkong    |        NULL | NULL          |     NULL |   NULL |
+-----------+-------------+-------------+---------------+----------+--------+

Conclusion

In this tutorial, we covered the different types of MySQL joins and how you can apply them on your database hosted on Alibaba Cloud ApsaraDB for MySQL or Elastic Compute Service (ECS) instances. We have included the basic syntax, examples and pictorial representation to help you understand how SQL inner, left and right Joins work.

We believe the information gained from this guide will help you to write better and optimum SQL queries involving multiple tables to improve the speed of your web application or website.

MySQL ECS Database Tech Share ApsaraDB for RDS MySQL Joins
0 0 0
Share on

Read previous post:

How to Work with BLOB in MySQL Database Hosted on Alibaba Cloud

Read next post:

Alibaba Cloud ECS Instance Security Checklist for Ubuntu 16.04

francisndungu

31 posts | 8 followers

You may also like

Comments

francisndungu

31 posts | 8 followers

Related Products

More Posts by francisndungu

See All