Working with NULL Values

NULL is one of the most misunderstood concepts in SQL—and one of the most important! It's not zero, not an empty string, and not a space. NULL means "unknown" or "missing data." Understanding how NULL works is crucial because it behaves differently than any other value in SQL. Get this wrong, and your queries will silently return incorrect results!

📚 What You'll Learn

• What NULL actually means in SQL (it's not what you think!)
• How NULL behaves differently in comparisons
• Why = NULL and <> NULL never work
• How to check for NULL properly using IS NULL
• How to check for NOT NULL (non-missing data)
• How NULL interacts with WHERE clauses
• How NULL affects aggregate functions (COUNT, SUM, AVG)
• Using COALESCE to replace NULL with default values
• Common mistakes beginners make (and how to avoid them)
• Real-world scenarios where NULL appears
• Hands-on practice exercises

1. What is NULL? (The Core Concept)

NULL represents the absence of a value. It's not:

• ❌ Not zero (0)
• ❌ Not an empty string ('')
• ❌ Not a space (' ')
• ❌ Not false

NULL literally means "I don't know" or "this information is missing."

Example Table with NULL Values

Notice Bob has no phone number (NULL), Carol has no email and no graduation year (both NULL). This is completely normal—databases often have missing information!

2. Why = NULL and <> NULL Never Work

This is where NULL gets tricky. In SQL logic, NULL equals nothing—not even itself!

The Wrong Way (Doesn't Work!)

-- ❌ WRONG: This returns ZERO rows, even though Bob's phone IS NULL!
SELECT name, phone
FROM students
WHERE phone = NULL;

Result: Returns 0 rows (nothing!)

Understanding NULL Logic

Think about it philosophically:

This is why NULL = NULL evaluates to NULL (unknown), not TRUE. And in a WHERE clause, NULL is treated as FALSE, so the row is excluded!

The Same Problem with <> (Not Equal)

-- ❌ ALSO WRONG: This also returns nothing!
SELECT name, phone
FROM students
WHERE phone <> NULL;  -- Doesn't work!

Same logic: "Is unknown not equal to unknown?" → Unknown! Not TRUE, so no rows returned.

3. The Right Way to Check for NULL

SQL has special operators specifically for NULL: IS NULL and IS NOT NULL

Finding NULL Values

-- ✅ CORRECT: Find students with no phone number
SELECT name, phone
FROM students
WHERE phone IS NULL;

Result:

✅ Success! We found Bob, whose phone number is NULL.

Finding Non-NULL Values (Data That Exists)

-- ✅ CORRECT: Find students who DID provide a phone number
SELECT name, phone
FROM students
WHERE phone IS NOT NULL;

Result:

✅ Returns everyone who has a phone number (excluding Bob).

4. How NULL Interacts with WHERE Clauses

NULL can create unexpected results in WHERE clauses. Let's see some surprising behaviors:

Example: Students Graduating Before 2025

SELECT name, graduation_year
FROM students
WHERE graduation_year < 2025;

Result:

Notice: Carol (who has NULL graduation_year) is NOT included!

Including NULL in Your Results

-- Include students graduating before 2025 OR with unknown graduation year
SELECT name, graduation_year
FROM students
WHERE graduation_year < 2025 OR graduation_year IS NULL;

Now Carol (with NULL) would be included!

5. How NULL Affects Aggregate Functions

Aggregate functions (COUNT, SUM, AVG, MIN, MAX) handle NULL in specific ways:

COUNT Behavior with NULL

SELECT COUNT(*) 
FROM students;

SELECT COUNT(phone) 
FROM students;

SUM, AVG, MIN, MAX with NULL

These functions ignore NULL values completely:

-- Assume we have a grades column with some NULLs
SELECT 
    COUNT(*) as total_students,           -- Counts all rows
    COUNT(grade) as students_with_grades, -- Counts non-NULL grades
    AVG(grade) as average_grade           -- Averages only non-NULL grades
FROM students;

If 3 students have grades (85, 90, 75) and 1 has NULL:

• COUNT(*) = 4 (all students)
• COUNT(grade) = 3 (students with grades)
• AVG(grade) = 83.33 (average of 85, 90, 75 — NULL ignored)

6. Using COALESCE to Handle NULL

COALESCE returns the first non-NULL value from a list. It's perfect for replacing NULL with a default value!

Basic Syntax

COALESCE(value1, value2, value3, ...)

Returns the first value that's not NULL.

Example: Providing Default Values

SELECT 
    name,
    COALESCE(phone, 'No phone provided') AS phone_display,
    COALESCE(email, 'No email on file') AS email_display
FROM students;

Result:

Instead of showing NULL (which looks unprofessional), we display user-friendly messages!

COALESCE with Multiple Fallbacks

SELECT 
    name,
    COALESCE(mobile_phone, home_phone, work_phone, 'No contact number') AS contact
FROM students;

Returns the first non-NULL phone number, or "No contact number" if all are NULL.

7. Common Beginner Mistakes

Mistake #1: Using = or <> with NULL

-- ❌ WRONG
WHERE phone = NULL
WHERE email <> NULL

-- ✅ CORRECT
WHERE phone IS NULL
WHERE email IS NOT NULL

Mistake #2: Forgetting NULL Excludes Rows

-- ❌ INCOMPLETE: Misses students with NULL graduation_year
SELECT name FROM students WHERE graduation_year >= 2024;

-- ✅ BETTER: Explicitly decide what to do with NULL
SELECT name FROM students 
WHERE graduation_year >= 2024 OR graduation_year IS NULL;

Mistake #3: Treating NULL as Zero or Empty String

-- ❌ WRONG: NULL is NOT zero
WHERE age = 0  -- Won't find NULL ages

-- ❌ WRONG: NULL is NOT empty string
WHERE email = ''  -- Won't find NULL emails

-- ✅ CORRECT
WHERE age IS NULL
WHERE email IS NULL

Mistake #4: Not Understanding COUNT Differences

-- These give DIFFERENT results!
SELECT COUNT(*) FROM students;      -- Counts all rows (4)
SELECT COUNT(phone) FROM students;  -- Counts non-NULL phones (3)

Mistake #5: Forgetting NULL in Calculations

-- Any math with NULL returns NULL!
SELECT 10 + NULL;     -- Returns NULL (not 10!)
SELECT 5 * NULL;      -- Returns NULL (not 0!)
SELECT NULL / 2;      -- Returns NULL

Use COALESCE to handle NULL in calculations:

-- Treat NULL as 0 in calculations
SELECT COALESCE(bonus, 0) + salary AS total_compensation
FROM employees;

8. Real-World Scenarios Where NULL Appears

• Optional fields: Middle name, phone number, apartment number
• Future dates: Graduation date for current students
• Pending data: Grade not yet assigned, order not yet shipped
• Inapplicable data: Spouse name for unmarried person
• Unknown information: Birth date, original purchase price
• Partial records: Incomplete form submissions

9. 🎯 Hands-On Practice Exercises

SELECT name, email
FROM students
WHERE email IS NULL;

SELECT name, phone
FROM students
WHERE phone IS NOT NULL;

SELECT name, graduation_year
FROM students
WHERE graduation_year = 2024 OR graduation_year IS NULL;

SELECT 
    name,
    COALESCE(phone, 'Not Provided') AS phone_display
FROM students;

SELECT 
    COUNT(*) AS total_students,
    COUNT(phone) AS students_with_phones,
    COUNT(*) - COUNT(phone) AS students_without_phones
FROM students;

SELECT name, email, phone
FROM students
WHERE email IS NOT NULL AND phone IS NOT NULL;

📝 Key Takeaways

• NULL means "unknown" or "missing" — not zero, not empty string
• Never use = NULL or <> NULL — they don't work!
• Always use IS NULL to check for NULL values
• Always use IS NOT NULL to check for existing values
• NULL is excluded from all comparison operations (<, >, <=, >=)
• COUNT(*) counts all rows, COUNT(column) excludes NULL
• SUM, AVG, MIN, MAX ignore NULL values automatically
• Use COALESCE to replace NULL with default values
• Any math operation with NULL returns NULL
• NULL = NULL evaluates to NULL (unknown), not TRUE