Right Join in SQL Server

Right Join in SQL Server

Introduction:
In SQL Server, the RIGHT JOIN is one of the types of JOIN operations used to retrieve data from two or more tables based on a related column between them. The RIGHT JOIN returns all the rows from the right table and the matching rows from the left table. If there is no match found, NULL values are returned for the columns from the left table.

Syntax:
The basic syntax for a RIGHT JOIN in SQL Server is as follows:

sql
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Explanation:

• SELECT: Specifies the columns you want to retrieve from the tables.
• table1: The left table in the join operation.
• RIGHT JOIN: Indicates that we are performing a RIGHT JOIN.
• table2: The right table in the join operation.
• ON: Specifies the condition for joining the two tables based on a common column.

Example:
Consider two tables: Employees and Departments.

Employees Table:

EmployeeID	EmployeeName	DepartmentID
1	John	101
2	Jane	102
3	Mike	101
4	Sarah	NULL

Departments Table:

DepartmentID	DepartmentName
101	HR
102	Finance
103	Marketing

SQL Query:

sql
SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

EmployeeID	EmployeeName	DepartmentName
1	John	HR
2	Jane	Finance
3	Mike	HR
4	Sarah	NULL
NULL	NULL	Marketing

Explanation of Output:

• The first three rows are the matching rows between Employees and Departments based on the DepartmentID.
• The fourth row includes an employee (Sarah) with a NULL value for the DepartmentID, as there is no matching department for this employee.
• The last row includes a department (Marketing) with NULL values for EmployeeID and EmployeeName, as there are no employees assigned to this department.

Conclusion:
The RIGHT JOIN in SQL Server allows us to retrieve all the rows from the right table and their matching rows from the left table, filling in the non-matching rows with NULL values. It is useful when you want to include all the records from the right table in your result set, regardless of whether there are matches in the left table.