Handling NULL Values
Overview
NULL is the most dangerous concept in SQL. NULL does not mean zero (0), and it does not mean an empty string (''). It mathematically means 'Unknown' or 'Missing Data'. Because it represents the unknown, any standard mathematical operation involving NULL instantly becomes NULL (e.g., 10 + NULL = NULL). To prevent this from destroying your calculations, SQL provides specialized functions to safely handle and replace missing data.
Syntax
-- 1. IS NULL / IS NOT NULL (Standard Filtering)
SELECT * FROM users
WHERE phone_number IS NULL; -- Never use "= NULL"!
-- 2. COALESCE (The Lifesaver!)
-- Returns the FIRST non-null value in the list.
SELECT
name,
-- If bonus is NULL, treat it as 0!
salary + COALESCE(bonus, 0) AS total_compensation
FROM employees;
-- 3. NULLIF (Division by Zero protection)
-- Returns NULL if the two values match.
-- 100 / 0 crashes the DB. 100 / NULL safely returns NULL.
SELECT total_sales / NULLIF(total_employees, 0) AS sales_per_employee
FROM departments;Common Pitfalls
- Writing
WHERE email = NULL. This will never throw an error, but it will NEVER return any rows, even if there are millions of null emails. Because NULL means 'Unknown', asking 'Is Unknown exactly equal to Unknown?' mathematically evaluates toFALSEin SQL. You MUST writeWHERE email IS NULL. - Forgetting how
NOT INreacts toNULL. If you writeWHERE id NOT IN (1, 2, NULL), the entire query collapses and returns 0 rows. ANULLinside aNOT INlist breaks the boolean logic completely.
Interview Questions
COALESCE() function operates when provided multiple arguments.COALESCE(val1, val2, val3, ...) evaluates the arguments from left to right. It instantly returns the very first argument that is not NULL. If all arguments are NULL, it returns NULL. It is the industry standard for establishing safe defaults.
Real-World Example
Building a robust Contact UI that falls back through multiple phone numbers.
SELECT
customer_id,
-- Prioritize mobile. If missing, try work. If missing, say 'No Phone'.
COALESCE(mobile_phone, work_phone, 'No Phone on Record') AS best_contact_number
FROM customers;Check Your Knowledge
Test your understanding of Handling NULL Values with these quick questions.