SQL RIGHT JOIN — How to Include All Rows from the Right Table

SQL RIGHT JOIN Syntax

SELECT columns
FROM table1
RIGHT JOIN table2
  ON table1.column = table2.column;

RIGHT JOIN is also called RIGHT OUTER JOIN — both are identical. The keyword OUTER is optional.

Sample Tables

We will use the same Employees and Departments tables from the Joins Introduction chapter:

+----+-----------+--------+--------+
| ID | Name      | DeptID | Salary |
+----+-----------+--------+--------+
| 1  | Priya K.  | 101    | 55000  |
| 2  | John D.   | 102    | 62000  |
| 3  | Arun M.   | 101    | 78000  |
| 4  | Emma W.   | 103    | 51000  |
| 5  | Ravi S.   | NULL   | 72000  |
+----+-----------+--------+--------+

+-----+----------------+-----------+
| ID  | DeptName       | Location  |
+-----+----------------+-----------+
| 101 | IT             | Delhi     |
| 102 | Marketing      | London    |
| 103 | Sales          | Mumbai    |
| 104 | HR             | New York  |
+-----+----------------+-----------+

Notice that Ravi S. has a NULL DeptID (no department), and the HR department (ID 104) has no employees assigned to it.

SQL RIGHT JOIN Example

This query returns all departments with their employees. Departments without employees still appear:

SELECT e.Name, d.DeptName, d.Location
FROM Employees AS e
RIGHT JOIN Departments AS d
  ON e.DeptID = d.ID;

+-----------+-----------+-----------+
| Name      | DeptName  | Location  |
+-----------+-----------+-----------+
| Priya K.  | IT        | Delhi     |
| Arun M.   | IT        | Delhi     |
| John D.   | Marketing | London    |
| Emma W.   | Sales     | Mumbai    |
| NULL      | HR        | New York  |
+-----------+-----------+-----------+

The HR department appears with NULL for Name because no employee is assigned to department 104. Ravi S. is excluded because RIGHT JOIN only guarantees all rows from the right table (Departments).

RIGHT JOIN with WHERE Filter

You can add a WHERE clause to filter the results further. This query finds departments that have no employees:

SELECT d.DeptName, d.Location
FROM Employees AS e
RIGHT JOIN Departments AS d
  ON e.DeptID = d.ID
WHERE e.ID IS NULL;

+-----------+-----------+
| DeptName  | Location  |
+-----------+-----------+
| HR        | New York  |
+-----------+-----------+

The trick is checking WHERE e.ID IS NULL — this returns only the rows from the right table that had no match in the left table.

RIGHT JOIN vs LEFT JOIN

RIGHT JOIN and LEFT JOIN are mirror images of each other. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order:

Both queries produce the same result. Here is the LEFT JOIN equivalent:

SELECT e.Name, d.DeptName, d.Location
FROM Departments AS d
LEFT JOIN Employees AS e
  ON e.DeptID = d.ID;

RIGHT JOIN vs RIGHT OUTER JOIN

RIGHT JOIN and RIGHT OUTER JOIN are exactly the same. The keyword OUTER is optional and does not change the behavior:

SELECT e.Name, d.DeptName
FROM Employees AS e
RIGHT JOIN Departments AS d ON e.DeptID = d.ID;

SELECT e.Name, d.DeptName
FROM Employees AS e
RIGHT OUTER JOIN Departments AS d ON e.DeptID = d.ID;

For the complete JOIN syntax reference, see the official MySQL JOIN documentation. In the next chapter, you will learn SQL FULL JOIN to return all rows from both tables.

If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.

📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.

SELECT d.DeptName,
       COALESCE(SUM(e.Salary), 0) AS TotalSalary
FROM Employees AS e
RIGHT JOIN Departments AS d
  ON e.DeptID = d.ID
GROUP BY d.DeptName;

+-----------+-------------+
| DeptName  | TotalSalary |
+-----------+-------------+
| HR        | 0           |
| IT        | 133000      |
| Marketing | 62000       |
| Sales     | 51000       |
+-----------+-------------+

📖 Chapter 23 of 30 — SQL Tutorial on Neotech Navigators