SQL RIGHT JOIN
The mirror image of LEFT JOIN — keeps every row from the RIGHT table, filling in NULL for any unmatched left-side rows.
What & Why
RIGHT JOIN flips which side is fully preserved — every row from the right table survives, and unmatched rows get NULL on the left side instead. Most people default to always writing LEFT JOIN (just swapping table order) for consistency, but it's worth recognizing RIGHT JOIN when reading someone else's query.
See How It Works
BUSINESS QUESTION
Marketing wants every campaign in the result, including campaigns that have no lead rows, using RIGHT JOIN to preserve campaigns.
| 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 |
| 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 |
Trace the relationship row by row1× speed
marketing.leads
| id | campaign_id | source |
|---|---|---|
| 301 | 1 | google_ads |
| 302 | 1 | google_ads |
| 303 | 2 | |
| 304 | 3 |
lead.campaign_id = campaign.id
marketing.campaigns · preserved right table
| id | name |
|---|---|
| 1 | Spring Launch |
| 2 | Retention Webinar |
| 3 | Finance Retargeting |
| 4 | Enterprise Search |
Result set · 2 rows
| campaign_id | campaign_name | lead_id | created_at |
|---|---|---|---|
| 1 | Spring Launch | 301 | 2024-01-21 09:10:00+00 |
| 1 | Spring Launch | 302 | 2024-01-24 12:40:00+00 |
The preserved Spring Launch campaign matches two lead rows.
EXAMPLE QUERY
SELECT
c.id AS campaign_id,
c.name AS campaign_name,
l.id AS lead_id,
l.created_at
FROM marketing.leads l
RIGHT JOIN marketing.campaigns c ON l.campaign_id = c.id
ORDER BY campaign_id, lead_id;Sign up free to try it on a real business scenario