How to Use LEFT JOIN in MySQL
In this tutorial, you’ll learn how to use the MySQL LEFT JOIN.
LEFT JOIN is used to select all records from the left table and the matched records from the right table.
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_nam
Examples
Suppose you have two tables, named customers, and groups.
The customers table consists of four columns, named customer_id, customer_code, customer_name, and group_id.
The group_id column can be a NULL value.
The groups table consists of two columns, named group_id and group_name.
The group_id column of the customers table referenced the group_id column of the groups table.
Customers:
| customer_id | customer_code | customer_name | group_id |
|---|---|---|---|
| 1 | #CID_40001 | Josh Warner | 1 |
| 2 | #CID_40002 | Lisa Ramsey | |
| 3 | #CID_40003 | Clifton Christensen | 2 |
| 4 | #CID_40004 | Iris Wilkerson | 2 |
| 5 | #CID_40005 | Charles Harmon | |
| 6 | #CID_40006 | Glen Wood | 1 |
| 7 | #CID_40007 | Francis Lewis | |
| 8 | #CID_40008 | William Barnett | 4 |
Groups:
| group_id | group_name |
|---|---|
| 1 | Wholesales Customers |
| 2 | Retail Customers |
| 3 | Regular Customers |
| 4 | Online Customers |
Example 1
The following statement retrieves all records from the customers table and the matched records from the groups table:
SELECT customers.customer_id, customers.customer_name, customers.customer_code, customers.group_id, groups.group_id group_id_right, groups.group_name
FROM customers
LEFT JOIN groups
ON customers.group_id = groups.group_id;
Returned result set:
| customer_id | customer_code | customer_name | group_id | group_id_right | group_name |
|---|---|---|---|---|---|
| 1 | #CID_40001 | Josh Warner | 1 | 1 | Wholesales Customers |
| 2 | #CID_40002 | Lisa Ramsey | |||
| 3 | #CID_40003 | Clifton Christensen | 2 | 2 | Retail Customers |
| 4 | #CID_40004 | Iris Wilkerson | 2 | 2 | Retail Customers |
| 5 | #CID_40005 | Charles Harmon | |||
| 6 | #CID_40006 | Glen Wood | 1 | 1 | Wholesales Customers |
| 7 | #CID_40007 | Francis Lewis | |||
| 8 | #CID_40008 | William Barnett | 4 | 4 | Online Customers |
Example 2
The following statement retrieve all records from the groups table and the matched records from the customers table:
SELECT groups.group_id, groups.group_name, customers.customer_id, customers.customer_name, customers.customer_code, customers.group_id group_id_right
FROM groups
LEFT JOIN customers
ON groups.group_id = customers.group_id;
Returned result set:
| group_id | group_name | customer_id | customer_name | customer_code | group_id_right |
|---|---|---|---|---|---|
| 1 | Wholesales Customers | 1 | Josh Warner | #CID_40001 | 1 |
| 1 | Wholesales Customers | 6 | Glen Wood | #CID_40006 | 1 |
| 2 | Retail Customers | 3 | Clifton Christensen | #CID_40003 | 2 |
| 2 | Retail Customers | 4 | Iris Wilkerson | #CID_40004 | 2 |
| 3 | Regular Customers | ||||
| 4 | Online Customers | 8 | William Barnett | #CID_40008 | 4 |
Example 3: Use an alias instead of a table name
You can use an alias instead of a table name, as the following example shows:
SELECT c.customer_id, c.customer_name, c.customer_code, c.group_id, g.group_id group_id_right, g.group_name
FROM customers c
LEFT JOIN groups g
ON c.group_id = g.group_id;
As you can see, I have used the character c as the alias of the customers table and g as the alias of the groups table.
Using alias instead of the table name makes it easy for us to write the SQL statement, and the length is shorter.
In this tutorial, you’ve learned how to use the MySQL LEFT JOIN.
You use LEFT JOIN to retrieve all the records from the left table and the matched records from the right table.
If you need to retrieve all the records from the right table and the matched records from the left table, use RIGHT JOIN.
If you need to retrieve all records when there are matching values in both tables, you can use INNER JOIN.