LEFT JOIN
Returns all rows from the left table and the matching rows from the right. Where there is no match, the right columns come back as NULL.
SELECT s.name_en, e.course_id, e.grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;
Every student appears, even those with no enrollment.
RIGHT JOIN
The mirror image: all rows from the right table. In practice most teams use only LEFT JOIN and reorder the tables, because reading a mix of both directions is error-prone.
SELECT s.name_en, e.grade
FROM enrollments e
RIGHT JOIN students s ON s.id = e.student_id;
-- identical to the LEFT JOIN above
Finding unmatched rows (anti-join)
This is the most valuable use of LEFT JOIN:
-- students who have never enrolled
SELECT s.name_en
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.id IS NULL;
-- orphan enrollments pointing at deleted students
SELECT e.*
FROM enrollments e
LEFT JOIN students s ON e.student_id = s.id
WHERE s.id IS NULL;
The classic LEFT JOIN mistake
A condition on the right table belongs in ON, not WHERE. Putting it in WHERE turns the outer join back into an inner join, because NULL fails the comparison:
-- WRONG: students with no A grade disappear
SELECT s.name_en, e.grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.grade = 'A';
-- RIGHT: all students kept, grade shown only when it is 'A'
SELECT s.name_en, e.grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id AND e.grade = 'A';
FULL OUTER JOIN
MySQL has no FULL OUTER JOIN. Emulate it with UNION:
SELECT s.name_en, d.name_en AS dept
FROM students s LEFT JOIN departments d ON s.department = d.code
UNION
SELECT s.name_en, d.name_en
FROM students s RIGHT JOIN departments d ON s.department = d.code;
UNION removes duplicates, so the overlapping rows appear once.
Counting with LEFT JOIN
SELECT s.name_en, COUNT(e.id) AS courses_taken
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name_en;
Use COUNT(e.id), not COUNT(*) — COUNT(*) counts the NULL-filled row and reports 1 for students with no enrollments.