MySQL LEFT JOIN Clause

The LEFT JOIN keyword in MySQL returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL on the right side when there is no match.

Syntax 

The typical syntax for the LEFT JOIN clause in MySQL is:

SELECT table1.column1, table2.column2,...
FROM table1 
LEFT JOIN table2 
ON table1.matching_column = table2.matching_column;

In this syntax: 

table1 and table2: These are the names of the tables you wish to join. 

table1.column1, table2.column2, ...: These are the columns you wish to select. 

matching_column: This is the common column that both tables share, which is used to match rows between the two tables.

Demo Database 

For the demonstration, we'll use our SchoolDB database. Our focus will be on the Students table and the Courses table. 

The Students table includes student_id, first_name, last_name, grade_level columns:

The Courses table includes course_id, course_name, and student_id columns:

Examples 

With our demo database ready, let's demonstrate some examples of the LEFT JOIN clause. 

Basic LEFT JOIN 

Here's how to get a list of students (first_name, last_name) and the courses (course_name) they are enrolled in:

SELECT Students.first_name, Students.last_name, Courses.course_name
FROM Students 
LEFT JOIN Courses 
ON Students.student_id = Courses.student_id;

This statement will return a list of students along with the respective courses they're enrolled in, and for any student without a course, the course_name will be NULL. 

LEFT JOIN with Additional Conditions 

You can pair a WHERE clause with the LEFT JOIN statement to filter results. For example, to find all students and any courses they are taking in the 12th grade:

SELECT Students.first_name, Students.last_name, Courses.course_name
FROM Students 
LEFT JOIN Courses 
ON Students.student_id = Courses.student_id
WHERE Students.grade_level = 12;

This statement will fetch all 12th-grade students and the courses they're taking if any. 

When to Use LEFT JOIN

Use LEFT JOIN when you want to retrieve all rows from the left table, and the matching rows from the right table. Rows from the left table with no match in the right table will have NULL in the right table's columns. 

Scenario: Using the Customers and Orders tables, if you want to list all customers and their orders (including customers who haven’t ordered anything), you'd use a LEFT JOIN.

Summary 

The LEFT JOIN clause in MySQL is a powerful tool for merging data from multiple tables, enabling you to keep all rows from the left table and matched ones from the right. 

With LEFT JOIN, you can effectively handle and visualize data structures and relationships, thereby expanding your overall database management skills. It is an essential function in your SQL toolkit, allowing you to efficiently utilize the capabilities of relational databases.