Right Join: Mirroring Left Join Reasoning from the Opposite Side

What a RIGHT JOIN Guarantees

A RIGHT JOIN returns all rows from the table on the right side of the join clause, plus any matching rows from the left table. When there is no match, the columns from the left table come back as NULL.

In other words, a RIGHT JOIN is the “preserve the right table” version of an outer join. If you already know how to reason about preserving one side of a join, RIGHT JOIN simply flips which side is protected: the right-hand table is the one whose rows must appear in the result.

Core idea in one sentence

RIGHT JOIN keeps every row from the right table, even if the left table has no matching key.

Syntax and a Quick Translation Trick

Typical syntax:

SELECT  columns
FROM    left_table AS L
RIGHT JOIN right_table AS R
       ON L.key = R.key;

A practical trick: if you ever feel unsure about RIGHT JOIN, you can rewrite it as a LEFT JOIN by swapping table positions. This is not a new join type; it is the same logic expressed from the opposite side.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

-- RIGHT JOIN form
SELECT  ...
FROM    L
RIGHT JOIN R
       ON L.key = R.key;

-- Equivalent LEFT JOIN form (swap tables)
SELECT  ...
FROM    R
LEFT JOIN L
       ON L.key = R.key;

This equivalence is extremely useful for debugging and for teams that prefer to standardize on LEFT JOIN for readability. The result set (including NULL placement) is the same, assuming you keep the selected columns consistent.

When RIGHT JOIN Is the Most Natural Choice

RIGHT JOIN is most natural when the “must-keep” dataset is already written on the right side due to query structure, readability, or because you are joining multiple tables and the right table is the one you conceptually treat as the anchor for the current step.

Common scenarios:

• Auditing completeness: “Show me all items in table B, and any corresponding info from table A if it exists.”
• Reverse perspective reporting: You start with a detail table (on the right) and optionally attach metadata from a lookup table (on the left), but you want to keep all details.
• Legacy SQL or tools: Some older codebases or auto-generated SQL may produce RIGHT JOINs; you need to read and validate them confidently.

Practical Example Setup

Imagine you have employees and departments. Some departments may have no employees yet (new departments), and some employees may have a missing or invalid department assignment. If your goal is to list all departments and show employees when they exist, the preserved side should be departments.

We will intentionally place departments on the right to demonstrate RIGHT JOIN reasoning.

-- employees
-- emp_id | emp_name | dept_id
-- 1      | Ava      | 10
-- 2      | Ben      | 10
-- 3      | Cy       | 20
-- 4      | Dia      | NULL
-- 5      | Eli      | 99

-- departments
-- dept_id | dept_name
-- 10      | Sales
-- 20      | Support
-- 30      | R&D

Step-by-Step: Reading a RIGHT JOIN Result

Step 1: Identify the preserved table

In a RIGHT JOIN, the preserved table is the one on the right side of RIGHT JOIN. Here, that is departments. So you should expect:

• Every department row appears at least once in the output.
• If a department has multiple employees, it appears multiple times (one per matching employee).
• If a department has zero employees, it still appears once with employee columns as NULL.

Step 2: Identify the join key and match condition

The join condition is employees.dept_id = departments.dept_id. Matches occur where an employee’s dept_id equals a department’s dept_id.

Step 3: Predict unmatched rows on the preserved side

Department 30 (R&D) has no employees in the sample. Because departments are preserved, you will still see a row for R&D with NULLs for employee fields.

Step 4: Write the query

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

Step 5: Interpret the output shape

You should expect:

• Sales (10): Ava and Ben → two rows with dept_name = Sales
• Support (20): Cy → one row with dept_name = Support
• R&D (30): no employees → one row with emp_id and emp_name as NULL

Employees with dept_id NULL or dept_id = 99 do not match any department row, and because employees are on the non-preserved side, those employees do not appear in the result.

Filtering Without Accidentally Breaking the Outer Join

A common mistake with outer joins is applying filters in the WHERE clause that unintentionally remove the NULL-extended rows (the rows that exist only because the preserved table is kept). With RIGHT JOIN, the NULL-extended columns belong to the left table.

Example: Keep all departments, but only show employees with a certain name pattern

If you do this:

SELECT
  e.emp_id,
  e.emp_name,
  d.dept_name
FROM employees AS e
RIGHT JOIN departments AS d
  ON e.dept_id = d.dept_id
WHERE e.emp_name LIKE 'A%';

you will remove the departments that have no employees matching the filter, including departments with no employees at all, because e.emp_name is NULL for those rows and NULL LIKE 'A%' is not true.

To keep all departments while filtering which employees attach, move the filter into the ON clause:

SELECT
  e.emp_id,
  e.emp_name,
  d.dept_name
FROM employees AS e
RIGHT JOIN departments AS d
  ON e.dept_id = d.dept_id
 AND e.emp_name LIKE 'A%'
ORDER BY d.dept_name;

Now every department still appears, but only employees whose names start with A will be joined; other departments will show NULL employee columns.

