How to Use MySQL Joins on Alibaba Cloud MySQL Database

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
mysql>Select 
columns
from table1
join_type table2
on join_condition
$ mysql> Create database mystore;
mysql> Use mystore;
mysql> create table offices (office_id INT PRIMARY KEY, office_name VARCHAR(50) NOT NULL) Engine = InnoDB;
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');
mysql> select * from offices;
+-----------+-------------+
| office_id | office_name |
+-----------+-------------+
| 1 | Hangzhou |
| 2 | Shenzhen |
| 3 | Shanghai |
| 4 | Guangzhou |
| 5 | Hongkong |
+-----------+-------------+
mysql> create table customers (customer_id INT PRIMARY KEY, office_id INT, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;
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');
mysql> select * from customers;
+-------------+-----------+---------------+
| 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 |
+-------------+-----------+---------------+
mysql> create table sales (customer_id INT PRIMARY KEY, sales_id BIGINT, amount DOUBLE) Engine = InnoDB;
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');
mysql> select * from sales;
+-------------+----------+--------+
| customer_id | sales_id | amount |
+-------------+----------+--------+
| 5435 | 1 | 25000 |
| 7828 | 1 | 82560 |
| 8930 | 1 | 49120 |
| 9567 | 1 | 17490 |
+-------------+----------+--------+

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.

mysql> select * from offices inner join customers on offices.office_id=customers.office_id;
+-----------+-------------+-------------+-----------+---------------+
| 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 |
+-----------+-------------+-------------+-----------+---------------+
mysql> select * from offices, customers where offices.office_id=customers.office_id;

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.

mysql> select * from offices left join customers on offices.office_id=customers.office_id;
+-----------+-------------+-------------+-----------+---------------+
| 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 |
+-----------+-------------+-------------+-----------+---------------+

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.

mysql> select * from offices right join customers on offices.office_id=customers.office_id;
+-----------+-------------+-------------+-----------+---------------+
| 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 |
+-----------+-------------+-------------+-----------+---------------+

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.

mysql>Select 
columns
from table1
join_type table2
on join_condition1
join_type table3
on join_condition_2
mysql>Select *
from offices
inner join customers
on offices.office_id=customers.office_id
inner join sales
on customers.customer_id=sales.customer_id;
+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+
| office_id | office_name | customer_id | office_id | customer_name | customer_id | sales_id | amount |
+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+
| 3 | Shanghai | 5435 | 3 | John Doe | 5435 | 1 | 25000 |
+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+
mysql>Select *
from offices
left join customers
on offices.office_id=customers.office_id
left join sales
on customers.customer_id=sales.customer_id;
+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+
| 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 |
+-----------+-------------+-------------+-----------+---------------+-------------+----------+--------+
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;
+-----------+-------------+-------------+---------------+----------+--------+
| 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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com