Learn

NULL is SQL's way of saying "unknown" or "missing". It's NOT zero, NOT empty string — it's the absence of a value.

Three-valued logic

SQL's WHERE evaluates to TRUE, FALSE, or NULL. Only TRUE rows are returned.

SELECT * FROM users WHERE age = NULL;     -- ⚠ returns NOTHING
SELECT * FROM users WHERE age = 30;        -- normal
SELECT * FROM users WHERE age IS NULL;     -- this is what you want

= NULL always evaluates to NULL — never TRUE. Same for <>, <, >, etc. Use IS NULL and IS NOT NULL.

NULL in arithmetic

Any operation involving NULL gives NULL:

SELECT NULL + 5;        -- NULL
SELECT NULL || 'hi';    -- NULL  (both SQLite and Postgres — NULL propagates through ||)
SELECT NULL = NULL;     -- NULL  (NOT TRUE!)

Exception: IS NULL and IS DISTINCT FROM (Postgres) handle NULLs.

NULL in aggregates

COUNT(*) counts ALL rows. COUNT(col) counts NON-NULL values:

SELECT COUNT(*) FROM users;          -- 10
SELECT COUNT(email) FROM users;      -- 8 (2 have NULL email)

SUM, AVG, MAX, MIN ignore NULLs. AVG([1, 2, NULL]) = 1.5, not 1.

COALESCE — first non-NULL

Returns the first non-NULL argument:

SELECT name, COALESCE(nickname, name, 'anonymous') AS display
FROM users;

For each user: nickname if set, else real name, else 'anonymous'. Way cleaner than nested CASE expressions.

IFNULL / NULLIF / CASE

IFNULL (SQLite, MySQL) / NVL (Oracle) — two-argument COALESCE:

SELECT IFNULL(email, 'no email') FROM users;

NULLIF(a, b) — returns a, but NULL if a = b. Useful for treating sentinel values as missing:

SELECT NULLIF(score, -1) FROM games;
-- -1 (the "didn't play" sentinel) becomes NULL, AVG/COUNT will skip it

CASE — full conditional logic:

SELECT name,
       CASE
           WHEN age IS NULL THEN 'unknown'
           WHEN age < 13 THEN 'child'
           WHEN age < 20 THEN 'teen'
           ELSE 'adult'
       END AS category
FROM users;

NULL in joins

With LEFT JOIN, the right side is NULL when there's no match:

SELECT u.name, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id;
-- users without posts get a NULL title

Filter "users without posts":

SELECT u.name FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE p.id IS NULL;

This is the anti-join pattern — useful for finding orphans.

NULL and UNIQUE

Most DBs allow MULTIPLE NULLs in a UNIQUE column — because NULL ≠ NULL. SQLite, Postgres, MySQL all do this. SQL Server is different (allows only one NULL).

Keep NULLs out when you can

• NOT NULL every column that should always have a value (most columns!)
• Use sentinel values when domain warrants — '' for missing string, 0 for missing count
• But use NULL for genuinely-unknown — birthday, optional middle name

NULL is fine in moderation. Default to NOT NULL; opt into NULL when the absence is meaningful.