SQL Joins Visualized

Joins combine data from multiple tables. Here's a visual guide to the different types.

The Setup

Two tables:

-- users
| id | name    |
|----|---------|
| 1  | Alice   |
| 2  | Bob     |
| 3  | Charlie |

-- orders
| id | user_id | product |
|----|---------|---------|
| 1  | 1       | Widget  |
| 2  | 1       | Gadget  |
| 3  | 2       | Thing   |
| 4  | 4       | Other   |  -- Note: user_id 4 doesn't exist

INNER JOIN

Returns rows where there's a match in BOTH tables.

SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Result:

| name  | product |
|-------|---------|
| Alice | Widget  |
| Alice | Gadget  |
| Bob   | Thing   |

Charlie (no orders) and order 4 (no matching user) excluded.

LEFT JOIN

Returns ALL rows from left table, matched rows from right. NULL if no match.

SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Result:

| name    | product |
|---------|---------|
| Alice   | Widget  |
| Alice   | Gadget  |
| Bob     | Thing   |
| Charlie | NULL    |

Charlie included even though no orders.

RIGHT JOIN

Opposite of LEFT. All rows from right table.

SELECT users.name, orders.product
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

Result:

| name  | product |
|-------|---------|
| Alice | Widget  |
| Alice | Gadget  |
| Bob   | Thing   |
| NULL  | Other   |

Order 4 included even though no matching user.

FULL OUTER JOIN

All rows from both tables. NULL where no match.

SELECT users.name, orders.product
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

Result:

| name    | product |
|---------|---------|
| Alice   | Widget  |
| Alice   | Gadget  |
| Bob     | Thing   |
| Charlie | NULL    |
| NULL    | Other   |

Everyone included.

Practical Examples

Find users without orders

SELECT users.*
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

Find orphaned orders

SELECT orders.*
FROM orders
LEFT JOIN users ON orders.user_id = users.id
WHERE users.id IS NULL;

Count orders per user

SELECT users.name, COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

Visualizing

INNER:      ╭──╮╭──╮
            │██╳██│
            ╰──╯╰──╯
            
LEFT:       ╭──╮╭──╮
            │██╳██│
            ╰──╯╰──╯
            ▲
            All left rows
            
RIGHT:      ╭──╮╭──╮
            │██╳██│
            ╰──╯╰──╯
                  ▲
                  All right rows
                  
FULL:       ╭──╮╭──╮
            │██╳██│
            ╰──╯╰──╯
            ▲    ▲
            All from both

Tips

1. **Start with INNER** - Most common, safest 2. **Use LEFT for optional relationships** - "Users and their orders if any" 3. **Check for NULLs** - They indicate missing relationships 4. **Mind performance** - Large joins can be slow, add indexes

Back to Journal