There's a topic every beginner trips over without exception — NULL. The query looks right but returns the wrong thing; counters diverge; rows vanish from results. Nine times out of ten, NULL is under the hood. Let's deal with it — and with data types, the other half of the same rake.

NULL means "unknown," not zero

NULL is the mark "no value here." It's not zero, not the empty string '', but absence itself: the order's delivery date isn't set, the customer's phone isn't filled in. And NULL has special logic: any comparison with it yields neither true nor false but "unknown."

SELECT * FROM customers WHERE phone = NULL;   -- always empty!

This query returns no rows — even if thousands of phones are missing. "Equal to unknown-what?" — "unknown," and the row doesn't make the result. NULL has dedicated operators:

SELECT * FROM customers WHERE phone IS NULL;      -- no phone
SELECT * FROM customers WHERE phone IS NOT NULL;  -- phone present

The sneakier part: NULL hides in negations too. WHERE status <> 'paid' returns orders whose status differs from paid — but does not return orders where the status is NULL: a comparison with the unknown isn't true. Want "everything except paid, including empty" — write WHERE status <> 'paid' OR status IS NULL.

Three more places where NULL changes the result:

  • Aggregates ignore NULL: hence the COUNT(*) vs COUNT(phone) difference from the aggregates article; AVG averages only the filled-in values.
  • LEFT JOIN produces NULL in the columns of the table where no match was found — we used that to find "orphans".
  • Arithmetic with NULL yields NULL: amount + NULL is NULL, and the total "evaporates."

The COALESCE function substitutes a fallback for NULL: COALESCE(phone, 'not specified') returns the phone, or the placeholder when it's missing.

Types: why '10' is not 10

Every column has a type, and comparisons must happen in that type:

  • Numbers (integer, numeric) — no quotes: amount > 1000. The string '10' and the number 10 are different things: strings compare character by character, so in a string column '9' > '10' (nine "beats" the one). If numbers in a database suddenly sort as 1, 10, 2, 20 — the column is a string, and that alone is an interesting find.
  • Strings (varchar, text) — in single quotes, exact down to case and spaces.
  • Boolean (boolean) — true/false: WHERE is_active = true. Some databases use 0/1 numbers instead — look at the actual data.
  • Dates and time (date, timestamp) — quoted in the '2026-07-08' format. Date ranges are convenient with BETWEEN or comparisons: WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01' — all of July. Note the "start of next month, strictly less" trick: it honestly captures the last day's records with times like 23:59:59.
  • Time zones — the topic people hit unexpectedly: a timestamp may be stored in UTC while the UI shows local time. If a record "created at 01:30" sits in the database under "yesterday's" time — most likely not a bug but UTC.

Where this applies

NULL is a minefield map of the product: unfilled fields are exactly where the UI, reports, and integrations behave unexpectedly. The query SELECT COUNT(*) FROM t WHERE important_field IS NULL is an express data-quality audit for any table. And understanding types removes false alarms: "sorting is broken" (no, the column is a string), "the time is off by an hour" (no, that's the time zone).

Where beginners stumble:

  • They write = NULL instead of IS NULL — the query silently returns nothing, and it seems the data doesn't exist.
  • They forget <> doesn't find NULL. "Everything except paid" without OR status IS NULL loses rows — and nobody notices.
  • They compare a timestamp with a "plain date" and wonder: WHERE created_at = '2026-07-08' finds nothing for the day — because it compares against midnight. The >= today AND < tomorrow range is reliable.

What to learn next. You can now read in every form — time to learn to modify data carefully: INSERT, UPDATE, DELETE and transactions.