NULL Handling in SQL (IS NULL vs = NULL)

What NULL actually means

NULL is not a value — it's the absence of one. It means "we don't know" or "not applicable." That's fundamentally different from an empty string '' (a known, zero-length text) or 0 (a known number). Because it represents the unknown, NULL refuses to behave like a normal value in comparisons.

Why = NULL never works (three-valued logic)

Most languages have two boolean values. SQL has three: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL produces UNKNOWN:

A WHERE clause keeps a row only when its condition is TRUE. Since col = NULL is UNKNOWN (never TRUE), it matches nothing — no error, just zero rows.

-- ❌ Returns 0 rows, even if some phones really are NULL
SELECT * FROM users WHERE phone = NULL;

IS NULL / IS NOT NULL — the right way

IS NULL and IS NOT NULL are the only correct way to test for NULLs. They return TRUE/FALSE (never UNKNOWN), so they actually filter rows.

-- ✅ Rows with a missing phone
SELECT * FROM users WHERE phone IS NULL;

-- ✅ Rows that do have a phone
SELECT * FROM users WHERE phone IS NOT NULL;

The NOT IN + NULL trap

This one bites even experienced developers. If the list or subquery inside NOT IN contains a single NULL, the entire expression becomes UNKNOWN for every row — so you get zero rows back.

-- ❌ Returns nothing if any manager_id is NULL
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

-- ✅ Fix A: NOT EXISTS is NULL-safe
SELECT e.name FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM employees m WHERE m.manager_id = e.id
);

-- ✅ Fix B: exclude NULLs from the NOT IN list
SELECT name FROM employees
WHERE id NOT IN (
  SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
);

How aggregates treat NULL

Aggregate functions ignore NULLs — which is usually what you want, but it changes the math:

• SUM, MIN, MAX skip NULLs.
• AVG divides by the number of non-NULL values, not the row count — so NULLs don't drag the average toward zero.
• COUNT(column) skips NULLs; COUNT(*) counts every row. (Full breakdown in COUNT(*) vs COUNT(column).)

NULL in GROUP BY, DISTINCT & ORDER BY

For grouping and de-duplication, SQL makes a pragmatic exception: it treats all NULLs as "the same" so they don't scatter.

• GROUP BY puts all NULLs into a single group.
• DISTINCT keeps just one NULL.
• ORDER BY sorts NULLs together, but where they land differs by database. PostgreSQL and Oracle treat NULLs as largest (last in ascending); MySQL and SQL Server put them first. Be explicit with NULLS FIRST / NULLS LAST where supported.

-- PostgreSQL / Oracle: control NULL placement explicitly
SELECT name, phone FROM users
ORDER BY phone ASC NULLS LAST;

Replacing NULLs: COALESCE, IFNULL, NULLIF

To substitute a default for missing values, use COALESCE — it's standard SQL and returns the first non-NULL argument.

-- Show 'N/A' when phone is missing
SELECT name, COALESCE(phone, 'N/A') AS phone
FROM users;

-- Vendor-specific equivalents (same idea)
-- MySQL:      IFNULL(phone, 'N/A')
-- SQL Server: ISNULL(phone, 'N/A')
-- Oracle:     NVL(phone, 'N/A')

-- NULLIF does the reverse: return NULL when two values match
SELECT NULLIF(discount, 0) AS discount FROM orders;  -- 0 becomes NULL

NULL cheat sheet