Unit 10.02: Edge cases: empty groups, refunds, and boundaries

Unit ID: SQL-M10-U03 - Estimated active time: 14-17 minutes Objective: test the inputs that break otherwise-correct queries.

Correct SQL still fails on unusual data

Every module so far contributed an edge case. This unit collects them into a checklist you run before publishing.

Empty groups

A LEFT JOIN plus GROUP BY produces groups with nothing in them:

SELECT COUNT(*) FROM (
  SELECT c.country
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.country
  HAVING COUNT(o.order_id) = 0
);
-- 1

One country group has zero orders. COUNT(o.order_id) correctly returns 0 there, but AVG(o.order_total) returns NULL, not zero - and a chart will render that as a gap rather than "no orders". Decide which you want and make it explicit with COALESCE.

The NULL boundary

From Module 3, still the most common edge case:

SELECT COUNT(*) FILTER (WHERE country IS NULL) AS unknown_country FROM customers;
-- 300

Any two-way split on country loses these 300 unless you add the third bucket.

Date boundaries

From Module 8:

SELECT COUNT(*) FROM orders WHERE placed_at BETWEEN '2026-06-01' AND '2026-06-30';  -- 967
SELECT COUNT(*) FROM orders WHERE placed_at >= '2026-06-01' AND placed_at < '2026-07-01';  -- 1000

33 orders live or die on the choice of range style.

Refunds, cancellations, and status

Our data has a pending status worth ₹15,558. Real data usually has more: refunds, cancellations, test orders, internal accounts. Each is a decision:

SELECT status, COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders
GROUP BY status;
-- completed | 988 | 2685905.00
-- pending   |  12 |   15558.00

Listing every status before filtering is how you discover the ones you did not know existed. Filtering first hides them permanently.

The pre-publication checklist

1. Do the buckets sum to the total? *(Unit 10.01)*
2. Is the magnitude plausible against min/max? *(Unit 10.02)*
3. Are there NULLs in any column you filtered or grouped on?
4. Are there empty groups, and should they show as 0 or as absent?
5. Did you enumerate every status before excluding any?
6. Are your date boundaries half-open?

Practice

List every distinct status and explain why doing so is safer than filtering to the one you expect.

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY orders DESC;
-- completed | 988
-- pending   |  12

Takeaway

Enumerate before you filter, and run the six-point checklist. Correct SQL over unexamined data still produces wrong reports.

---

Mark complete