MySQL LEFT JOIN Clause

The LEFT JOIN keyword in MySQL returns all 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 is a very useful operation for scenarios where you want to fetch all records from one table (the left table) even if there are no matches in the other table (the right table).

Syntax 

The general 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 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. If a student is not enrolled in any 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 any courses they're enrolled in. If a 12th-grade student is not enrolled in any course, the course_name will be NULL. 

Summary 

  • Returns all rows from the left table and the matched rows from the right table.
  • If there's no match, the result will contain NULL for every column of the right table.

Comments

Post a Comment