Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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)
)
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

sql server interview questions and answers

1 comment:

  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?
    PLEASE HELP ME

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.