RIGHT JOIN

Learning Focus

Use this lesson to understand RIGHT JOIN with practical syntax and examples.

Concept Overview

RIGHT JOIN (also written as RIGHT OUTER JOIN) keeps all rows from the right table, and matches rows from the left table when possible. When no match exists, the left-side columns become NULL.

PostgreSQL supports RIGHT JOIN, but many teams standardize on LEFT JOIN for readability because a right join can always be rewritten as a left join by swapping the table order.

Why is it important?

• Completeness from the right: some questions are naturally framed as “keep all rows from table B”
• Reconciliation: find “orphan” rows on one side (for example, orders with no customer)
• Readability choice: understanding equivalence to left joins helps you read and refactor queries safely

Where does it fit?

RIGHT JOIN is an outer join type alongside LEFT JOIN and FULL JOIN. In practice, you will see it less often than LEFT JOIN, but you should recognize it and know how to rewrite it.

---

Syntax & Rules

Core Syntax

SELECT select_list
FROM left_table l
RIGHT JOIN right_table r
  ON r.key = l.key;

Equivalent rewrite (same result, often preferred):

SELECT select_list
FROM right_table r
LEFT JOIN left_table l
  ON r.key = l.key;

Available Options / Parameters

Part	What it does	Notes
RIGHT [OUTER] JOIN	keeps all rows from the right table	OUTER is optional
ON	defines match condition	usually PK/FK equality
WHERE	filters after join	filters on left columns can remove NULL-extended rows
Rewrite as LEFT JOIN	swap table order	improves consistency in many codebases

Key Rules and Considerations

• Unmatched left-side columns become NULL.
• Filtering on left-table columns in WHERE can turn the query into an inner join behavior.
• If your schema enforces foreign keys, “orphans” (unmatched keys) should be rare; if they exist, it often indicates missing constraints or staged data.

---

Step-by-Step Examples

Example 1: Keep All Orders, Even if Customer Is Missing (Beginner)

This example intentionally omits a foreign key constraint to demonstrate what a right join can reveal.

CREATE TABLE customers (
  customer_id bigint PRIMARY KEY,
  full_name   text NOT NULL
);

CREATE TABLE orders (
  order_id    bigint PRIMARY KEY,
  customer_id bigint,
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO customers (customer_id, full_name) VALUES
  (1, 'Alice'),
  (2, 'Bob');

INSERT INTO orders (order_id, customer_id, amount) VALUES
  (101, 1, 120.00),
  (102, 3, 80.00),
  (103, NULL, 10.00);

SELECT o.order_id, o.customer_id, c.full_name, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Expected output:

 order_id | customer_id | full_name | amount
----------+-------------+-----------+--------
 101      | 1           | Alice     | 120.00
 102      | 3           |           | 80.00
 103      |             |           | 10.00
(3 rows)

Explanation:

• Orders 102 and 103 have no matching customer row, so the customer columns are NULL.

---

Example 2: Rewrite RIGHT JOIN as LEFT JOIN (Intermediate)

The following query returns the same result as Example 1.

SELECT o.order_id, o.customer_id, c.full_name, o.amount
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Expected output (same):

 order_id | customer_id | full_name | amount
----------+-------------+-----------+--------
 101      | 1           | Alice     | 120.00
 102      | 3           |           | 80.00
 103      |             |           | 10.00
(3 rows)

Explanation:

• The rewrite is often preferred because it reads as “start from orders, keep all orders”.

---

Example 3: Find Orphan Rows on the Left Side (Right Join Perspective) (Advanced)

To find orders with no customer, test the left table's key for NULL.

SELECT o.order_id, o.customer_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;

Expected output:

 order_id | customer_id | amount
----------+-------------+--------
 102      | 3           | 80.00
 103      |             | 10.00
(2 rows)

Explanation:

• c.customer_id IS NULL indicates “no customer matched this order”.

---

Example 4: Prefer FULL JOIN for Reconciliation (Advanced)

PostgreSQL supports FULL JOIN, which can be useful when you want to keep unmatched rows from both sides.

SELECT
  c.customer_id AS customer_id_from_customers,
  o.customer_id AS customer_id_from_orders,
  c.full_name,
  o.order_id
FROM customers c
FULL JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id NULLS LAST, c.customer_id;

Expected output (high-level):

 customer_id_from_customers | customer_id_from_orders | full_name | order_id
----------------------------+-------------------------+-----------+---------
 1                          | 1                       | Alice     | 101
 2                          |                         | Bob       |
                            | 3                       |           | 102
                            |                         |           | 103
(4 rows)

Explanation:

• Bob appears even though he has no orders.
• Orphan orders still appear.

---

Practical Use Cases

1) Data reconciliation (staging vs canonical)

SELECT s.external_id
FROM canonical_entities c
RIGHT JOIN staging_entities s ON s.external_id = c.external_id
WHERE c.external_id IS NULL;

2) Keep all event records even if dimension row is missing

SELECT e.event_id, u.user_id
FROM users u
RIGHT JOIN events e ON e.user_id = u.user_id;

3) Audit unmatched rows (orphans)

SELECT o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

4) Reporting: keep all right-side rows (rare)

SELECT a.account_id, p.plan_name
FROM accounts a
RIGHT JOIN plans p ON p.plan_id = a.plan_id;

5) Refactor to LEFT JOIN for consistency

-- Equivalent pattern
SELECT o.order_id, c.full_name
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;

---

Common Mistakes & Troubleshooting

1) Confusing which table is preserved

Wrong mental model:

• assuming RIGHT JOIN preserves the left table.

