How to Use RIGHT JOIN in SQL

Use RIGHT JOIN when every row from the right table must stay in the result, even if no matching row exists in the left table. This is useful for optional reverse relationships like all departments with employees, all categories with products, or all campaigns with clicks.

What you’ll build or solve

You’ll learn how to use RIGHT JOIN in SQL to keep all right-table rows while adding matching left-table data when available. You’ll also know when a LEFT JOIN rewrite may read more clearly.

When this approach works best

This approach is the right choice when the second table in the query is the primary one that must stay complete.

Common real-world scenarios include:

• All departments with employees
• All categories with products
• All campaigns with conversions
• All courses with students
• All tags with posts

This is a bad idea when your SQL team consistently standardizes on LEFT JOIN for readability. In that case, swapping table order may be clearer.

Prerequisites

You only need:

• Two related SQL tables
• Basic SELECT and join key knowledge
• A shared relational key

Step-by-step instructions

Step 1: Keep every right-table row with RIGHT JOIN

Start with the left table in FROM, then keep the right table complete.

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

This keeps every department.

Departments without employees still appear, and employee columns become NULL.

This is logically similar to flipping the table order and using LEFT JOIN.

SELECT departments.department_name, employees.name
FROM departments
LEFT JOIN employees
ON departments.department_id = employees.department_id;

What to look for:

• Every right-table row stays visible
• Missing left-table values become NULL
• Equivalent to swapped LEFT JOIN
• Join on shared keys
• Pick the clearest reading direction

Examples you can copy

All categories with products

SELECT products.name, categories.category_name
FROM products
RIGHT JOIN categories
ON products.category_id = categories.category_id;

All campaigns with clicks

SELECT clicks.click_id, campaigns.name
FROM clicks
RIGHT JOIN campaigns
ON clicks.campaign_id = campaigns.campaign_id;

All courses with students

SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses
ON students.course_id = courses.course_id;

Common mistakes and how to fix them

Mistake 1: Using WHERE filters that remove right-only rows

What the reader might do:

WHERE employees.name IS NOT NULL

Why it breaks: this removes unmatched right-table rows and defeats the RIGHT JOIN.

Corrected approach:

Move optional filters into the join condition when needed.

Mistake 2: Joining on the wrong relationship key

What the reader might do:

ON employees.email = departments.department_id

Why it breaks: unrelated columns create invalid matches.

Corrected approach:

Use the actual foreign key relationship.

Mistake 3: Using RIGHT JOIN when LEFT JOIN would be clearer

What the reader might do:

FROM clicks
RIGHT JOIN campaigns

Why it breaks: the intent may be harder to scan.

Corrected approach:

Swap table order and use LEFT JOIN.

Troubleshooting

If right-table rows disappear, inspect WHERE filters.

If NULL left values appear, those are unmatched rows.

If the query reads backward, rewrite it as LEFT JOIN.

If the matches look wrong, verify the shared key.

Quick recap

• Use RIGHT JOIN to keep all right rows
• Missing left values become NULL
• Equivalent to swapped LEFT JOIN
• Join on meaningful keys
• Prefer the clearest reading direction