11. NULL Handling -- ISNULL, COALESCE, NULLIF

Level: Beginner

ℹ️ What You'll Learn

• What NULL means (empty, missing data)
• IS NULL and IS NOT NULL operators
• ISNULL function -- replace NULL with value
• COALESCE function -- first non-NULL value
• NULLIF function -- convert to NULL
• NULL in comparisons (pitfalls)
• NULL in calculations
• Common NULL mistakes

NULL = missing data. NULL is not zero, not empty string, not false. NULL is "unknown". This article explains handling NULL properly.

Why NULL Handling Matters for Backend Developers

APIs must handle missing data gracefully:

-- WITHOUT NULL handling
Student.ParentPhone = NULL
-- Backend: "Phone is null" -> Error or crashes

-- WITH NULL handling
ISNULL(Student.ParentPhone, 'Not provided')
-- Backend gets: 'Not provided' -> Clean handling

Proper NULL handling prevents "null reference" errors in APIs.

What is NULL?

NULL = missing, unknown, or not applicable.

Student table:
StudentId | Name        | DateOfBirth | ParentPhone
1         | Ravi Kumar  | 2010-05-15  | 9876543210
2         | Priya Sharma| NULL        | NULL           <- Missing data
3         | Arjun Reddy | 2009-03-20  | 8765432109

Student 2's DateOfBirth and ParentPhone are NULL (not provided yet).

Important: NULL != 0, NULL != '', NULL != false. NULL is "unknown".

IS NULL Operator

Check if value is NULL:

SELECT * FROM Student WHERE DateOfBirth IS NULL;

Result (students without date of birth):

StudentId | Name         | DateOfBirth | ParentPhone
2         | Priya Sharma | NULL        | NULL

Note: Cannot use = NULL. Must use IS NULL.

-- WRONG
SELECT * FROM Student WHERE DateOfBirth = NULL;
-- Returns NO rows (NULL = NULL is unknown in SQL)

-- RIGHT
SELECT * FROM Student WHERE DateOfBirth IS NULL;
-- Returns rows with NULL DateOfBirth

IS NOT NULL Operator

Check if value is NOT NULL:

SELECT * FROM Student WHERE DateOfBirth IS NOT NULL;

Result (students with date of birth):

StudentId | Name        | DateOfBirth | ParentPhone
1         | Ravi Kumar  | 2010-05-15  | 9876543210
3         | Arjun Reddy | 2009-03-20  | 8765432109

ISNULL Function

Replace NULL with default value:

SELECT 
    StudentId,
    Name,
    ISNULL(ParentPhone, 'Not Provided') AS Phone
FROM Student;

Result:

StudentId | Name         | Phone
1         | Ravi Kumar   | 9876543210
2         | Priya Sharma | Not Provided      <- NULL replaced
3         | Arjun Reddy  | 8765432109

ISNULL(column, replacement_value):

• If column is NULL, return replacement_value
• If column is NOT NULL, return column value

COALESCE Function

Return first non-NULL value:

SELECT 
    StudentId,
    Name,
    COALESCE(ParentPhone, Email, 'Contact Not Available') AS Contact
FROM Student;

Tries ParentPhone first. If NULL, tries Email. If still NULL, uses 'Contact Not Available'.

Useful for: Fallback values in order of preference.

NULLIF Function

Convert value to NULL (opposite of ISNULL):

SELECT 
    TeacherId,
    Name,
    NULLIF(Salary, 0) AS ActualSalary
FROM Teacher;

If Salary = 0, returns NULL. Otherwise returns Salary.

Use case: Hide default/placeholder values.

NULL in Comparisons

NULL comparisons are tricky:

-- Problem: NULL comparisons always unknown
SELECT * FROM Student WHERE ParentPhone = '9876543210';
-- Doesn't return rows where ParentPhone is NULL

SELECT * FROM Student WHERE ParentPhone != '9876543210';
-- Doesn't return rows where ParentPhone is NULL either!

-- Solution: Use IS NULL
SELECT * FROM Student WHERE ParentPhone IS NULL;

Rule: For NULL checks, always use IS NULL or IS NOT NULL, never = or !=.

NULL in Calculations

NULL + anything = NULL:

SELECT 
    StudentId,
    Fees,
    Discount,
    (Fees - Discount) AS NetFees
FROM Student;

If Discount is NULL:

StudentId | Fees | Discount | NetFees
1         | 50000| 5000     | 45000
2         | 50000| NULL     | NULL      <- NULL propagates!

Student 2's NetFees is NULL (because Discount is NULL).

Solution -- Use ISNULL:

SELECT 
    StudentId,
    Fees,
    Discount,
    (Fees - ISNULL(Discount, 0)) AS NetFees
FROM Student;

Result:

StudentId | Fees | Discount | NetFees
1         | 50000| 5000     | 45000
2         | 50000| NULL     | 50000    <- NULL treated as 0

NULL in Aggregate Functions

Aggregate functions (COUNT, SUM, AVG) ignore NULL:

SELECT 
    COUNT(*) AS TotalStudents,
    COUNT(ParentPhone) AS StudentsWithPhone,
    AVG(ISNULL(Discount, 0)) AS AvgDiscount
FROM Student;

• COUNT(*) = 3 (all rows)
• COUNT(ParentPhone) = 1 (only non-NULL phones)
• AVG with ISNULL = treats NULL as 0

---

Common NULL Mistakes

Mistake 1: Using = NULL in WHERE clause

Wrong:

