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.

Now, look at the following 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.

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.

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.

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.