NULL Logic and CASE Expressions

Julia Fischer, who leads customer support, sends two questions: “How many tickets are still open? And what’s our average satisfaction score?” Both answers depend on the same value, and it’s the one that causes more wrong numbers in analytics than any other: NULL.

In support_tickets, closed_at is NULL while a ticket is open, and satisfaction is NULL when the customer never rated it. NULL doesn’t mean zero or empty text. It means unknown.

Unknown is contagious

Try finding open tickets the way you’d find anything else:

SELECT COUNT(*) AS open_tickets
FROM support_tickets
WHERE closed_at = NULL;

Zero. Is closed_at equal to NULL? SQL’s answer is “unknown”, because comparing anything with an unknown value gives an unknown result, even NULL = NULL. And WHERE keeps a row only when its condition is TRUE, never when it’s unknown. The test for missing values has its own syntax:

SELECT COUNT(*) AS open_tickets
FROM support_tickets
WHERE closed_at IS NULL;

56 open tickets. This is three-valued logic: every condition is TRUE, FALSE or NULL. AND and OR follow the tables below. The rule of thumb: NULL wins unless the other side settles the answer on its own (FALSE for AND, TRUE for OR).

Where NULL quietly drops rows

The dangerous case isn’t = NULL, which returns nothing and gets noticed. It’s a “not equal” filter on a column that has NULLs:

SELECT
  (SELECT COUNT(*) FROM orders WHERE coupon_code <> 'BLACKFRIDAY25') AS with_not_equal,
  (SELECT COUNT(*) FROM orders WHERE coupon_code IS DISTINCT FROM 'BLACKFRIDAY25') AS null_safe;

264 versus 1,942. “Orders that didn’t use BLACKFRIDAY25” obviously includes the 1,678 orders with no coupon at all, but <> dropped every one of them. IS DISTINCT FROM treats NULL as a comparable value, so it’s the NULL-safe version of <>. SQLite also accepts the shorter IS NOT 'BLACKFRIDAY25'; PostgreSQL only accepts IS DISTINCT FROM.

NULL in aggregates

Aggregates skip NULLs, which is usually what you want but always worth stating:

SELECT
  COUNT(*)                     AS tickets,
  COUNT(satisfaction)          AS rated,
  ROUND(AVG(satisfaction), 2)  AS avg_score,
  ROUND(AVG(COALESCE(satisfaction, 0)), 2) AS wrong_avg
FROM support_tickets;

COUNT(*) counts rows (873); COUNT(satisfaction) counts known values (588). AVG divides by the known count, giving 3.74. Replacing NULL with 0 using COALESCE drags the average to 2.52, inventing 285 furious customers who never said anything. Report Julia the 3.74 and the response rate, 588 of 873: an average over two-thirds of tickets should say so.

COALESCE(a, b, ...) returns its first non-NULL argument and is right for display defaults, like COALESCE(coupon_code, 'none'). Its mirror image is NULLIF(a, b), which returns NULL when a = b. Dividing by NULLIF(denominator, 0) gives NULL instead of a divide-by-zero error in engines that raise one.

Decide what each NULL means

NULL is one marker for several different situations, and the right treatment depends on which one you’re looking at. In Cartwheel alone:

• closed_at is NULL because the event hasn’t happened yet. Count these as open, and exclude them from resolution times.
• satisfaction is NULL because the customer didn’t answer. Report the response rate next to the average.
• coupon_code is NULL because the order had no coupon. Here NULL really means “none”, so COALESCE(coupon_code, 'none') is a fair label.
• referred_by is NULL because the customer wasn’t referred, or because nobody recorded it. You can’t tell which, and you should say so.

NULLs also affect sorting. SQLite puts NULLs first in ascending order and last in descending order; PostgreSQL does the opposite. If the position matters, say it explicitly with ORDER BY satisfaction NULLS LAST, which both engines accept.

CASE: labels from logic

Julia also wants a readable status for each ticket; that one is your exercise. Here is the same tool on orders, labelling how each one was priced. CASE evaluates its WHEN conditions top to bottom and returns the first match:

SELECT
  order_id,
  coupon_code,
  shipping_fee,
  CASE
    WHEN coupon_code IS NULL AND shipping_fee = 0 THEN 'free shipping only'
    WHEN coupon_code IS NULL THEN 'full price'
    WHEN coupon_code = 'FREESHIP' THEN 'coupon: shipping'
    ELSE 'coupon: discount'
  END AS price_type
FROM orders
ORDER BY order_id
LIMIT 6;

Two rules make CASE reliable. Order the WHENs from most specific to most general, because the first TRUE branch wins and the rest are never checked. And write an ELSE on purpose: without one, unmatched rows get NULL, which then flows into everything downstream. SQLite also has IIF(condition, a, b) for two-way choices, but CASE is standard SQL and works in every engine.

Missing values are one source of surprise; dates are the other, and they’re next.