NULL: The Value That Means "Unknown"

NULL means missing or not applicable. A visitor row might have badge_id NULL because nobody scanned a badge. That is different from badge_id = 0 or ''. A column only holds NULLs if its table allows them.

A comparison with NULL is never true. badge_id = NULL does not return the rows you expect, not even the rows whose badge_id is NULL, because SQL evaluates the comparison as UNKNOWN and WHERE keeps only rows where the condition is TRUE. Use the dedicated tests:

SELECT * FROM visits WHERE badge_id IS NULL;      -- no badge recorded
SELECT * FROM visits WHERE badge_id IS NOT NULL;  -- a badge was recorded

Three-valued logic. A SQL condition is TRUE, FALSE or UNKNOWN. NULL = NULL is UNKNOWN, NULL > 5 is UNKNOWN, and NOT UNKNOWN is still UNKNOWN. A row survives only on TRUE, so WHERE city <> 'Austin' silently drops rows whose city is NULL. If you want them back, say so: WHERE city <> 'Austin' OR city IS NULL.

Aggregates skip NULLs. COUNT(column) counts only non-NULL values while COUNT(*) counts rows, and SUM, AVG, MIN and MAX all ignore NULLs. An average over a column with 3 NULLs in 10 rows is an average of 7 values, not of 10 values with three zeros.

Giving NULL a fallback. COALESCE(a, b) returns the first non-NULL argument, so COALESCE(nickname, name) shows the name when there is no nickname. NULL is also how a LEFT JOIN reports 'no matching row', which is why IS NULL is the key to finding what is missing (see the LEFT JOIN and anti-join concept).