Explain the different types of SQL JOINs. When would you use each one?

Question

Accepted Answer

## Sample Tables

```
users                      orders
+----+-------+             +----+---------+-------+
| id | name  |             | id | user_id | total |
+----+-------+             +----+---------+-------+
| 1  | Alice |             | 1  | 1       | 100   |
| 2  | Bob   |             | 2  | 1       | 50    |
| 3  | Carol |             | 3  | 4       | 75    |
+----+-------+             +----+---------+-------+
```

## INNER JOIN

Returns only rows that have matches in **both tables**.

```sql
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;
```

Result:
```
| name  | total |
|-------|-------|
| Alice | 100   |
| Alice | 50    |
```
*Carol has no orders, order 3 has no matching user → excluded*

## LEFT JOIN (LEFT OUTER JOIN)

Returns **all rows from left table** + matching rows from right. NULL if no match.

```sql
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
```

Result:
```
| name  | total |
|-------|-------|
| Alice | 100   |
| Alice | 50    |
| Bob   | NULL  |
| Carol | NULL  |
```
*All users shown, even without orders*

## RIGHT JOIN (RIGHT OUTER JOIN)

Returns **all rows from right table** + matching rows from left.

```sql
SELECT users.name, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
```

Result:
```
| name  | total |
|-------|-------|
| Alice | 100   |
| Alice | 50    |
| NULL  | 75    |
```
*All orders shown, even without matching user*

## FULL OUTER JOIN

Returns **all rows from both tables**. NULL where no match exists.

```sql
SELECT users.name, orders.total
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
```

Result:
```
| name  | total |
|-------|-------|
| Alice | 100   |
| Alice | 50    |
| Bob   | NULL  |
| Carol | NULL  |
| NULL  | 75    |
```

## When to Use Each

| Join Type | Use Case |
|-----------|----------|
| **INNER** | Only want matching records (most common) |
| **LEFT** | Want all from primary table + optional related data |
| **RIGHT** | Want all from secondary table (rare, often rewrite as LEFT) |
| **FULL** | Want complete picture from both tables |

## Real-World Examples

```sql
-- Show all users and their order count (including 0)
SELECT users.name, COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

-- Show only users who have made orders
SELECT DISTINCT users.name
FROM users
INNER JOIN orders ON users.id = orders.user_id;
```