SQL NULLs Demystified: What They Are and How to Handle Them

SQL NULLs can be one of the most confusing aspects of working with databases — even for people who already know how to write SELECT statements and JOIN tables. You write a perfectly logical WHERE clause, run your query, and still get results (or missing results) that don't make sense. Nine times out of ten, NULL is the culprit.

In this article, we'll demystify what NULL actually is in SQL, why it behaves the way it does, and how to handle it confidently in your queries.

What Exactly Is NULL?

NULL is not zero. NULL is not an empty string ''. NULL is not false. NULL means the absence of a value — it represents the unknown.

Think of it this way: if you have a users table and a user hasn't provided their phone number yet, the phone column isn't empty — it's unknown. SQL represents that unknown state as NULL.

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL,
    phone       VARCHAR(20),        -- optional, can be NULL
    age         INT                 -- optional, can be NULL
);

INSERT INTO users (name, email, phone, age) VALUES
    ('Alice Johnson', '[e-mail]', '555-1234', 29),
    ('Bob Smith',     '[e-mail]',   NULL,       34),
    ('Carol White',   '[e-mail]', '555-5678', NULL),
    ('David Brown',   '[e-mail]', NULL,       NULL);

Here, Bob and David have no phone on file, Carol and David have no age recorded. Their values are truly unknown, not blank.

Checking for NULL: IS NULL and IS NOT NULL

Here's where beginners make their first mistake. You cannot use = NULL to check for NULL values. This is because NULL is not equal to anything — not even to itself.

-- ❌ This will NOT work — returns 0 rows!
SELECT * FROM users WHERE phone = NULL;

-- ✅ This is correct
SELECT * FROM users WHERE phone IS NULL;

id	name	email	phone	age
2	Bob Smith	[e-mail]	NULL	34
4	David Brown	[e-mail]	NULL	NULL

And to find users who do have a phone number:

SELECT name, phone
FROM users
WHERE phone IS NOT NULL;

name	phone
Alice Johnson	555-1234
Carol White	555-5678

NULL in Comparisons: Three-Valued Logic

SQL uses what's called three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL evaluates to UNKNOWN — and rows that evaluate to UNKNOWN are excluded from results, just like FALSE.

-- What does SQL think of these?
SELECT
    NULL = NULL,         -- UNKNOWN (not TRUE!)
    NULL <> NULL,        -- UNKNOWN
    NULL > 5,            -- UNKNOWN
    NULL = 0,            -- UNKNOWN
    5 = 5                -- TRUE
;

This is why WHERE phone = NULL silently returns nothing — the comparison is UNKNOWN for every row.

COALESCE: Replacing NULL with a Default Value

The COALESCE function is your best friend for handling NULLs in output. It returns the first non-NULL value in a list of arguments.

SELECT
    name,
    COALESCE(phone, 'No phone on file') AS phone_display,
    COALESCE(age, 0) AS age_display
FROM users;

name	phone_display	age_display
Alice Johnson	555-1234	29
Bob Smith	No phone on file	34
Carol White	555-5678	0
David Brown	No phone on file	0

You can also chain multiple fallback values:

-- Try preferred_email first, then email, then a hard-coded fallback
SELECT
    name,
    COALESCE(preferred_email, email, '[e-mail]') AS contact_email
FROM users;

NULLIF: Turning a Value INTO NULL

NULLIF is the reverse of COALESCE — it converts a specific value to NULL if it matches a second argument. This is useful for avoiding division-by-zero errors or treating sentinel values like empty strings as NULLs.

-- Safely divide without crashing on zero denominators
SELECT
    product_name,
    total_revenue,
    total_units,
    total_revenue / NULLIF(total_units, 0) AS revenue_per_unit
FROM product_sales;

Without NULLIF, dividing by zero raises a database error. With it, you get NULL instead — which is the honest answer when there are no units sold.

How NULL Affects Aggregate Functions

This one surprises many beginners: aggregate functions like COUNT, AVG, SUM, MIN, and MAX all ignore NULL values automatically.

SELECT
    COUNT(*)          AS total_rows,
    COUNT(phone)      AS rows_with_phone,
    COUNT(age)        AS rows_with_age,
    AVG(age)          AS avg_age
FROM users;

total_rows	rows_with_phone	rows_with_age	avg_age
4	2	2	31.5

Notice:

• COUNT(*) counts all 4 rows (including rows with NULLs)
• COUNT(phone) counts only 2 rows — those where phone is not NULL
• AVG(age) calculates (29 + 34) / 2 = 31.5 — it ignores Carol and David's NULL ages entirely

This is usually what you want, but be aware of it! If you need to count rows including NULLs for a specific column, use COUNT(*) or COALESCE first.

Common NULL Gotchas

Gotcha 1: NULL in a NOT IN list

This one bites even experienced developers:

-- Say blocked_ids has values (2, NULL)
SELECT * FROM users
WHERE id NOT IN (2, NULL);
-- Returns 0 rows! Because NOT IN with NULL = UNKNOWN for everything

When using NOT IN, if the subquery or list contains any NULL values, the whole expression evaluates to UNKNOWN for every row and you get nothing back. Use NOT EXISTS or filter out NULLs from the list instead.

Gotcha 2: NULL in GROUP BY

NULL values in a GROUP BY column are grouped together as if they were equal (a special exception to the normal NULL comparison rules):

SELECT age, COUNT(*) AS user_count
FROM users
GROUP BY age;

age	user_count
29	1
34	1
NULL	2

Carol and David are grouped together under the NULL age bucket.

Gotcha 3: String concatenation with NULL

In most databases, concatenating NULL with a string produces NULL:

SELECT 'Hello, ' || NULL;  -- Returns NULL, not 'Hello, '

Use COALESCE to guard against this:

SELECT 'Hello, ' || COALESCE(name, 'stranger') FROM users;

Key Takeaways

• NULL means unknown, not zero, not empty string
• Always use IS NULL or IS NOT NULL — never = NULL
• Any comparison with NULL produces UNKNOWN (not TRUE or FALSE)
• Use COALESCE to substitute a default for NULL values in output
• Use NULLIF to convert a specific value back into NULL (great for preventing division by zero)
• Aggregate functions silently ignore NULLs — COUNT(column) vs COUNT(*) behaves differently
• Be very careful with NOT IN when NULLs might be present in the list

Once NULL "clicks," a whole category of mysterious query bugs disappears. Keep these rules handy the next time your WHERE clause returns unexpected results!

---

Have you ever been burned by a NULL-related bug? Share your story in the comments — I'd love to hear it! And if this cleared things up, hit the ❤️ button to help other beginners find it.

---