# Inner and Outer Joins — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/j-outer

> ON versus WHERE.

## Where you put the filter matters

An INNER JOIN keeps only matching pairs; a LEFT JOIN keeps every left row and fills right columns with NULL when there is no match. A condition on the right table in **WHERE** removes those NULL rows, quietly turning the LEFT JOIN into an inner join. Put conditions that restrict which right rows match in the **ON** clause to keep unmatched left rows. FULL OUTER JOIN keeps unmatched rows from both sides; CROSS JOIN produces every combination.

## Combine tables correctly

Joins are where most wrong numbers come from: lost rows, extra rows and silent filters.

![Three ideas: outer joins and filters, semi/anti joins, fan-out.](assets/figures/sql-deep-dive/section-2-map.svg) — Figure 2.1 — Outer joins, semi/anti joins and fan-out.

## The same filter in WHERE and in ON, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). With the filter in WHERE, John (no paid orders) disappears; with the filter in ON, John stays with a NULL order_id.

```sql
-- Filter in WHERE: turns the LEFT JOIN back into an inner join
SELECT c.name, o.id AS order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid' ORDER BY c.name, o.id;

-- Filter in ON: keeps customers without paid orders
SELECT c.name, o.id AS order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.name, o.id;
```

Output:

```
 name  | order_id 
-------+----------
 Asha  |      101
 Asha  |      102
 Asha  |      106
 Meera |      105
 Ravi  |      104
(5 rows)

 name  | order_id 
-------+----------
 Asha  |      101
 Asha  |      102
 Asha  |      106
 John  |         
 Meera |      105
 Ravi  |      104
(6 rows)
```

## Count rows before and after joins

Comparing row counts quickly reveals lost rows (filters) or extra rows (fan-out).

**Quiz:** What happens when you filter a LEFT JOIN's right table in WHERE?

- [ ] Nothing changes
- [x] Unmatched left rows are removed, like an inner join
- [ ] All rows are duplicated
- [ ] The query fails

*Answer:* Unmatched left rows are removed, like an inner join. NULL fails the WHERE condition.