Suggest an edit

Improve this article

Refine the answer for “How does left 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)

**LEFT JOIN** returns every row from the left table, plus matching rows from the right table. Where no match exists, the right-side columns come back as NULL.

## Theory

### TL;DR
- Think of the left table as a guest list. Everyone on it appears in the result. The right table is the food order. No order means an empty plate (NULL).
- Every row from the left table appears at least once. Non-matching right rows get dropped entirely.
- Use LEFT JOIN when you need all left records regardless of whether a match exists on the right side.

### Quick example

```sql
-- clients (left table)
-- client_id | name
-- 1         | Alice
-- 2         | Bob
-- 3         | Carol

SELECT c.name, p.item
FROM clients c
LEFT JOIN purchases p ON c.client_id = p.client_id;

-- Result:
-- Alice | Laptop
-- Alice | Mouse
-- Bob   | NULL
-- Carol | NULL
```

All three clients appear. Bob and Carol show NULL for item because they have no matching rows in purchases.

### Key difference from INNER JOIN

INNER JOIN only returns rows where both tables have a match. Bob and Carol would disappear from the result completely. LEFT JOIN keeps them and fills the right-side columns with NULL. That single behavior is the whole reason LEFT JOIN exists.

### When to use

- All customers plus their orders, including those with zero orders: LEFT JOIN.
- Only customers who placed at least one order: INNER JOIN.
- Full merge where both sides may have missing data: FULL OUTER JOIN.
- Right table prioritized: RIGHT JOIN, but most teams just swap the table order and write LEFT JOIN instead.

### How it works internally

The engine scans every row in the left table. For each left row, it looks for matching rows in the right table using the ON condition. If it finds matches, it outputs one combined row per match. If it finds nothing, it outputs the left row once with NULLs in all right columns.

Indexes on the join key (like `client_id` in purchases) make this fast. Without an index on a large table, the engine compares every left row against every right row. That is the most common reason LEFT JOINs kill performance in production.

### Common mistakes

**Mistake 1: WHERE on a right-table column converts LEFT JOIN into INNER JOIN.**

```sql
-- BUG: Bob and Carol disappear
SELECT c.name, p.item
FROM clients c
LEFT JOIN purchases p ON c.client_id = p.client_id
WHERE p.item IS NOT NULL;

-- FIX: move the condition into ON
SELECT c.name, p.item
FROM clients c
LEFT JOIN purchases p
  ON c.client_id = p.client_id AND p.item IS NOT NULL;
```

WHERE runs after the join and removes the NULL rows you wanted to keep. Putting the condition in ON changes when the filter applies, so unmatched left rows still make it through.

**Mistake 2: Multiple right-side matches multiply row count.**

If one client has 50 orders, you get 50 rows for that client. Correct behavior, but it surprises people who expect one row per client. Use `COUNT` with `GROUP BY` instead.

```sql
SELECT c.name, COUNT(p.purchase_id) AS total_purchases
FROM clients c
LEFT JOIN purchases p ON c.client_id = p.client_id
GROUP BY c.client_id, c.name;
-- Alice | 2
-- Bob   | 0
-- Carol | 0
```

**Mistake 3: No index on the join key.**

```sql
CREATE INDEX ON purchases(client_id);
```

Without it, a LEFT JOIN on a million-row table does a full scan on every query. Add the index once and the difference is dramatic.

### Real-world usage

- E-commerce: all users LEFT JOIN orders, to find customers who never bought anything.
- SaaS dashboards: all users LEFT JOIN events, so zero-activity users still appear in retention reports.
- CRM: all leads LEFT JOIN deals, so the pipeline shows leads with no deal attached.
- REST API: `users LEFT JOIN profiles` when a user profile is optional and not every user has one.

### Follow-up questions

**Q:** Can LEFT JOIN return duplicate rows?
**A:** Yes. If the right table has multiple matching rows, the left row repeats once per match. Use GROUP BY or aggregate functions to collapse them.

**Q:** What if the left table itself is empty?
**A:** The result is empty. There are no left rows to preserve.

**Q:** Is LEFT JOIN slower than INNER JOIN?
**A:** Often slightly, because the engine tracks unmatched left rows. With proper indexes the gap is small. The bigger factor is result set size and whether you aggregate afterward.

**Q:** You have a 3-table chain: `users LEFT JOIN orders LEFT JOIN items`. You add `WHERE items.name = 'Laptop'`. What happens?
**A:** The entire result behaves like INNER JOIN. Users with no orders and orders with no matching items get dropped. Move the condition to the ON clause of the second join: `ON orders.order_id = items.order_id AND items.name = 'Laptop'`.

## Examples

### Basic: all clients with optional purchases

```sql
-- Returns all clients; NULL where no purchase exists
SELECT c.name, p.item
FROM clients c
LEFT JOIN purchases p ON c.client_id = p.client_id;

-- Alice | Laptop
-- Alice | Mouse
-- Bob   | NULL
-- Carol | NULL
```

Alice appears twice because she has two purchases. Bob and Carol appear once each with NULL in item.

### Intermediate: order stats for a user dashboard

```sql
-- users: user_id | username
-- orders: order_id | user_id | total

SELECT
  u.username,
  COUNT(o.order_id)        AS order_count,
  COALESCE(AVG(o.total), 0) AS avg_order_value
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username;

-- alice | 2 | 524.00
-- bob   | 0 | 0.00
```

`COUNT(o.order_id)` returns 0 for bob because it counts non-NULL values only. `COALESCE(..., 0)` converts the NULL average to 0. This pattern shows up on almost every analytics dashboard that tracks per-user activity.

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

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.