Left Join in MySQL
Introduction
Before we jump into the concept of 'Left Join in MySQL', let's take a step back and understand what is a 'Join'. In MySQL, a 'Join' is a method that retrieves data from two or more database tables. The tables are mutually connected, meaning that data is correlated between the tables.
There are several types of Joins in MySQL, but in this tutorial, we will focus on the 'Left Join'.
What is a Left Join in MySQL?
A 'Left Join' in MySQL is used to return all the rows from the left table and the matched rows from the right table. If no match is found, the result is NULL on the right side. In simpler terms, a 'Left Join' gives you all the records from the main (left) table, whether there's a matching record in the secondary (right) table or not.
Syntax of Left Join in MySQL
The basic syntax of a Left Join in MySQL is as follows:
SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
In this syntax:
table1is the left table.table2is the right table.matching_columnis the column common to both tables.
How to Use Left Join in MySQL?
To illustrate how Left Join works, let's consider two tables: Students and Courses. The Students table contains students' data, and the Courses table contains data of courses taken by the students.
Students table:
| ID | Name | Age |
|---|---|---|
| 1 | John | 22 |
| 2 | Alice | 25 |
| 3 | Bob | 20 |
Courses table:
| ID | CourseName |
|---|---|
| 1 | Math |
| 3 | Programming |
Now, if we want to join these two tables to see what courses each student has taken, we would use a Left Join. The SQL query would look like this:
SELECT Students.Name, Courses.CourseName
FROM Students
LEFT JOIN Courses
ON Students.ID = Courses.ID;
The result would be:
| Name | CourseName |
|---|---|
| John | Math |
| Alice | NULL |
| Bob | Programming |
As you can see, the result includes all students whether they have a course listed or not. Alice, who does not have a matching course in the Courses table, still appears in the result with CourseName as NULL.
Conclusion
In this tutorial, we learned about the 'Left Join' in MySQL. We understood its syntax and how to use it with a practical example. Remember, a Left Join returns all the records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side. This makes the Left Join a powerful tool in SQL for retrieving and manipulating data across multiple tables.