Let's say we want to display the name of all students and all the courses in the database, in this fashion.
- For all the students we want to display the name of the course if it is a valid course.
- For students that are not enrolled in a course, or not enrolled in a valid course, we want to display a blank against course name.
- For all the courses that don't have a student enrolled in them, we want to display a blank against student name.
For this problem statement, all the students records and all the courses need to be shown, along with records that match through the join.
A FULL OUTER JOIN is used in such cases.
A FULL OUTER JOIN returns all the records from tables at both sides of the JOIN, that are unmatched from the connecting query in ON, along with the matched records.
Note that all the records from the courses table and all the records from the students table appear in the results.