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:
sqlSELECT 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:
sqlSELECT 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
andDepartments
based on theDepartmentID
. - The fourth row includes an employee (Sarah) with a
NULL
value for theDepartmentID
, as there is no matching department for this employee. - The last row includes a department (Marketing) with
NULL
values forEmployeeID
andEmployeeName
, 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.
0 Comments