Take, for example, an imaginary database of a "school" with three tables:
- Students (StudentID, Name)
- Courses (CourseID, Name)
- Enrollment (StudentID, CourseID)
In this context you could explain the joins as follows:
Inner join would be a natural operation for answering queries like "what courses is this student enrolled in" or "what students are enrolled in this course".
However, if a student is not enrolled in any courses, the query above would simply return no rows, whilst you might still want to have one row with the name of the student in the output and a NULL/NA value as its only "course". In this case you could prefer a left join.
The need for left/right/outer joins is even more common if you need to do some aggregations later on. Suppose you need to output a table, listing all courses alongside with the count of students enrolled in them. Using an inner join would only list the courses with at least one student. Using a left join would list all of them, including those with 0 students.
Right joins are somewhat esoteric and rarely used (I've written tons of SQL but never used a right join so far I think). Indeed, you can always use a left join in place of a right join, and most of the time people tend to think about data access in a "left to right" manner. The three possible situations for using a right join that I see are:
If you somewhy tend to "think" in a right-to-left manner. Suppose you want to list courses along with the number of students enrolled. The "left to right" thinking goes as follows: first select all Courses, then left-join all Enrollments, then aggregate.
You may also think differently. The "important" data source you want to start with is the Enrollments table, after all, so why not start by reading data from it, and perhaps even aggregating it first. Then you could join the Courses table to get the course names in place of IDs. In this situation you would be right-joining the Courses.
If you suspect a right join would be more efficient. Suppose that, in the example above, there are thousands of courses, only a couple of them are non-empty, with hundreds of students. In this case, a "straightforward" manner to execute a "Courses left join Enrollments" query would be to scan the Courses table, trying to match up each course by scanning the Enrollments table (assume the query optimizer is dumb and there are no indexes on the tables, for the sake of example -- is there a query optimzer in dplyr, after all?).
Only two courses would have matches but the scan would have to proceed for a thousand times. The resulting table would have the two nonempty course names repeated hundreds of times for each enrolled student alongside thousands of course names with NULL students, after which you'd proceed to the aggregation.
On the contrary, if we first aggregated Enrollments and then right-joined the Course names in the "right-to-left" manner, the aggregated Enrollments table would only have two rows, and thus perform just two ID matches over the Courses table (writing out the remaining course names as-is).
Fortunately, most SQL database engines are smart enough nowadays to deal with such optimizations internally without the need for the user to hand-tune the direction and order of joins in the query.
Finally, sometimes you generate SQL programmatically. In this case again, the need to "join in" the Course or Student names to some previously computed main result table could be quite common, and a right join perfectly appropriate.
That was a long digression on right situations for right joins, let's get back to the outer join. It is also rarely used, in my experience. They say outer joins are harder for SQL engines to optimize (although I can't at the moment exactly see why so). Anyway, in our example one could use an outer join to create a table of the form (Course, List of Students), with two extra conditions:
- All the empty courses would be included, showing their student list as a single NULL entry,
- There would be a special NULL course, listing all students who are not enrolled anywhere at all.
That could be a perfectly useful table, right?