Missing records
Ops is doing a post-mortem on a checkout outage and wants a sanity check: “Which days had no orders at all? If there was a bad stretch, I want to see exactly which days.”
SELECT DISTINCT placed_at FROM orders, and I look for the gaps between the dates that come back?
For a handful of order days, sure — eyeball it, like the WHERE lesson's SELECT * plan. NOVA takes orders every day, all year. You're not scanning 365 gaps by eye.
Here’s the actual snag: SQL can only ever show you rows that exist. A day with zero orders leaves no row behind in orders at all — there’s nothing to filter, group, or count, because as far as orders is concerned, that day never happened.
To find something that’s missing, you need a second list of everything that should exist, then compare. That’s exactly what you did back in “What about customers who never ordered?” — LEFT JOIN customers to orders, then look for the rows where the order side came back NULL. This is the same move: an anti-join. Same idea, just pointed at dates instead of customers.
Build the list of what should exist. LEFT JOIN the real data onto it. Whatever comes back NULL is missing.
NOVA doesn’t have a calendar table, so build a tiny one by hand with literal dates — a common, perfectly portable trick when you need a reference list that doesn’t exist yet. Here’s the first week of May 2023:
WITH calendar AS (
SELECT '2023-05-01' AS day UNION ALL
SELECT '2023-05-02' UNION ALL
SELECT '2023-05-03' UNION ALL
SELECT '2023-05-04' UNION ALL
SELECT '2023-05-05' UNION ALL
SELECT '2023-05-06' UNION ALL
SELECT '2023-05-07' UNION ALL
SELECT '2023-05-08'
)
SELECT c.day, o.id, o.customer_id
FROM calendar c
LEFT JOIN orders o ON o.placed_at = c.day;| day | id | customer_id |
|---|---|---|
| 2023-05-01 | 8 | 5 |
| 2023-05-02 | NULL | NULL |
| 2023-05-03 | NULL | NULL |
| 2023-05-04 | NULL | NULL |
| 2023-05-05 | NULL | NULL |
| 2023-05-06 | NULL | NULL |
| 2023-05-07 | NULL | NULL |
| 2023-05-08 | NULL | NULL |
One real order landed on May 1st; every other day in that window has nothing to join to, so o.id and o.customer_id come back NULL. Filter down to just those:
WITH calendar AS (
SELECT '2023-05-01' AS day UNION ALL
SELECT '2023-05-02' UNION ALL
SELECT '2023-05-03' UNION ALL
SELECT '2023-05-04' UNION ALL
SELECT '2023-05-05' UNION ALL
SELECT '2023-05-06' UNION ALL
SELECT '2023-05-07' UNION ALL
SELECT '2023-05-08'
)
SELECT c.day
FROM calendar c
LEFT JOIN orders o ON o.placed_at = c.day
WHERE o.id IS NULL
ORDER BY c.day;| day |
|---|
| 2023-05-02 |
| 2023-05-03 |
| 2023-05-04 |
| 2023-05-05 |
| 2023-05-06 |
| 2023-05-07 |
| 2023-05-08 |
Running…
Try it: What happens if you swap LEFT JOIN for a plain JOIN, keeping WHERE o.id IS NULL?
Run your query to see results here.
Empty — and dangerously so, because it’s empty for the wrong reason. A plain (inner) JOIN only keeps calendar days that already found a match in orders, so o.id can never be NULL by the time WHERE runs; there’s nothing left for the filter to catch. It would be easy to misread this as “no missing days,” when really the missing days were thrown away before the check even started. The LEFT JOIN is what keeps the unmatched calendar rows around long enough to notice they’re unmatched.
Ops wants the same check for a different window: which days in the first two weeks of August 2023 (Aug 1–14) had no orders at all? Return each missing day.
Hint 1
Same two-part pattern as the May example: a reference list of every day in the window, then an anti-join against the real orders.
Hint 2
Build a calendar CTE with 14 literal dates, '2023-08-01' through '2023-08-14', one per UNION ALL line.
Hint 3
LEFT JOIN orders onto the calendar ON o.placed_at = c.day, then WHERE o.id IS NULL.
Solution
Running…
NOVA took orders on August 1st, 5th, and 11th that window — everything else, eleven days, comes back empty. Nothing about this was a new idea: it’s the exact same LEFT JOIN IS NULL anti-join from the joins chapter, just aimed at a hand-built list of dates instead of an existing table of customers. Once you can name “which rows are missing” as its own pattern, you stop needing a calendar table handed to you — you can build the reference list yourself.
A table can never show you a row that doesn’t exist. To find what’s missing, build (or find) a reference list of everything that should exist, LEFT JOIN the real data onto it, and filter WHERE the joined side IS NULL. Swapping in a plain JOIN silently breaks this — it discards the unmatched rows before you can check for them.