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.
Now You Try
Practice this concept
Marketing wants every campaign with its lead count, including campaigns with zero leads. Order by lead_count ascending and campaign name ascending.
Available schema
marketingPrefix tables with marketing.table_name.
namelead_countmarketing.campaigns| Column | Type |
|---|---|
| id | integer |
| name | text |
| channel | text |
| spend | numeric |
| start_date | date |
| end_date | date |
| status | text |
| target_segment | text |
| legacy_id | text |
query.sql
Sign up free to try it on a real business scenario