How to Use RIGHT JOIN in MySQL

Key Insights

• RIGHT JOIN returns all records from the right table and only matching records from the left table, filling unmatched left-side columns with NULL values
• Most developers prefer LEFT JOIN over RIGHT JOIN for readability—any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order
• Understanding the difference between filtering in the ON clause versus the WHERE clause is critical for getting correct results with RIGHT JOIN

Introduction to RIGHT JOIN

RIGHT JOIN is one of the four main join types in MySQL, alongside INNER JOIN, LEFT JOIN, and FULL OUTER JOIN (which MySQL doesn’t natively support). It returns every row from the right table in your query, regardless of whether a matching row exists in the left table. When no match exists, MySQL fills the left table’s columns with NULL values.

You’ll encounter RIGHT JOIN less frequently than its counterpart, LEFT JOIN. Most developers find LEFT JOIN more intuitive because we naturally read from left to right. However, understanding RIGHT JOIN matters for several reasons: you’ll encounter it in legacy codebases, it occasionally produces cleaner queries in specific scenarios, and knowing both directions deepens your understanding of how relational joins work.

The typical use case for RIGHT JOIN involves ensuring you see all records from a “primary” table while optionally pulling in related data from a “secondary” table. Think of scenarios like listing all departments whether or not they have employees, or showing all products regardless of whether they’ve been ordered.

RIGHT JOIN Syntax

The basic syntax follows this structure:

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

You can also write it as RIGHT OUTER JOIN—the OUTER keyword is optional and doesn’t change behavior:

SELECT columns
FROM left_table
RIGHT OUTER JOIN right_table
ON left_table.column = right_table.column;

The key elements are:

• left_table: The table that may have missing matches (columns become NULL when no match exists)
• right_table: The table that will have all its rows represented in the output
• ON clause: Specifies the join condition, typically matching foreign keys to primary keys

How RIGHT JOIN Works (Visual Explanation)

Let’s create a concrete example with two tables: employees and departments. The relationship is straightforward—each employee belongs to one department, but some departments might not have any employees assigned.

-- Create the tables
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Insert sample data
INSERT INTO departments (department_id, department_name) VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Human Resources'),
(4, 'Research');

INSERT INTO employees (employee_id, employee_name, department_id) VALUES
(101, 'Alice Chen', 1),
(102, 'Bob Smith', 1),
(103, 'Carol White', 2),
(104, 'David Brown', NULL);

Notice that the Research department (ID 4) has no employees, and David Brown has no department assignment. Now let’s run a RIGHT JOIN:

SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

The result:

+-------------+---------------+---------------+-----------------+
| employee_id | employee_name | department_id | department_name |
+-------------+---------------+---------------+-----------------+
|         101 | Alice Chen    |             1 | Engineering     |
|         102 | Bob Smith     |             1 | Engineering     |
|         103 | Carol White   |             2 | Marketing       |
|        NULL | NULL          |             3 | Human Resources |
|        NULL | NULL          |             4 | Research        |
+-------------+---------------+---------------+-----------------+

Every department appears in the results. Human Resources and Research have NULL values for employee columns because no employees belong to those departments. David Brown doesn’t appear because he has no department, and the RIGHT JOIN preserves right table rows, not left table rows.

RIGHT JOIN vs LEFT JOIN

Here’s the truth that experienced developers know: RIGHT JOIN and LEFT JOIN are functionally interchangeable. Any RIGHT JOIN query can be rewritten as a LEFT JOIN by swapping the table order. The previous query produces identical results to this LEFT JOIN:

-- RIGHT JOIN version
SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

-- Equivalent LEFT JOIN version
SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id;

Both queries return the same five rows with the same NULL values. The only difference is which table appears on which side of the JOIN keyword.

So why does RIGHT JOIN exist? Historically, some developers found it useful when building complex queries with multiple joins where reordering tables would be awkward. In practice, the overwhelming convention is to use LEFT JOIN. Code reviews often flag RIGHT JOIN usage as a readability concern.

