Suggest an edit

Improve this article

Refine the answer for “How does right join work?”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

**SQL RIGHT JOIN** returns every row from the right table and only the matching rows from the left table. Where there is no match, the left-table columns come back as NULL.

## Theory

### TL;DR

- Analogy: every order on the list gets printed; customer info fills in only if a match exists.
- Main difference: RIGHT JOIN keeps all right-table rows. INNER JOIN keeps only matches. LEFT JOIN keeps all left-table rows.
- Decision rule: right table = source of truth (all shipments must appear) → RIGHT JOIN. Otherwise → LEFT JOIN or INNER JOIN.
- `A RIGHT JOIN B` is identical to `B LEFT JOIN A`.

### Quick example

```sql
CREATE TABLE clients (id INT, name VARCHAR(50));
INSERT INTO clients VALUES (1, 'Alice'), (2, 'Bob');

CREATE TABLE orders (order_id INT, client_id INT, item VARCHAR(50));
INSERT INTO orders VALUES (101, 1, 'Laptop'), (102, NULL, 'Mouse'), (103, 3, 'Keyboard');

-- All orders appear; client name fills in where matched
SELECT c.name, o.item
FROM clients c
RIGHT JOIN orders o ON c.id = o.client_id;
```

Output:

```
name  | item
Alice | Laptop
NULL  | Mouse
NULL  | Keyboard
```

All 3 orders show up. Orders 102 and 103 have no matching client, so `name` is NULL.

### Key difference

RIGHT JOIN flips LEFT JOIN. LEFT keeps every row from the left table and fills NULLs on the right side when there is no match. RIGHT does the opposite: every row from the right table survives, and left-side columns go NULL without a match. In every codebase I have reviewed, RIGHT JOIN almost always gets rewritten as LEFT JOIN during code review, not because it is wrong, but because putting the driving table first reads more naturally. The query optimizer treats both the same way.

### When to use

- All rows from the right table must appear → RIGHT JOIN (all shipments, all log events).
- All rows from the left table must appear → LEFT JOIN.
- Only matching rows matter → INNER JOIN.
- Everything from both sides → FULL OUTER JOIN.

### Comparison table

| Join type | Left table rows | Right table rows | NULLs placed in | Typical use case |
|---|---|---|---|---|
| INNER JOIN | Matches only | Matches only | None | Customers with orders |
| LEFT JOIN | All | Matches only | Right columns | All customers + their orders |
| **RIGHT JOIN** | Matches only | **All** | **Left columns** | **All orders + optional customer info** |
| FULL OUTER JOIN | All | All | Both sides | Full audit, complete merge |

### Common mistakes

**Mistake: filtering NULLs with WHERE after RIGHT JOIN**

```sql
-- Wrong: this turns RIGHT JOIN into INNER JOIN
SELECT * FROM clients c
RIGHT JOIN orders o ON c.id = o.client_id
WHERE c.name IS NOT NULL;
```

WHERE runs after the join. Adding `WHERE c.name IS NOT NULL` drops every unmatched right-table row. You end up with the same result as INNER JOIN. To filter the left table while keeping all right-table rows, move the condition into the ON clause:

```sql
-- Correct: filter inside ON, right-table rows are preserved
SELECT * FROM clients c
RIGHT JOIN orders o ON c.id = o.client_id AND c.name IS NOT NULL;
```

**Mistake: expecting NULL join keys to disappear**

If `order.client_id` is NULL, that row still appears in the result. NULL never equals NULL, so there is no match, and `c.name` will be NULL. The row is not dropped.

**Mistake: mixing RIGHT JOIN and LEFT JOIN in the same query**

`FROM clients RIGHT JOIN orders` is identical to `FROM orders LEFT JOIN clients`. Most SQL style guides prefer LEFT JOIN so the driving table stays first. Mixing both types in one query makes the logic hard to follow.

### Real-world usage

- E-commerce: all shipments with optional customer profiles (`shipments RIGHT JOIN customers`).
- Logging: all events with optional user session data, so no log entry is lost.
- HR systems: all employee records with optional department assignments.
- Analytics: all ad impressions with optional click data.

### Follow-up questions

**Q:** What happens if the join column contains NULL on the right-table side?
**A:** The right-table row still appears in the result. NULL does not equal NULL, so there is no match, and the left-side columns come back as NULL.

**Q:** How do you rewrite a RIGHT JOIN as a LEFT JOIN?
**A:** Swap the table order and change the join type. `FROM A RIGHT JOIN B ON A.id = B.id` becomes `FROM B LEFT JOIN A ON B.id = A.id`.

**Q:** Why does a WHERE filter on the left table break the RIGHT JOIN behavior?
**A:** WHERE runs after the join. `WHERE left.col IS NOT NULL` removes all unmatched right-table rows, which gives the same result as INNER JOIN. Use the ON clause to filter the left table if you still need all right-table rows.

**Q:** In a chain like `A RIGHT JOIN B RIGHT JOIN C`, how do NULLs propagate?
**A:** Each link resolves left to right. If A has no match for B, A's columns become NULL. That NULL then participates in the next join with C. Draw it as two separate joins to trace how NULLs flow through the chain.

## Examples

### Basic: all orders with optional client names

```sql
CREATE TABLE clients (id INT, name VARCHAR(50));
INSERT INTO clients VALUES (1, 'Alice'), (2, 'Bob');

CREATE TABLE orders (order_id INT, client_id INT, item VARCHAR(50));
INSERT INTO orders VALUES (101, 1, 'Laptop'), (102, NULL, 'Mouse'), (103, 3, 'Keyboard');

Orders 102 and 103 have no matching record in `clients`. RIGHT JOIN keeps them in the result regardless.

### Intermediate: e-commerce shipment report

```sql
CREATE TABLE customers (cust_id INT, email VARCHAR(100));
INSERT INTO customers VALUES (1, 'alice@shop.com'), (2, 'bob@shop.com');

CREATE TABLE shipments (ship_id INT, cust_id INT, status VARCHAR(20));
INSERT INTO shipments VALUES
  (5001, 1, 'Delivered'),
  (5002, NULL, 'Pending'),
  (5003, 2, 'Shipped'),
  (5004, 3, 'Cancelled');

All 4 shipments appear in the logistics report. Shipments 5002 and 5004 have no registered customer, but the business still needs to track them. That is exactly what RIGHT JOIN is for.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts1124 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.