Let’s say we want to display the name of all students and the names of the courses they are enrolled in if they are enrolled in a course.
Notice the results if we use an INNER JOIN.
There are only 22 records in the final result. It does not contain students who are either enrolled in an invalid course, or are not enrolled in a course.
This is a problem that can be solved using an
OUTER JOIN, specifically the
LEFT OUTER JOIN.
OUTER JOIN is a JOIN that also returns records that are unmatched from the criteria in
ON clause, along with the ones that match.
LEFT OUTER JOIN or a
LEFT JOIN returns records from the table at left in the JOIN, that are unmatched from the connecting query in
ON, along with the matched records.
Observe that the table
students is at the left in the
LEFT JOIN clause, so
all records from the
students table appear in the result. For the students who don’t have a course in which they are enrolled in, the course name is returned as blank.