What is RIGHT JOIN (RIGHT OUTER JOIN) in SQL Server ?

Right join is also called as Right Outer Join.
In Right Join SQL Server shows all data from right side table and common matching data from left side table. If matching record not found from left side table then SQL server show NULL value.

Below image will give you brief idea regarding right join. We have to tables and it is showing all data from right side table and matching data from left side table.

right join example digram in sql server

Syntax of Right Join

SELECT < list of columns >
FROM table1 AS t1
RIGHT JOIN
table2 AS t2 ON t1.column1Value = t2.Column1Value AND t1.column2Value = t2.Column2Value
WHERE < condition if any >

Note : We use the RIGHT JOIN keyword to join two or more table and ON keyword is use to add condition. We can add multiple condition using AND keyword.


Consider below two result set of tblStudentDetails table and tblStudentMarks table, having student and student marks details.
Get list of all student with there respective marks.

right join example in sql server

Example

SELECT sd.StudentId, sd.StudentName, sm.Subject, sm.Marks
FROM
tblStudentDetails AS sd
RIGHT JOIN
tblStudentMarks AS sm ON sd.StudentId = sm.StudentId

Output

right join example in sql server


Note : It return all data from right side table and matching data from left side table.
tblStudentDetails called left table because it is available to left side of 'RIGHT JOIN' keyword and same for tblStudentMarks called as right table because it is available to right side 'RIGHT JOIN' keyword.


RIGHT JOIN with where condition.

Consider the below result set and select student whose marks greater than 50 and subject in Math and Science .

right join with where condition in sql server

Example

SELECT sd.StudentId, sd.StudentName, sm.Subject, sm.Marks
FROM
tblStudentDetails AS sd
RIGHT JOIN
tblStudentMarks AS sm ON sd.StudentId = sm.StudentId
WHERE sm.Marks BETWEEN 40 AND 80

Output

right join example in sql server