MySQL LEFT JOIN

Last updated: October 19, 2026Oct 19, 2026 4 min read

LEFT JOIN returns every row from the left table, together with matching rows from the right table. When there is no match, the right-hand columns are filled with NULL. Use it whenever missing matches matter: customers with no orders, products never sold, students without a department.

1. What LEFT JOIN Returns

SELECT columns
FROM left_table
LEFT [OUTER] JOIN right_table ON left_table.col = right_table.col;

2. A Basic LEFT JOIN

SELECT s.name, s.dept_id, d.dept_name
FROM students s
LEFT JOIN departments d ON d.dept_id = s.dept_id
ORDER BY s.name;

Result: 5 rows

Diya has no department, so her dept_name is NULL. With an INNER JOIN she would disappear.

3. Finding Rows Without a Match

Add WHERE right_table.key IS NULL to keep only unmatched rows, a pattern called an anti-join:

SELECT s.name
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
WHERE e.student_id IS NULL;

Students with no enrolments

SELECT c.title
FROM courses c
LEFT JOIN enrolments e ON e.course_id = c.course_id
WHERE e.course_id IS NULL;

Courses nobody has taken

4. Counting with Zeros

SELECT c.title, COUNT(e.student_id) AS enrolled
FROM courses c
LEFT JOIN enrolments e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title
ORDER BY enrolled DESC, c.title;

Result: 4 rows

5. Filters: ON vs WHERE

Show every student and only their A grades. The grade filter must go in ON; in WHERE it would remove students without an A:

SELECT s.name, e.course_id AS a_grade_course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id AND e.grade = 'A'
ORDER BY s.name;

Condition in ON: all 5 students

SELECT s.name, e.course_id AS a_grade_course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
WHERE e.grade = 'A'
ORDER BY s.name;

Condition in WHERE: only students with an A

6. Replacing NULLs with COALESCE

SELECT s.name, COALESCE(d.dept_name, 'Undeclared') AS department
FROM students s
LEFT JOIN departments d ON d.dept_id = s.dept_id
ORDER BY s.name;

Result: 5 rows

7. Chaining LEFT JOINs

Every student, their courses and course titles, keeping students with no enrolments:

SELECT s.name, c.title, e.grade
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
LEFT JOIN courses    c ON c.course_id  = e.course_id
ORDER BY s.name, c.title;

Result: 6 rows

8. Table Order Matters

A LEFT JOIN B keeps all of A; B LEFT JOIN A keeps all of B:

SELECT d.dept_name, s.name
FROM departments d
LEFT JOIN students s ON s.dept_id = d.dept_id
ORDER BY d.dept_name, s.name;

Result: 5 rows

Now History appears (with no student) and Diya doesn't.

9. Practice Exercises

SELECT d.dept_name, COUNT(s.student_id) AS students
FROM departments d LEFT JOIN students s ON s.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;

SELECT s.name
FROM students s
LEFT JOIN students m ON m.student_id = s.mentor_id
WHERE m.student_id IS NULL;

10. Summary

• LEFT JOIN keeps all left rows; unmatched right columns become NULL.
• WHERE right.key IS NULL finds rows without a match.
• Count right-side columns to get zeros; use COALESCE for display defaults.
• Put right-table filters in ON, not WHERE, to keep unmatched rows.
• LEFT OUTER JOIN is the same as LEFT JOIN.