Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Part 11 - Real time example for right join

Suggested Videos:
Part 8 - SQL Query to find department with highest number of employees
Part 9 - Difference between inner join and left join
Part 10 - Join 3 tables in sql server

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.
Real time example for right join

In this case we use RIGHT JOIN To retrieve all Department and Employee names, irrespective of whether a Department has Employees or not.
real time example for right join in sql server

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.
business case for right join

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)

Create Table Employees
     EmployeeID int primary key,
     EmployeeName nvarchar(50),
     DepartmentID int foreign key references Departments(DepartmentID)

Insert into Departments values (1, 'IT')
Insert into Departments values (2, 'HR')
Insert into Departments values (3, 'Payroll')
Insert into Departments values (4, 'Admin')

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)

sql server interview questions and answers


  1. hello venkat sir
    In the above query we are using Count(Employees.DepartmentID),If we use only Count(Employees) why is it showing error?
    And for Select DepartmentName why isnt sql server throwing any error even if we are not using select Departments.Departmentname?

  2. since departmentid is common in both tables and there is a conflict so we should give departmentid from which table.But departmentname is there only in department table so no need to mention it again .

  3. If we exchange the tables in the join then it can still use left join.
    What is the use case where we need left join alone or right join alone (using the other should not be possible).


It would be great if you can help share these free resources