LEFT, RIGHT & FULL JOIN — Including Unmatched Rows

10 min read

Outer Joins

Outer joins include rows that have no match in the other table, padding the missing side with NULL. This is useful for finding orphaned records or including all records regardless of matching data.

LEFT JOIN — all rows from the left table, matched rows from the right (or NULLs)
RIGHT JOIN — all rows from the right table, matched rows from the left (or NULLs)
FULL JOIN — all rows from both tables; NULLs where no match exists

sql

-- All students, with their enrolments if any (students with no enrolments included)
SELECT s.name, e.course_id, e.enrolled_at
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.id;

-- Students who have NEVER enrolled in anything
SELECT s.name
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.id
WHERE e.id IS NULL;

-- All courses, even those no one enrolled in
SELECT c.title, COUNT(e.id) AS enrolment_count
FROM courses c
LEFT JOIN enrolments e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY enrolment_count DESC;

RIGHT JOIN is rarely used in practice because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Many teams forbid RIGHT JOIN in their style guides for consistency.

Ready to test yourself?

Practice SQL— quiz & coding exercises