Fix:

• The preserved table is the one named by the join type: RIGHT JOIN preserves the right table.

---

2) Filtering left-table columns in WHERE

Wrong SQL:

SELECT o.order_id, c.full_name
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.full_name ILIKE 'a%';

Bad outcome:

• Orphan orders disappear because c.full_name is NULL for them.

Fix (keep the join outer by moving filter to ON):

SELECT o.order_id, c.full_name
FROM customers c
RIGHT JOIN orders o
  ON o.customer_id = c.customer_id
 AND c.full_name ILIKE 'a%';

---

3) Expecting orphan keys in FK-enforced schemas

Wrong assumption:

• Expecting orders without customers in a schema with a foreign key.

Bad outcome:

• The data cannot exist (insert would fail) unless constraints are missing/disabled.

Fix:

• Confirm schema constraints before interpreting results.

---

4) Forgetting RIGHT JOIN is a LEFT JOIN with swapped tables

Wrong approach:

• Maintaining mixed LEFT and RIGHT joins across a codebase.

Fix:

• Rewrite RIGHT JOINs into LEFT JOINs for consistency.

---

Debugging checklist:

1. Identify which table must be fully preserved.
2. Rewrite as a LEFT JOIN if it makes the query easier to read.
3. Use WHERE left.pk IS NULL to find unmatched rows from the left table.
4. Check foreign keys and nullability on join columns.
5. Add a small ORDER BY ... LIMIT 20 preview when exploring data.

---

Best Practices

• ✅ Prefer LEFT JOIN by swapping table order for consistency. ❌ Avoid mixing join directions across a codebase without a reason.
• ✅ Use right joins primarily for reconciliation-style queries. ❌ Avoid using RIGHT JOIN when starting from the right table would be clearer.
• ✅ Detect orphans with WHERE left.pk IS NULL. ❌ Avoid checking nullable non-key columns to detect missing matches.
• ✅ Be explicit about filters: put preserved-side filters in WHERE, and match-side filters in ON. ❌ Avoid filters that accidentally remove NULL-extended rows.
• ✅ Consider FULL JOIN in PostgreSQL when both sides need preserving. ❌ Avoid emulating full joins with unions when PostgreSQL already supports it.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_customers (
  customer_id bigint PRIMARY KEY,
  full_name   text NOT NULL
);

CREATE TABLE practice_orders (
  order_id    bigint PRIMARY KEY,
  customer_id bigint,
  amount      numeric(12,2) NOT NULL
);

INSERT INTO practice_customers (customer_id, full_name) VALUES
  (1, 'Alice'),
  (2, 'Bob');

INSERT INTO practice_orders (order_id, customer_id, amount) VALUES
  (101, 1, 12.00),
  (102, 3, 8.00);

Exercise 1 (Easy): Basic RIGHT JOIN

Task: Return all orders with the customer name if it exists.

-- Your SQL here

Solution:

SELECT o.order_id, o.customer_id, c.full_name
FROM practice_customers c
RIGHT JOIN practice_orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

---

Exercise 2 (Medium): Rewrite RIGHT JOIN as LEFT JOIN

Task: Rewrite the query from Exercise 1 using a LEFT JOIN.

-- Your SQL here

Solution:

SELECT o.order_id, o.customer_id, c.full_name
FROM practice_orders o
LEFT JOIN practice_customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

---

Exercise 3 (Advanced): Find orphan orders

Task: Return only orders that do not have a matching customer.

-- Your SQL here

Solution:

SELECT o.order_id, o.customer_id
FROM practice_customers c
RIGHT JOIN practice_orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;

---

Connection to Other Concepts

Concept	Why it matters
LEFT JOIN	RIGHT JOIN can be rewritten as LEFT JOIN by swapping tables
NULL Values	unmatched rows become NULL on the non-preserved side
FULL JOIN	preserves both sides for reconciliation in PostgreSQL
INNER JOIN	excludes orphans entirely
Aliases (AS)	make join direction and column origins clearer

---

Visual Learning Diagram

flowchart TD
  A[LEFT Table] --> B[RIGHT JOIN]
  C[RIGHT Table (Preserved)] --> B
  B --> D[All Right Rows Kept]
  B --> E[Unmatched Left = NULL]
  B --> F[Rewrite]
  F --> G[Swap Tables]
  G --> H[LEFT JOIN]
  B --> I[Reconciliation]
  I --> J[FULL JOIN]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I,J allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Misidentifying the preserved side	wrong result interpretation	remember RIGHT JOIN keeps right table rows
Filtering on left columns in WHERE	orphan right rows disappear	move those filters into ON if needed
Using RIGHT JOIN where LEFT JOIN reads clearer	harder to maintain	rewrite as LEFT JOIN by swapping tables
Assuming orphans exist with FK constraints	confusion	check schema constraints and nullability
Using nullable non-keys to detect missing matches	false positives	check the left primary key for NULL

---

Quick Reference

SELECT * FROM a RIGHT JOIN b ON b.a_id = a.id;
SELECT * FROM b LEFT JOIN a ON b.a_id = a.id; -- equivalent
SELECT * FROM a RIGHT JOIN b ON b.a_id = a.id WHERE a.id IS NULL; -- orphans
SELECT * FROM a FULL JOIN b ON b.a_id = a.id; -- PostgreSQL supports
SELECT * FROM b LEFT JOIN a ON b.a_id = a.id WHERE a.id IS NULL;

What's Next

• Previous: LEFT JOIN - Review the previous lesson to reinforce context.
• Next: CROSS JOIN - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.