Index

SQL Server - Get Started Install SQL Server SQL Server Management Studio SQL Server - Data Types SQL Server - Naming Conventions SQL Server - CREATE Database SQL Server - CREATE Table Add Columns Rename Column, Table Drop Columns
SQL Queries
SQL Server - Insert Data SQL Server - Update Data SQL Server - Delete Data SQL Server - Select Query SQL Server - Inner Join Left Join Right Join Full Join

Follow US

SQL Server - RIGHT JOIN Query

The RIGHT JOIN is the reverse of LEFT JOIN. The RIGHT JOIN query returns all the records from the right table and matching records from the left table.

Here, the right side table is a table that comes to the right side or after the "RIGHT JOIN" phrase in the query, and the left table is a table that comes at the left side or before the "RIGHT JOIN" phrase.

The RIGHT JOIN returns NULL for all non-matching records from the left table.

In some databases, it is called RIGHT OUTER JOIN.

Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

In the above syntax, table2 is the right table and table1 is the left table.

For the demo purpose, we will use the following Employee and Department tables in all examples.

Employee Table

EmpId FirstName LastName Email Salary DeptId
1 'John' 'King' '[email protected]' 33000 1
2 'James' 'Bond'
3 'Neena' 'Kochhar' '[email protected]' 17000 2
4 'Lex' 'De Haan' '[email protected]' 15000 1
5 'Amit' 'Patel' 18000 4
6 'Abdul' 'Kalam' '[email protected]' 25000 2

Department Table

DeptId Name
1 'Finance'
2 'HR'
3 'Sales'

Now, look at the following RIGHT JOIN query.

SQL Server: RIGHT JOIN Query 

SELECT dept.DeptId, dept.Name, emp.empid, emp.FirstName  
FROM Employee emp 
RIGHT JOIN Department dept
ON emp.DeptId = dept.DeptId;

The above RIGHT JOIN query joins the Employee table and Department table where Employee is the left table and Department is the right table.

The above query will display the following result.

DeptId Name EmpId FirstName
1 'Finance' 1 'John'
1 'Finance' 4 'Lex'
2 'HR' 3 'Neena'
2 'HR' 6 'Abdul'
3 'Sales' NULL NULL

As you can see, it fetches all the records from the Department table and only matching records from the Employee table where emp.DeptId = dept.DeptId. It contains the 3, 'Sales' record from the Department table and NULL for Employee columns because there are no matching records in the Employee table whose DeptId is 3.

Now, let's change the right table and see how the result will be changed.

SQL Server: RIGHT Join Query

SELECT emp.empid, emp.FirstName, dept.DeptId, dept.Name 
FROM  Department dept
RIGHT JOIN Employee emp 
ON dept.DeptId = emp.DeptId;

Above, the left table is Department and the right table is the Employee. This query will display the following result.

EmpId FirstName DeptId Name
1 'John' 1 'Finance'
2 'James' NULL NULL
3 'Neena' 2 'HR'
4 'Lex' 1 'Finance'
5 'Amit' NULL NULL
6 'Abdul' 2 'HR'

As you can see, it dispays all the records from the right table Employee and matching rows from the left table Department. It returns NULL for all non-matching records from the Employee table.