LEFT JOIN
A LEFT JOIN returns all rows from the left table along with any matching rows from the right table.
If there is no match, the result shows NULL for columns from the right table.
Syntax
Here is the syntax for a LEFT JOIN:
LEFT JOIN Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
- The left table (
table1) is listed first. - All rows from the left table are included, even if there is no match in the right table.
Example: Students and Enrollments
Below is an example of a LEFT JOIN between the students and enrollments tables.
students
| student_id | name |
|---|---|
| 1 | John Miller |
| 2 | Lisa Brown |
| 3 | David Smith |
| 4 | Emily Davis |
| 5 | Michael Jones |
enrollments
| student_id | class_name |
|---|---|
| 1 | Math |
| 1 | Science |
| 2 | History |
| 5 | Art |
LEFT JOIN example
SELECT students.name, enrollments.class_name
FROM students
LEFT JOIN enrollments
ON students.student_id = enrollments.student_id;
Result:
| name | class_name |
|---|---|
| John Miller | Math |
| John Miller | Science |
| Lisa Brown | History |
| David Smith | NULL |
| Emily Davis | NULL |
| Michael Jones | Art |
David Smith and Emily Davis included even though they have no enrollments.
How is LEFT JOIN useful?
Use LEFT JOIN when you want to:
- Keep all rows from the left table, regardless of matches
- Identify records in the left table without corresponding entries in the right table
- Build full lists, such as all customers with or without purchases
Want to learn more?
Join CodeFriends Plus membership or enroll in a course to start your journey.