RIGHT JOIN

---

title: “RIGHT JOIN” part: 1 topic_number: 17 slug: “right-join” difficulty: “Beginner” prerequisites: “left-join” —

RIGHT JOIN

What Is It?

RIGHT JOIN returns ALL rows from the right table, and matching rows from the left table. If there’s no match, the left table’s columns show as NULL. It’s exactly like LEFT JOIN, but reversed.

Real-world analogy: If LEFT JOIN is “all students with optional clubs”, RIGHT JOIN is “all clubs with optional students”.

Honest truth: Most developers rarely use RIGHT JOIN. You can always rewrite it as a LEFT JOIN by swapping the table order. But it’s good to understand it!

---

Syntax Breakdown

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

What this means:

• ALL rows from table2 (right table) appear
• Matching rows from table1 (left table) are included
• If no match, table1’s columns show NULL

---

Basic Example

Show ALL departments and their employees (including departments with no employees):

SELECT 
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

What this does:

• All departments appear (departments is the right table)
• Employees are included if they match
• Departments with no employees show NULL for employee columns

Expected output (partial):

first_name	last_name	department_name
Alice	Johnson	Engineering
Bob	Smith	Engineering
…	…	…
NULL	NULL	Operations

If Operations has no employees, it still appears with NULL for first_name and last_name.

---

Going Deeper

RIGHT JOIN vs LEFT JOIN (They’re Equivalent!)

These two queries return the SAME results:

Using RIGHT JOIN:

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

Using LEFT JOIN (rewritten):

SELECT e.first_name, d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

Same output! Just swap the table order and change RIGHT to LEFT.

This is why RIGHT JOIN is rare: You can always use LEFT JOIN instead by reordering tables. Most developers find LEFT JOIN more intuitive.

Pause and Predict: If RIGHT JOIN can always be rewritten as LEFT JOIN, why does it exist?

Answer

**Historical reasons and symmetry.** SQL was designed to have symmetric operations (LEFT and RIGHT). In theory, RIGHT JOIN lets you add optional data to an existing query without rewriting the FROM clause. **In practice:** Most style guides recommend using only LEFT JOIN for consistency. It makes code more predictable and easier to read.

Finding Unmatched Rows with RIGHT JOIN

Find departments with NO employees:

SELECT d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

What this does:

1. RIGHT JOIN ensures all departments appear
2. WHERE filters for departments where employee_id IS NULL (no match)

Expected output:

department_name
Operations

(If Operations has no employees assigned)

Same query with LEFT JOIN:

SELECT d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

Identical result! This is why LEFT JOIN is preferred — more intuitive.

---

Watch Out — Common Mistakes

Mistake #1: Confusing Which Table is “Complete”

--  CONFUSING — Which table has all rows?
SELECT *
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

Answer: Departments (right table) has all rows. Employees might have NULLs.

With LEFT JOIN (clearer):

-- • CLEARER — Departments is on the left, obviously complete
SELECT *
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

Lesson: LEFT JOIN with the “complete” table first is more readable.

Mistake #2: Mixing LEFT and RIGHT JOINs

--  CONFUSING
SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
RIGHT JOIN table3 t3 ON t2.id = t3.id;

What happens: This is valid, but which tables are complete? It’s hard to reason about.

Better approach: Stick to LEFT JOIN throughout, reorder tables as needed:

-- • CLEARER
SELECT *
FROM table3 t3
LEFT JOIN table2 t2 ON t3.id = t2.id
LEFT JOIN table1 t1 ON t2.id = t1.id;

Consistency makes queries easier to understand.

Mistake #3: Thinking RIGHT JOIN Is More Powerful

RIGHT JOIN and LEFT JOIN have exactly the same power. There’s nothing you can do with RIGHT JOIN that you can’t do with LEFT JOIN.

Don’t overthink it: If you understand LEFT JOIN, you understand RIGHT JOIN. Just swap the tables.

---

Edge Case Spotlight

When RIGHT JOIN Makes Sense

There’s ONE scenario where RIGHT JOIN can be clearer:

You have a complex query with many LEFT JOINs, and you need to add one more optional table:

SELECT *
FROM base_table b
LEFT JOIN table2 t2 ON b.id = t2.id
LEFT JOIN table3 t3 ON b.id = t3.id
LEFT JOIN table4 t4 ON b.id = t4.id
-- Now you need to add table5, but you want ALL of table5
-- RIGHT JOIN lets you do this without restructuring
RIGHT JOIN table5 t5 ON b.id = t5.id;  -- All of table5 appears

But even this is confusing! Most developers would restructure with LEFT JOIN.

Bottom line: RIGHT JOIN exists for completeness, but LEFT JOIN is almost always preferred.

---

Try This

Exercise 1 (Guided)

Show all projects and count how many employees are assigned to each using RIGHT JOIN. (Then rewrite it using LEFT JOIN to see they’re equivalent.)

Hint

RIGHT JOIN from employee_projects to projects, GROUP BY project_name, COUNT employee_id.

Exercise 2 (Independent)

Find all projects that have NO employees assigned, using RIGHT JOIN. Show only project_name.

Exercise 3 (Challenge)

