The right join for the right situation?

Question

(Please pardon the pun in the question title.)

I'm currently teaching an introductory class to data science.

I understand the differences between a left join, a right join, an inner join and a full join. However, I am not sure how to explain to students when a particular join (say an inner join) is the appropriate join for that particular situation.

I am looking for three real-life examples:

• In example A, an inner join is the most appropriate.
• In example B, a left/right join is the most appropriate.
• In example C, a full join is the most appropriate.

I don't need all the details, but I do want the big picture ideas that show that in that real-life example, this join is most appropriate.

Note: I am teaching using the R package dplyr, so I have used the names of the dplyr join functions.

Answer 1

You're an online retailer. Like Amazon. You keep your purchase data for different categories of items in different tables, but all website users have one account with one ID.

Inner Join: You have two datasets, one with User IDs and purchases of clothing data, the second dataset has User IDs and purchases of books data. You want to find out who purchases both clothes and books from your site.

You inner join to find the User IDs and purchases for people who bought clothing and books. Any users who didn't buy both of these items will be 'dropped' from the final table.

Left Join You have one dataset with User IDs and account info(e.g. age, name) of all users. You want to build out this table to include some sales data, without dropping people from your complete list of users. So you would left join your purchase dataset to your account dataset (with User ID as the key). Any user who has made no purchases won't be dropped from your final table.

Right Join The opposite of left join. E.g. you want to keep all purchase data, even if for some reason your Account Info table does not contain the ID of the user who made that purchase.

Full (Outer) Join You want to Join account info and purchases tables, but you don't want to lose any entries in either table. So your final table will include Users who have made no purchases, and (perhaps mysteriously) purchases who have no users associated with them.

Answer 2

You can explain them using Venn diagram.

See more details about the query and examples here.

Answer 3

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:

1. 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.

2. 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.

3. 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?