Post
Ask Question

Difference Between Inner Join And Right Join In SQL Server

Introduction

 
In this article, I am going to explain  the difference between inner join and right join with examples. This is one of the very common SQL server interview questions.
 
Here we will be using SQL Server 2017 or you can use SQL Server 2008 or above.
 
Read my previous Joins in SQL Server 2017  articles using the below links before reading this article, 

Definition of Joins

 
It is used to fetch/retrieve data from two or more related tables from the database. In general, tables are related to each other using foreign key constraints.
 
Prerequisites
 
SQL Server 2017 or you can use SQL server 2008 or above version.
 
Now, first we will create a Database and two tables to apply the joins for understanding.
 

Creating a Database and Two Tables

 
Step 1 - Create a Database
 
Open your SQL Server and use the following script to create the “chittadb” Database.
  1. Create database chittadb   
Now, select the script query then press F5 or click on the Execute button to execute the above script.
 
You should see a message, “Command(s) completed successfully.” This means your new database has been created.
 
Step 2 - Create first table
 
Open your SQL Server and use the following script to create table “tbl_Department”.
  1. create table tbl_Department  
  2. (  
  3. DeptId int primary key not null identity(1,1),  
  4. DeptName nvarchar(50),  
  5. DeptHead nvarchar(50),  
  6. Location nvarchar(100)  
  7. )  
Execute the above query to create “tbl_Department “.
 
You should see a message, “Command(s) completed successfully.”
 
Now, data is inserted into the table.
  1. Insert into tbl_Department values ( 'IT', 'Chitta', 'Chennai')  
  2. Insert into tbl_Department values ( 'Payroll', 'Akhil', 'Odisha')  
  3. Insert into tbl_Department values ( 'HR', 'Ram', 'Pune')  
  4. Insert into tbl_Department values ( 'Timesheet', 'Kannan', 'chennai')  
Execute the above query, you should see a message, “Command(s) completed successfully.”
 
Now retrieve all data from “tbl_Department” table.
  1. select * from tbl_Department  
output
 
Difference Between Inner join and Right join In SQL Server  
 
Step 3 - Create second table
 
Open your SQL Server and use the following script to create table “tbl_Employee”.
  1. Create table tbl_Employee  
  2. (  
  3.      EmpID int primary key not null identity(1,1),  
  4.      Name nvarchar(50),  
  5.      Gender nvarchar(50),  
  6.      country nvarchar(20),  
  7.      Salary int,  
  8.      DepartmentId int foreign key references tbl_Department(DeptId)  
  9. )  
Execute the above query to create “tbl_Employee “.
 
You should see a message, “Command(s) completed successfully.”
 
Now, data is inserted into the table.
  1. Insert into tbl_Employee values ( 'Jitu', 'Male','India',4000, 1)  
  2. Insert into tbl_Employee values ( 'Rani', 'Female','India', 5000, 3)  
  3. Insert into tbl_Employee values ( 'Rohit', 'Male','India', 5500, 1)  
  4. Insert into tbl_Employee values ( 'Dibas', 'Male','India', 6500, 2)  
  5. Insert into tbl_Employee values ( 'Gajendra', 'Male','India', 3800, 2)  
  6. Insert into tbl_Employee values ( 'Raja', 'Male','India', 9000, 1)  
  7. Insert into tbl_Employee values ( 'Jeni', 'Female','India', 5800, 3)  
  8. Insert into tbl_Employee values ( 'Chandin', 'Female','India', 8500, 1)  
  9. Insert into tbl_Employee values ( 'pintu', 'Male','India', 9500, NULL)  
  10. Insert into tbl_Employee values ( 'Subrat', 'Male','India', 9800, NULL)  
Execute the above query, you should see a message, “Command(s) completed successfully.”
 
Now retrieve all data from “tbl_Employee” table.
  1. select * from tbl_Employee  
output
 
Difference Between Inner join and Right join In SQL Server  
 
General Formula for Joins
  1. SELECT      ColumnList (whatever column you want to display)   
  2. FROM           LeftTableName   
  3. JOIN_TYPE  RightTableName   
  4. ON                 JoinCondition  
 INNER JOIN
 
Inner join returns only the matching rows between both the tables, non-matching rows are eliminated.
 
Example
 
Write a query to retrieve Name, Gender, Country, Salary and DeptName from tbl_Employee and tbl_Department table.
 
INNER JOIN Query
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM tbl_Employee  
  3. INNER JOIN tbl_Department  
  4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
OR
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM tbl_Employee  
  3.  JOIN tbl_Department  
  4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
Note
JOIN or INNER JOIN means both are same. It's always better to use INNER JOIN.
 
OR
  1. select emp.Name,emp.Gender,emp.country,emp.Salary,dept.DeptName   
  2.     from tbl_Employee emp   
  3.     inner join tbl_Department dept  
  4.     on emp.DepartmentId=dept.DeptId  
OutPut
 
Difference Between Inner join and Right join In SQL Server  
 

RIGHT JOIN or RIGHT OUTER JOIN

 
Right Join or Right Outer Join returns only the matching rows between both the tables, plus non-matching rows from the right table.
 
Example
 
Write a query to retrieve Name, Gender, Country, Salary and DeptName from tbl_Employee and tbl_Department table.
 
RIGHT JOIN or RIGHT OUTER JOIN Query
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM tbl_Employee  
  3. RIGHT JOIN tbl_Department  
  4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
OR
  1. SELECT Name, Gender,country, Salary, DeptName   
  2. FROM tbl_Employee  
  3. RIGHT OUTER JOIN tbl_Department  
  4. ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
Note
You can use, RIGHT JOIN or RIGHT OUTER JOIN. OUTER keyword is optional.

OR
  1. select emp.Name,emp.Gender,emp.country,emp.Salary,dept.DeptName   
  2.     from tbl_Employee emp   
  3.     RIGHT JOIN tbl_Department dept  
  4.     on emp.DepartmentId=dept.DeptId  
OutPut
 
Difference Between Inner join and Right join In SQL Server  
 

What is the difference between INNER JOIN and RIGHT JOIN?

 
INNER JOIN
 
Inner join returns only the matching rows between both the tables, non-matching rows are eliminated.
 
Difference Between Inner join and Right join In SQL Server  
 
RIGHT JOIN
 
Right Join or Right Outer Join returns only the matching rows between both the tables, plus non-matching rows from the right table.
 
Difference Between Inner join and Right join In SQL Server  

What is the Difference between INNER JOIN and JOIN

There is no difference between inner join and join, they are exactly the same. Similarly there is also no difference between 
  • LEFT JOIN and LEFT OUTER JOIN
  • RIGHT JOIN and RIGHT OUTER JOIN
  • FULL JOIN and FULL OUTER JOIN

Conclusion

 
In this article, I explained  the difference between inner join and right join in SQL Server with examples. I hope this article has helped you to understand this topic. Post your valuable feedback in the comments section.


Similar Articles