NULL represents an unknown or missing value, not zero, not an empty string, and not "false" — it's its own distinct concept. Any comparison involving NULL with a standard operator (=, <>, <, >) evaluates to NULL (treated as not-true) rather than true or false, which is why WHERE column = NULL never matches anything; the dedicated IS NULL / IS NOT NULL operators exist specifically to test for NULL correctly.
Why = NULL never works
This trips up nearly everyone at least once — it's not a bug, it's the SQL standard's defined behavior for an unknown value compared to anything.
SELECT * FROM users WHERE middle_name = NULL;
-- returns ZERO rows, even if many users have a NULL middle_name — always
SELECT * FROM users WHERE middle_name IS NULL;
-- this is the CORRECT way — returns every row where middle_name is NULLCOALESCE: the first non-NULL value
COALESCE takes any number of arguments and returns the first one that isn't NULL — the standard way to supply a fallback/default value inline in a query.
SELECT name, COALESCE(nickname, name) AS display_name
FROM users;
-- shows the nickname if set, otherwise falls back to the real name
SELECT COALESCE(phone, email, 'No contact info') AS contact
FROM customers;NULL in aggregates and joins
Aggregate functions like COUNT, SUM, and AVG silently ignore NULL values rather than treating them as zero — COUNT(column) counts only non-NULL values, while COUNT(*) counts every row regardless. A LEFT JOIN's unmatched rows fill the right table's columns with NULL, which is exactly the mechanism the earlier joins topic used to find "customers with zero orders."
SELECT COUNT(*) FROM orders; -- every row
SELECT COUNT(discount_code) FROM orders; -- only rows where discount_code is NOT NULL
SELECT AVG(discount_code IS NOT NULL) FROM orders; -- NULLs never inflate or deflate an AVG