MySQL LEFT JOIN [Explained with Easy Examples]

In this tutorial, we will study the MySQL LEFT JOIN. We saw what a join is in the INNER JOIN article but let us refresh our memory again. A join is used to combine two or more tables using a commonly related column between them. 

There are different types of joins in MySQL. They are –

• Inner Join
• Left (Outer) Join
• Right (Outer) Join
• Full Outer Join

In this article, we will focus on the left join, sometimes also referred to as the left outer join, and how to use the MySQL LEFT JOIN keyword to make a left join.

What is a Left Join?

Suppose you have two tables – table1 and table2. A left join keyword will return all the records of table1 and those records that matched from table2. If the records did not match from the right side, then NULL is returned as value for those records. In other words, the left join includes the left table (table1) and the common records between table1 and table2. Let us see a diagrammatic representation of this.

It is important to note that some databases refer to the LEFT JOIN as LEFT OUTER JOIN.

Syntax of MySQL LEFT JOIN

SELECT expression FROM table1 [t1] 
LEFT JOIN table2 [t2] 
ON table1.column_name = table2.column_name;Code language: SQL (Structured Query Language) (sql)

Here ‘t1’ and ‘t2’ are optional aliases that you can have for the table names. We will see more of it in the examples. The columns mentioned after the ON keyword are the related common columns using which the left join is to be performed.

In the above query, ‘table1’ is the leftmost table, which will be the focus of the left join.

Examples of MySQL LEFT JOIN

Consider the below Students and Marks tables.

The Students table stores the details of the student in every row. The Marks table stores every student’s marks in English, Maths and Science out of 100.

Simple Example of MySQL LEFT JOIN

Let us now make a left join on the Students table and Marks table such that we see every student’s ID, first name, last name, and their marks in Science.

The first step is to find the commonly related columns between the two tables. In this case, they are ID from the Students table and StudentID from the Marks table. Let us now write a query for the left join using the SELECT statement.

SELECT s.FirstName, s.LastName, m.Science FROM Students s 
LEFT JOIN Marks m 
ON s.ID=m.StudentID;Code language: SQL (Structured Query Language) (sql)

In the above query, Students is our leftmost table. We use ‘s’ as a shorthand notation to refer to the Students table and ‘m’ for the Marks table. We get the output as follows –

Wait. Why do Manvi Khanna and Jay Shinde have NULL as their marks in Science? As you can see their IDs do not have a record in the Marks table and since the left join includes all the records from the leftmost table, their names are included in the result-set. However, since there is no corresponding data for Manvi Khanna and Jay Shinde’s records in the Marks table, LEFT JOIN returns NULL as their marks for Science.

Another observation you can notice is that the Marks table has a record for a student with ID 9. However, since there is no record in the Students table with the ID value as 9, LEFT JOIN excludes that record from the Marks table. As you read earlier, left join returns only the records from the leftmost table and the common records between the left and the right tables.

Using MySQL LEFT JOIN With Expressions

Building on the previous query, let’s make a left join on the Students table and Marks table such that we see every student’s ID, first name, last name, and percentage. To calculate the percentage of every student, we add their marks in the three subjects, divide it by 300 and then multiply that by 100. The query is –  

SELECT s.ID, s.FirstName, s.LastName, 
((m.English+m.Maths+m.Science)/300)*100 AS Percentage FROM Students s 
LEFT JOIN Marks m 
ON s.ID=m.StudentID;Code language: SQL (Structured Query Language) (sql)

And we get that output as –

MySQL LEFT JOIN With the HAVING Clause

Now, let us display the ID, first name, last name and percentage of the students who have more than 85%. Since we will have to filter the query through the alias Percentage, we will use the HAVING clause instead of the WHERE clause.

SELECT s.ID, s.FirstName, s.LastName, 
((m.English+m.Maths+m.Science)/300)*100 AS Percentage FROM Students s 
LEFT JOIN Marks m 
ON s.ID=m.StudentID HAVING Percentage>0;Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

LEFT JOIN, also referred to in some databases as LEFT OUTER JOIN is an extremely important operation when it comes to MySQL. Joining two tables and displaying that data provides useful insights into the data. I would highly recommend you to practice some examples on MySQL LEFT JOIN.