MySQL LEFT JOIN with Examples


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_idcustomer_codecustomer_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_idcustomer_codecustomer_namegroup_id
1#CID_40001Josh Warner1
2#CID_40002Lisa Ramsey 
3#CID_40003Clifton Christensen2
4#CID_40004Iris Wilkerson2
5#CID_40005Charles Harmon 
6#CID_40006Glen Wood1
7#CID_40007Francis Lewis 
8#CID_40008William Barnett4

Groups:

group_idgroup_name
1Wholesales Customers
2Retail Customers
3Regular Customers
4Online 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_idcustomer_codecustomer_namegroup_idgroup_id_rightgroup_name
1#CID_40001Josh Warner11Wholesales Customers
2#CID_40002Lisa Ramsey   
3#CID_40003Clifton Christensen22Retail Customers
4#CID_40004Iris Wilkerson22Retail Customers
5#CID_40005Charles Harmon   
6#CID_40006Glen Wood11Wholesales Customers
7#CID_40007Francis Lewis   
8#CID_40008William Barnett44Online 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_idgroup_namecustomer_idcustomer_namecustomer_codegroup_id_right
1Wholesales Customers1Josh Warner#CID_400011
1Wholesales Customers6Glen Wood#CID_400061
2Retail Customers3Clifton Christensen#CID_400032
2Retail Customers4Iris Wilkerson#CID_400042
3Regular Customers    
4Online Customers8William Barnett#CID_400084

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.

Leave a Reply

Your email address will not be published. Required fields are marked *