My recommendation: default to LEFT JOIN. Use RIGHT JOIN only when it genuinely improves query clarity, which is rare.

Practical Use Cases

Despite the preference for LEFT JOIN, understanding RIGHT JOIN helps in several scenarios.

Finding unassigned or orphaned records:

-- Find departments with no employees assigned
SELECT 
    d.department_id,
    d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

Result:

+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             3 | Human Resources |
|             4 | Research        |
+---------------+-----------------+

Audit and reporting queries:

When generating reports, you often need complete lists from reference tables regardless of transactional data:

-- Monthly sales report showing all products, even those with no sales
SELECT 
    p.product_id,
    p.product_name,
    COALESCE(SUM(s.quantity), 0) AS total_sold,
    COALESCE(SUM(s.quantity * s.unit_price), 0) AS total_revenue
FROM sales s
RIGHT JOIN products p
ON s.product_id = p.product_id
AND s.sale_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY p.product_id, p.product_name
ORDER BY total_revenue DESC;

Working with legacy code:

Sometimes you inherit a codebase that uses RIGHT JOIN extensively. Rather than rewriting everything, understanding the existing patterns lets you maintain and extend the code safely.

Filtering with RIGHT JOIN (WHERE vs ON)

This distinction trips up many developers. Placing a filter condition in the ON clause versus the WHERE clause produces different results with outer joins.

Filter in ON clause:

SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
AND e.employee_name LIKE 'A%';

Result:

+-------------+---------------+---------------+-----------------+
| employee_id | employee_name | department_id | department_name |
+-------------+---------------+---------------+-----------------+
|         101 | Alice Chen    |             1 | Engineering     |
|        NULL | NULL          |             2 | Marketing       |
|        NULL | NULL          |             3 | Human Resources |
|        NULL | NULL          |             4 | Research        |
+-------------+---------------+---------------+-----------------+

All departments still appear. The filter restricts which employees can match, but unmatched departments still show up with NULL employee data.

Filter in WHERE clause:

SELECT 
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_name LIKE 'A%';

Result:

+-------------+---------------+---------------+-----------------+
| employee_id | employee_name | department_id | department_name |
+-------------+---------------+---------------+-----------------+
|         101 | Alice Chen    |             1 | Engineering     |
+-------------+---------------+---------------+-----------------+

Only one row returns. The WHERE clause filters after the join completes, eliminating rows where employee_name is NULL or doesn’t match the pattern.

The rule: Use ON clause filters when you want to restrict matching without eliminating right table rows. Use WHERE clause filters when you want to filter the final result set.

Best Practices and Common Pitfalls

Prefer LEFT JOIN for readability. This isn’t arbitrary—it’s a near-universal convention. When you use RIGHT JOIN, other developers will mentally convert it to LEFT JOIN anyway. Save them the effort.

Handle NULL values explicitly. When working with RIGHT JOIN results, columns from the left table may be NULL. Use COALESCE(), IFNULL(), or CASE expressions to handle these cases:

SELECT 
    COALESCE(e.employee_name, 'Unassigned') AS employee,
    d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

Index your join columns. Performance degrades quickly without proper indexes. Ensure both columns in your ON clause are indexed:

CREATE INDEX idx_employees_department ON employees(department_id);

Avoid mixing RIGHT JOIN and LEFT JOIN in the same query. This creates confusion and makes queries harder to reason about. Pick one direction and stick with it.

Test with edge cases. Always verify your query handles empty tables, NULL foreign keys, and tables with no matching records. These edge cases reveal logic errors that don’t surface with “happy path” test data.

Document unusual RIGHT JOIN usage. If you have a legitimate reason to use RIGHT JOIN, add a comment explaining why. Your future self and teammates will appreciate it.

RIGHT JOIN is a tool worth understanding, even if you rarely reach for it. Master the concept, recognize it in existing code, and default to LEFT JOIN in your own work. That approach serves most MySQL developers well.