RIGHT JOIN as a Completeness Check (“Show Me What’s Missing”)

RIGHT JOIN is often used to find records in the right table that have no match in the left table. This is a “missing relationship” query.

Example: Departments with no employees

Because departments are preserved, departments without employees will show NULLs for employee columns. You can filter for those NULLs to find “unmatched on the left” cases.

SELECT
  d.dept_id,
  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
ORDER BY d.dept_id;

This returns R&D (30) in our sample. The key idea: you filter on a column from the non-preserved side (left table) being NULL, which indicates “no match was found.”

Be careful to choose a column that is guaranteed non-NULL in real matched rows (often a primary key like emp_id).

RIGHT JOIN with Aggregation: Counting Matches While Preserving the Right Table

RIGHT JOIN is also useful when you want an aggregate per right-table row, including zeros. For example: count employees per department, but include departments with zero employees.

SELECT
  d.dept_id,
  d.dept_name,
  COUNT(e.emp_id) AS employee_count
FROM employees AS e
RIGHT JOIN departments AS d
  ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

Because COUNT(column) ignores NULLs, departments with no employees will produce employee_count = 0, which is typically what you want for completeness reporting.

If you used COUNT(*) instead, the NULL-extended row for a department with no employees would still count as 1, which is misleading. So for outer joins, prefer COUNT(non_nullable_key_from_the_optional_side).

Chaining Joins: Keeping the “Right-Preserved” Perspective Straight

RIGHT JOIN can become confusing when you chain multiple joins, because “right” is local to each join operation, and parentheses (explicit or implied by the optimizer) can affect how you reason about intermediate results.

A practical approach is to keep each join step readable and verify which table is preserved at each step. If you have multiple joins and your goal is to preserve a particular table, consider rewriting the query so that preserved table is consistently on the left using LEFT JOINs. However, if you keep RIGHT JOIN, be explicit about which side is preserved in each join.

Example: Preserve departments, attach employees, then attach employee badges

Suppose badges is optional per employee (some employees have no badge yet). If you start with employees and RIGHT JOIN departments, then join badges, you must decide whether badges are optional relative to employees or relative to departments.

-- badges
-- emp_id | badge_id
-- 1      | B-100
-- 3      | B-200

One readable pattern is to first create the department-preserved set, then LEFT JOIN badges (optional) to employees:

SELECT
  d.dept_name,
  e.emp_name,
  b.badge_id
FROM employees AS e
RIGHT JOIN departments AS d
  ON e.dept_id = d.dept_id
LEFT JOIN badges AS b
  ON b.emp_id = e.emp_id
ORDER BY d.dept_name, e.emp_name;

Here’s how to reason about it:

• The RIGHT JOIN ensures every department appears (even if e.* is NULL).
• The subsequent LEFT JOIN to badges attaches badge info when e.emp_id exists.
• For departments with no employees, e.emp_id is NULL, so the badge join will also be NULL (which is consistent).

If you instead placed badges on the right with another RIGHT JOIN, you would be preserving badges, which is a different question (you would then keep all badges even if the employee record is missing).

RIGHT JOIN and Column Selection: Avoiding Ambiguity

When both tables share column names (like dept_id), always qualify columns with table aliases. In RIGHT JOIN queries, this matters even more because you will often filter for NULLs on the left table to find unmatched right-table rows.

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

Also be mindful of selecting * in outer joins. NULL-extended columns can be surprising in downstream processing, and duplicate column names can cause confusion in client code.

RIGHT JOIN vs Rewriting to LEFT JOIN: Practical Guidance

Even though RIGHT JOIN is valid SQL, many teams prefer to standardize on LEFT JOIN for consistency. You can still understand RIGHT JOIN fluently while choosing a style that reduces cognitive load.

Rewrite pattern

If you see:

FROM A
RIGHT JOIN B
  ON A.k = B.k

you can rewrite as:

FROM B
LEFT JOIN A
  ON A.k = B.k

Then re-check:

• Any WHERE filters referencing the optional side (now A) should be moved into ON if you still want to preserve B.
• Any ORDER BY or SELECT expressions referencing table aliases must be updated.

Common Pitfalls Specific to RIGHT JOIN Reasoning

1) Filtering on the left table in WHERE

Because left-table columns become NULL when there is no match, a WHERE predicate on the left table often turns the RIGHT JOIN into an effective inner join for those rows. If you are preserving the right table, consider whether the filter belongs in ON instead.

2) Assuming unmatched rows come from the left table

With RIGHT JOIN, the unmatched rows you keep are from the right table. If you are trying to find “left table rows with no match,” RIGHT JOIN is the wrong direction unless you swap tables or change the preserved side.

3) Using COUNT(*) in outer-join aggregates

For “count of matches,” use COUNT(left_table_key) (the optional side) rather than COUNT(*), otherwise you may count the NULL-extended placeholder row.

4) Confusion in multi-join queries

RIGHT JOIN can be harder to scan in long queries because the preserved table appears after the keyword. If readability suffers, rewrite to LEFT JOIN by swapping table order.