A RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and the matching rows from the left table.

If there is no match in the left table, the result will show NULL for the left table columns.

Basic syntax:

SELECT columns
FROM left_table
RIGHT JOIN right_table
ON left_table.common_column = right_table.common_column;

Step 1: Example tables

Employees Table

EmployeeID FirstName LastName DepartmentID
1 John Doe 101
2 Jane Smith 102
3 Mike Brown 101
4 Sara White NULL

Departments Table

DepartmentID DepartmentName
101 IT
102 HR
103 Finance

Step 2: Simple RIGHT JOIN

Retrieve all departments with their employees, including departments without employees:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees AS e
RIGHT JOIN Departments AS d
ON e.DepartmentID = d.DepartmentID;

Result:

FirstName LastName DepartmentName
John Doe IT
Mike Brown IT
Jane Smith HR
NULL NULL Finance

Finance department has no employees, so FirstName and LastName are NULL.
Sara is excluded because she has no DepartmentID (no matching row in the right table).

Step 3: RIGHT JOIN with table aliases

SELECT e.FirstName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;
  • e → Employees
  • d → Departments

Result: Same as Step 3.

Table aliases make the query cleaner.

Step 4: RIGHT JOIN with WHERE clause

You can filter the result after the join:

SELECT e.FirstName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID
WHERE e.FirstName IS NULL;

Result:

FirstName DepartmentName
NULL Finance

Shows departments without employees.

Step 5: RIGHT JOIN with multiple conditions

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID
AND d.DepartmentName <> 'HR';

Result:

FirstName LastName DepartmentName
John Doe IT
Mike Brown IT
NULL NULL Finance
Jane Smith NULL

Only non-HR departments are considered for matching.

Categorized in:

SQL Server,