MySQL RIGHT JOIN

The RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If a row in the right table has no matching row in the left table, NULL is returned for the left table's columns. It is the mirror image of LEFT JOIN.

Syntax

SELECT table1.column, table2.column
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

Sample Tables

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

INSERT INTO departments VALUES (1, 'HR');
INSERT INTO departments VALUES (2, 'IT');
INSERT INTO departments VALUES (3, 'Finance');
INSERT INTO departments VALUES (4, 'Marketing');

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT
);

INSERT INTO employees VALUES (1, 'Ravi Kumar', 1);
INSERT INTO employees VALUES (2, 'Sneha Joshi', 2);
INSERT INTO employees VALUES (3, 'Karan Das', 2);
INSERT INTO employees VALUES (4, 'Pooja Nair', 6);

Note: Department IDs 3 (Finance) and 4 (Marketing) have no employees. Employee 4 has dept_id 6 which does not exist in departments.

Example: RIGHT JOIN

SELECT e.emp_name, d.dept_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.dept_id = d.dept_id;

Result:

emp_name     | dept_name
-------------+-----------
Ravi Kumar   | HR
Sneha Joshi  | IT
Karan Das    | IT
NULL         | Finance
NULL         | Marketing

Finance and Marketing have no employees, so emp_name is NULL — but both departments still appear. Employee 4 (dept_id 6) does not appear because dept_id 6 is not in the right table.

Finding Departments with No Employees

SELECT d.dept_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

Result:

dept_name
---------
Finance
Marketing

RIGHT JOIN vs LEFT JOIN

Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Most developers prefer LEFT JOIN for consistency.

-- These two queries return identical results:

SELECT e.emp_name, d.dept_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.dept_id = d.dept_id;

SELECT e.emp_name, d.dept_name
FROM departments AS d
LEFT JOIN employees AS e ON d.dept_id = e.dept_id;

RIGHT JOIN vs INNER JOIN Comparison

Key Points

• RIGHT JOIN returns all rows from the right table regardless of a match.
• Left table columns return NULL when no matching row is found.
• The right table is the one written after the RIGHT JOIN keyword.
• Adding WHERE left_table.column IS NULL finds right-side rows with no match.
• RIGHT JOIN can always be converted to a LEFT JOIN by swapping table order.