Understanding NULL in SQL

Understanding NULL in SQL: Common Pitfalls and How to Handle Them

NULL is one of the first things every SQL learner meets and one of the last things fully understood. It doesn't mean zero, it doesn't mean empty string, and it doesn't behave like a normal value in comparisons — which is exactly where the bugs come from.

📘 What NULL Actually Means

NULL represents the absence of a value — "unknown," not "empty." A NULL phone number isn't the same as an empty string phone number; one means "we don't have this data," the other means "we know it's blank."

🧪 Why NULL = NULL Doesn't Work

SELECT * FROM customers WHERE phone = NULL;  -- ❌ Always returns zero rows
SELECT * FROM customers WHERE phone IS NULL; -- ✅ Correct

NULL isn't equal to anything — not even another NULL. In three-valued SQL logic, any comparison involving NULL evaluates to "unknown," not true or false, so the row is excluded either way. You must use IS NULL / IS NOT NULL, never = or !=.

🎯 NULL in Aggregate Functions

SELECT
  COUNT(*) AS total_rows,
  COUNT(phone) AS rows_with_phone,
  AVG(salary) AS avg_salary
FROM employees;

COUNT(*) counts every row regardless of NULLs. COUNT(column) only counts non-NULL values in that column — these can (and often do) return different numbers. AVG(), SUM(), and similar functions silently ignore NULLs rather than treating them as zero, which can quietly skew results if you're not expecting it.

🛠️ Handling NULLs Gracefully

-- COALESCE: return the first non-NULL value
SELECT name, COALESCE(phone, 'No phone on file') AS phone_display
FROM customers;

-- NULLIF: turn a specific value into NULL
SELECT NULLIF(discount_code, '') AS discount_code
FROM orders;

COALESCE is what you'll reach for constantly — filling in default display values, avoiding NULL propagation in calculations, providing fallbacks in JOINs.

⚠️ The JOIN Trap

-- This silently drops rows with NULL department!
SELECT * FROM employees
WHERE department != 'Sales';

If some employees have department = NULL (department unknown, not "no department"), this query excludes them — not because they're in Sales, but because NULL != 'Sales' evaluates to unknown, not true. This is one of the most common silent data-loss bugs in real pipelines.

-- Correct version, explicitly including NULLs if intended
SELECT * FROM employees
WHERE department != 'Sales' OR department IS NULL;

🔍 NULL in Arithmetic

SELECT salary + bonus AS total_pay FROM employees;
-- If bonus is NULL, total_pay is NULL — even though salary has a real value

Any arithmetic operation involving NULL produces NULL. If you want NULL bonuses treated as zero, you need to be explicit:

SELECT salary + COALESCE(bonus, 0) AS total_pay FROM employees;

⚠️ Common Mistakes

• Using = NULL instead of IS NULL — the single most common NULL-related bug
• Assuming COUNT(*) and COUNT(column) return the same number
• Not accounting for NULL in != or NOT IN filters, silently dropping legitimate rows
• Forgetting that arithmetic with NULL produces NULL, not zero

🔗 Related Posts

• SQL WHERE vs HAVING: When to Use Each
• SQL JOIN Types Explained (INNER, LEFT, RIGHT, FULL) with Examples