SQL Missing Records Between Tables
SQL Missing Records Between Tables helps analysts return parent rows that have no related child row.
What & Why
The Missing Records Between Tables pattern is used to return parent rows that have no related child row. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.
See How It Works
BUSINESS QUESTION
Marketing wants campaigns that have not generated a lead.
| 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.id AS campaign_id,
c.name
FROM marketing.campaigns c
WHERE NOT EXISTS (
SELECT 1
FROM marketing.leads l
WHERE l.campaign_id = c.id
)
ORDER BY campaign_id;RESULT — campaign without a lead
| campaign_id | name |
|---|---|
| 4 | Enterprise Search |
Campaign IDs 1 through 3 appear in marketing.leads; campaign 4 does not.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario