Real time example for right join

In my opinion this is a very good sql server interview question. Here are the sequence of questions, one of our Youtube channel subscribers faced in a sql server interview. 

Question 1: Can you list different types of JOINS available in SQL Server
Answer: Inner Join, Left Join, Right Join, Full Join and Cross Join

Question 2: Can you tell me the purpose of Right Join?
Answer: Right Join returns all rows from the Right Table irrespective of whether a match exists in the left table or not.

Question 3: Can you give me an example?
Answer: Consider the following Departments and Employees tables.


In this case we use RIGHT JOIN To retrieve all Department and Employee names, irrespective of whether a Department has Employees or not.

Select DepartmentName, EmployeeName

From Employees

Right Join Departments

On Employees.DepartmentID = Departments.DepartmentID

Question 4: I accept you have understood the purpose of Right Join. Based on the above 2 tables, can you give me any other business case for using Right Join.
At this point the candidate being interviewed, had no other answer and he simply told the interviewer he can't think of anything else. The good news is that, inspite of not answering the last question, the candidate got this Job. 

The candidate then emailed me and asked, what do you think that interviewer might be looking for here?
Here is what I think the interviewer is looking for. I may be wrong. If you can think of anything else, please feel free to leave a comment so it could help someone else.

Another business case for using RIGHT JOIN on the above 2 tables is to retrieve all the Department Names and the total number of Employees with in each department.


SQL Query with Right Join

Select DepartmentName, Count(Employees.DepartmentID) as TotalEmployees

From Employees

Right Join Departments

ON Departments.DepartmentID = Employees.DepartmentID

Group By DepartmentName

Order By TotalEmployees

SQL Script to create the required tables

Create Table Departments

(

     DepartmentID int primary key,

     DepartmentName nvarchar(50)

)

GO

Create Table Employees

(

     EmployeeID int primary key,

     EmployeeName nvarchar(50),

     DepartmentID int foreign key references Departments(DepartmentID)

)

GO

Insert into Departments values (1, 'IT')

Insert into Departments values (2, 'HR')

Insert into Departments values (3, 'Payroll')

Insert into Departments values (4, 'Admin')

GO

Insert into Employees values (1, 'Mark', 1)

Insert into Employees values (2, 'John', 1)

Insert into Employees values (3, 'Mike', 1)

Insert into Employees values (4, 'Mary', 2)

Insert into Employees values (5, 'Stacy', 2)

GO