SQL INNER vs OUTER JOIN
LEFT, RIGHT, and FULL OUTER are all 'outer' joins — they preserve unmatched rows. INNER is the only one that doesn't.
What & Why
Every join type covered in this series falls into one of two families. INNER keeps only matched rows. OUTER (covering LEFT, RIGHT, and FULL OUTER) preserves at least some unmatched rows, filling gaps with NULL.
See How It Works
BUSINESS QUESTION
Marketing wants every campaign in a coverage report, even when a campaign has no leads.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
| id | campaign_id | created_at | qualified_at | converted_at | lead_score | source | country | |
|---|---|---|---|---|---|---|---|---|
| 301 | 1 | ana@example.com | 2024-01-21 09:10:00+00 | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 | 86 | google_ads | US |
| 302 | 1 | ben@example.com | 2024-01-24 12:40:00+00 | NULL | NULL | 52 | google_ads | CA |
| 303 | 2 | chloe@example.com | 2024-02-16 08:30:00+00 | 2024-02-18 14:20:00+00 | NULL | 74 | GB | |
| 304 | 3 | dev@example.com | 2024-03-20 17:15:00+00 | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 | 91 | US |
EXAMPLE QUERY
SELECT
c.name,
COUNT(l.id) AS lead_count
FROM marketing.campaigns c
LEFT JOIN marketing.leads l ON l.campaign_id = c.id
GROUP BY c.name
ORDER BY lead_count ASC, c.name ASC;RESULT — exact output from the displayed Queryflo rows
| name | lead_count |
|---|---|
| Enterprise Search | 0 |
| Finance Retargeting | 1 |
| Retention Webinar | 1 |
| Spring Launch | 2 |
LEFT JOIN preserves Enterprise Search and COUNT(l.id) returns zero.
Sign up free to try it on a real business scenario