Explain why this query might confuse your teammates:

SELECT e.first_name, d.department_name, p.project_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
RIGHT JOIN employee_projects ep ON e.employee_id = ep.employee_id
LEFT JOIN projects p ON ep.project_id = p.project_id;

Then rewrite it using only LEFT JOINs.

---

Answer Key

Exercise 1 Answer

**Using RIGHT JOIN:** ```sql SELECT p.project_name, COUNT(ep.employee_id) AS employee_count FROM employee_projects ep RIGHT JOIN projects p ON ep.project_id = p.project_id GROUP BY p.project_name; ``` **Using LEFT JOIN (equivalent):** ```sql SELECT p.project_name, COUNT(ep.employee_id) AS employee_count FROM projects p LEFT JOIN employee_projects ep ON p.project_id = ep.project_id GROUP BY p.project_name; ``` **Both produce the same output!** All projects appear with their employee counts. The LEFT JOIN version is clearer — projects is the "main" table, obviously complete.

Exercise 2 Answer

```sql SELECT p.project_name FROM employee_projects ep RIGHT JOIN projects p ON ep.project_id = p.project_id WHERE ep.employee_id IS NULL; ``` **Clearer with LEFT JOIN:** ```sql SELECT p.project_name FROM projects p LEFT JOIN employee_projects ep ON p.project_id = ep.project_id WHERE ep.employee_id IS NULL; ``` **Expected output:** Projects with no employee assignments (if any exist in the data).

Exercise 3 Answer

**Why it's confusing:** - Starts with employees (LEFT), optionally adds departments (LEFT) - Then RIGHT JOIN to employee_projects — now employee_projects is complete, not employees! - The "base" table changed mid-query - Which rows are guaranteed to appear? Hard to tell at a glance. **Rewritten with only LEFT JOIN:** ```sql SELECT e.first_name, d.department_name, p.project_name FROM employee_projects ep LEFT JOIN employees e ON ep.employee_id = e.employee_id LEFT JOIN departments d ON e.department_id = d.department_id LEFT JOIN projects p ON ep.project_id = p.project_id; ``` **Now it's clear:** employee_projects is the base table. All assignments appear, with optional employee, department, and project details. **Lesson:** Mixing LEFT and RIGHT JOINs makes queries hard to reason about. Pick one style (LEFT JOIN) and stick with it.

---

Quick Recap

• RIGHT JOIN returns ALL rows from the right table
• LEFT table columns show NULL if there’s no match
• RIGHT JOIN and LEFT JOIN are equivalent (just swap table order)
• Most developers use only LEFT JOIN for consistency
• RIGHT JOIN exists for completeness, but rarely improves readability
• Stick to LEFT JOIN in your code for clarity

---

---

Two More JOIN Types You Need to Know

The CodingHorror article in your Study Resources covers these — they come up in interviews and are useful in specific situations.

FULL OUTER JOIN (MySQL workaround)

What it is: Returns ALL rows from BOTH tables. Rows with no match show NULL on the missing side.

MySQL doesn’t have a native FULL OUTER JOIN keyword. You simulate it with UNION:

-- All employees + all departments, even unmatched ones
SELECT 
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id

UNION

SELECT 
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

Expected output:

• All employees appear (even those with no department)
• All departments appear (even those with no employees)
• NULL fills the gap on the unmatched side

When to use it: Auditing data completeness — “show me everything, flag what’s unmatched.”

---

CROSS JOIN

What it is: Returns every combination of every row from both tables. No ON clause — no matching condition.

-- Pair every employee with every department
SELECT 
    e.first_name,
    d.department_name
FROM employees e
CROSS JOIN departments d
ORDER BY e.first_name, d.department_name;

Result size: rows_in_table1 × rows_in_table2. With 20 employees and 10 departments: 200 rows.

This is the “cartesian product” — dangerous on large tables but useful for:

• Generating all combinations (e.g., schedule slots × time slots)
• Building test data
• Finding missing combinations (paired with LEFT JOIN)

Practical example — find all possible employee-project pairings that DON’T exist yet:

SELECT 
    e.first_name,
    p.project_name
FROM employees e
CROSS JOIN projects p
WHERE NOT EXISTS (
    SELECT 1 FROM employee_projects ep
    WHERE ep.employee_id = e.employee_id
      AND ep.project_id = p.project_id
)
ORDER BY e.first_name;

Never accidentally run CROSS JOIN on large tables — 1000 rows × 1000 rows = 1,000,000 rows.

---

Join Types at a Glance

Join Type	What it returns	MySQL syntax
INNER JOIN	Only matching rows	INNER JOIN ... ON ...
LEFT JOIN	All left rows + matches	LEFT JOIN ... ON ...
RIGHT JOIN	All right rows + matches	RIGHT JOIN ... ON ...
FULL OUTER JOIN	All rows from both	LEFT JOIN ... UNION RIGHT JOIN ...
CROSS JOIN	All combinations	CROSS JOIN (no ON)

---

Up Next

Next topic: DISTINCT → part1_18_distinct.md

You’ve now covered all JOIN types! Next, you’ll learn how to eliminate duplicate rows with DISTINCT.