Topic 19 of 52
IS NULL, IS NOT NULL
Overview
NULL represents an unknown or missing value — it is not zero, empty string, or false. NULL requires special handling with IS NULL / IS NOT NULL because standard comparison operators (=, !=) always return false when comparing to NULL.
Syntax
sql
-- Check for NULL
WHERE deleted_at IS NULL -- record not deleted
WHERE phone_number IS NULL -- no phone on file
WHERE discount IS NOT NULL -- has a discount applied
-- NULL in arithmetic: any operation with NULL = NULL
SELECT NULL + 5; -- result: NULL
SELECT NULL || 'text'; -- result: NULL (in most DBs)
-- COALESCE: return first non-NULL value
SELECT COALESCE(nickname, full_name, email) AS display_name FROM users;
SELECT COALESCE(discount, 0) AS discount FROM products;
-- NULLIF: return NULL if values are equal
SELECT NULLIF(stock, 0) FROM products; -- NULL if stock is 0 (avoid division by zero)
-- IS DISTINCT FROM: NULL-safe comparison
WHERE column IS DISTINCT FROM 'value' -- works even if column is NULLCommon Pitfalls
- WHERE column = NULL is always false — never works! Always use IS NULL or IS NOT NULL.
- NOT IN fails silently with NULLs — if the subquery has any NULL, NOT IN returns no rows. Use NOT EXISTS instead.
- Interview tip: COALESCE is NULL-safe and returns the first non-NULL argument — essential for building safe expressions with nullable columns.
Real-World Example
Customer profile completeness report using NULL checks:
example
sql
-- User profile completeness audit
SELECT
u.id,
u.email,
CASE WHEN u.full_name IS NOT NULL THEN 1 ELSE 0 END AS has_name,
CASE WHEN u.phone IS NOT NULL THEN 1 ELSE 0 END AS has_phone,
CASE WHEN u.avatar_url IS NOT NULL THEN 1 ELSE 0 END AS has_avatar,
CASE WHEN u.bio IS NOT NULL THEN 1 ELSE 0 END AS has_bio,
-- Completeness score
(
CASE WHEN u.full_name IS NOT NULL THEN 25 ELSE 0 END +
CASE WHEN u.phone IS NOT NULL THEN 25 ELSE 0 END +
CASE WHEN u.avatar_url IS NOT NULL THEN 25 ELSE 0 END +
CASE WHEN u.bio IS NOT NULL THEN 25 ELSE 0 END
) AS profile_score,
-- Safe display values
COALESCE(u.full_name, 'Anonymous') AS display_name,
COALESCE(u.phone, 'Not provided') AS phone_display
FROM users u
WHERE u.deleted_at IS NULL
ORDER BY profile_score ASC;