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
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 |
| id | campaign_id | source |
|---|---|---|
| 301 | 1 | google_ads |
| 302 | 1 | google_ads |
| 303 | 2 | |
| 304 | 3 |
| id | name |
|---|---|
| 1 | Spring Launch |
| 2 | Retention Webinar |
| 3 | Finance Retargeting |
| 4 | Enterprise Search |
| 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.
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;Practice this concept
Marketing wants every campaign in the result, including campaigns that have no lead rows, using RIGHT JOIN to preserve campaigns.
marketingPrefix tables with marketing.table_name.
campaign_idcampaign_namelead_idcreated_atmarketing.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 |
marketing.leads| Column | Type |
|---|---|
| id | integer |
| campaign_id | integer |
| text | |
| created_at | timestamp with time zone |
| qualified_at | timestamp with time zone |
| converted_at | timestamp with time zone |
| lead_score | integer |
| source | text |
| country | text |
| archive_status | text |
Sign up free to try it on a real business scenario