SELECT * FROM Student WHERE ParentPhone = NULL;
-- Returns NO rows (even if NULLs exist)

SQL treats = NULL as unknown, not false. Returns nothing.

Result: Missing NULL rows.

Fix: Use IS NULL:

SELECT * FROM Student WHERE ParentPhone IS NULL;

Mistake 2: NULL in calculations without handling

Wrong:

SELECT 
    StudentId,
    Fees - Discount AS NetFees
FROM Student;
-- If Discount NULL, NetFees = NULL (unexpected)

Result: NULL propagation confuses backend.

Fix: Use ISNULL:

SELECT 
    StudentId,
    Fees - ISNULL(Discount, 0) AS NetFees
FROM Student;

Mistake 3: Not distinguishing NULL from empty string

Wrong:

CREATE TABLE Student
(
    ParentPhone VARCHAR(15)
);

INSERT INTO Student VALUES (NULL);      -- NULL
INSERT INTO Student VALUES ('');        -- Empty string

SELECT * WHERE ParentPhone IS NULL;
-- Returns only first row

NULL and '' are different!

Result: Logic errors.

Fix: Handle both:

SELECT * WHERE ParentPhone IS NULL OR ParentPhone = '';

Mistake 4: ISNULL in wrong order

Wrong:

SELECT ISNULL(ColumnA, ISNULL(ColumnB, 'Default'));
-- Works but hard to read

Better -- Use COALESCE:

SELECT COALESCE(ColumnA, ColumnB, 'Default');
-- Clearer intent

---

Best Practices for NULL Handling

1. Use IS NULL, not = NULL -- Always for NULL checks
2. Use COALESCE for multiple fallbacks -- Cleaner than nested ISNULL
3. Handle NULL in calculations -- Use ISNULL before math
4. Be explicit in SELECT -- Show NULL handling to readers
5. Use NOT NULL in design -- Only allow NULL when needed
6. Test NULL cases -- Unit tests for NULL scenarios
7. Document NULL semantics -- Comments explain why NULL allowed

---

🎯 Q1: What's the difference between NULL, 0, and empty string?

NULL:

ParentPhone = NULL  -- No value, unknown, missing

Zero:

Discount = 0        -- Valid value, zero discount

Empty string:

Email = ''          -- Valid value, empty text

All different. NULL means "no data yet".

SQL treats them differently:

WHERE ParentPhone IS NULL        -- Finds NULLs
WHERE Discount = 0               -- Finds zeros
WHERE Email = ''                 -- Finds empty strings

🎯 Q2: Why can't I use = NULL in WHERE?

NULL in SQL:

NULL = NULL         -- Unknown (not true, not false)
NULL = 'anything'   -- Unknown

SQL treats NULL comparisons as "unknown", not false.

Result:

SELECT * WHERE ParentPhone = NULL;
-- Where clause = unknown for ALL rows
-- Returns 0 rows

Must use IS NULL:

SELECT * WHERE ParentPhone IS NULL;
-- Correctly identifies NULL values

🎯 Q3: What's the difference between ISNULL and COALESCE?

ISNULL (SQL Server specific):

ISNULL(ParentPhone, 'Not Provided')
-- If NULL, return 'Not Provided'

One fallback value. Simple.

COALESCE (Standard SQL, works on all databases):

COALESCE(ParentPhone, Email, 'Contact Not Available')
-- Try ParentPhone, then Email, then default

Multiple fallbacks. More flexible.

Use COALESCE: Generally better (portable, multiple options).

🎯 Q4: Why does NULL + number = NULL?

NULL means "unknown":

Fees - Discount
50000 - 5000 = 45000     -- Clear

50000 - NULL = NULL      -- Unknown discount, unknown result

Math with unknown values = unknown result.

Solution -- Assume value:

Fees - ISNULL(Discount, 0)
50000 - 0 = 50000        -- Assume no discount

Now result is clear.

🎯 Q5: How do aggregates (SUM, COUNT, AVG) handle NULL?

They ignore NULL:

SELECT SUM(Discount) FROM Student;
-- If one Discount is NULL, SUM ignores it
-- Only sums non-NULL values

Example:

Discount: 1000, 2000, NULL, 1500
SUM = 1000 + 2000 + 1500 = 4500  (ignores NULL)
AVG = 4500 / 3 = 1500  (divides by 3, not 4)

To include NULL as 0:

SELECT SUM(ISNULL(Discount, 0)) FROM Student;
-- NULL treated as 0
-- Sum = 1000 + 2000 + 0 + 1500 = 4500

🎯 Q6: Should I allow NULL or force NOT NULL in design?

NOT NULL (safe):

Name NVARCHAR(100) NOT NULL

Forces value. No nulls. Simpler logic.

NULL (flexible):

DateOfBirth DATE NULL  -- Optional

Allows missing data. More realistic.

Best practice:

• NOT NULL for required data (Name, RollNumber)
• NULL for optional data (DateOfBirth, ParentPhone)
• Use NOT NULL by default, allow NULL only if needed

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on NULL handling in SQL Server with ISNULL and COALESCE. Try these prompts:

• "Explain NULL vs 0 vs empty string with examples"
• "Why can't I use = NULL in WHERE? Show correct approach"
• "When should I use ISNULL vs COALESCE? Show both"
• "How do I handle NULL in calculations like Fees - Discount?"
• "Quiz me: ask 5 questions about NULL, IS NULL, ISNULL, COALESCE"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

12. Aggregate Functions -- COUNT, SUM, AVG, MIN, MAX ->