NULL Handling and Conditionals: The Mystery of the Empty Box 📦

Imagine you have a toy box. Sometimes it has toys inside, sometimes it’s completely empty. But here’s the tricky part — an empty box is NOT the same as “no box at all.” In SQL, we call this mysterious empty state NULL.

🎭 The Story of NULL: The “I Don’t Know” Answer

Picture this: You’re a detective asking questions about a suspect.

• “What’s their name?” → “Alex”
• “What’s their age?” → “25”
• “What’s their phone number?” → “Umm… we don’t know” 🤷

That “we don’t know” is NULL. It’s not zero. It’s not empty text. It’s the absence of any value — like a blank space in your detective notebook.

🔍 IS NULL and IS NOT NULL: Finding the Empty Boxes

The Problem

You can’t find empty boxes by asking “Is this box equal to nothing?”

-- ❌ This DOESN'T work!
SELECT * FROM contacts
WHERE phone = NULL;

Why? Because NULL means “unknown,” and you can’t compare something to an unknown!

The Solution

Use special detective phrases:

-- ✅ Find people with NO phone
SELECT name FROM contacts
WHERE phone IS NULL;

-- ✅ Find people WITH a phone
SELECT name FROM contacts
WHERE phone IS NOT NULL;

Simple Example

Imagine a class attendance sheet:

-- Who forgot their excuse note?
SELECT Student FROM attendance
WHERE excuse_note IS NULL;
-- Result: Liam

⚠️ NULL Comparison Behavior: The Weird Math

Here’s where NULL gets really strange. Watch this:

-- Is NULL equal to NULL?
NULL = NULL   → Unknown (not TRUE!)

-- Is NULL not equal to NULL?
NULL <> NULL  → Unknown (not TRUE!)

-- Is 5 greater than NULL?
5 > NULL      → Unknown

🧠 Why This Happens

Think of two mystery boxes you can’t open:

• Box A: ???
• Box B: ???

Can you say they’re the same? No! You don’t know what’s inside!

The Three-Valued Logic

SQL uses three answers instead of two:

1. TRUE ✅
2. FALSE ❌
3. UNKNOWN ❓ (when NULL is involved)

-- This returns NO rows (not what you expect!)
SELECT * FROM products
WHERE price = NULL;

-- Because NULL = NULL is UNKNOWN,
-- not TRUE!

🔧 COALESCE: The Backup Plan Function

COALESCE is like having backup plans. It checks each value and returns the first non-NULL one.

How It Works

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

It goes left to right and picks the first “real” answer.

Real Example

SELECT name,
  COALESCE(nickname, first_name, 'Friend')
  AS what_to_call_them
FROM users;

🎯 Common Uses

-- Show "N/A" when phone is missing
SELECT name,
  COALESCE(phone, 'N/A') AS phone
FROM contacts;

-- Use 0 when discount is NULL
SELECT product,
  price - COALESCE(discount, 0) AS final
FROM products;

🔄 NULLIF: The “Make It Disappear” Function

NULLIF does the opposite — it creates NULL when two values match.

How It Works

NULLIF(value1, value2)
-- If value1 = value2, return NULL
-- Otherwise, return value1

Why Use It?

Avoid divide-by-zero errors!

-- ❌ This crashes if views = 0
SELECT clicks / views FROM stats;

-- ✅ This returns NULL instead
SELECT clicks / NULLIF(views, 0)
FROM stats;

Simple Example

SELECT product,
  total / NULLIF(sold, 0) AS ratio
FROM inventory;

🎯 CASE Expression: The Decision Maker

CASE is like a smart robot that makes decisions:

“IF this is true, do this. ELSE IF that is true, do that. OTHERWISE, do something else.”

Basic Structure

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Real Example: Grade Calculator

SELECT student,
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
  END AS grade
FROM exams;

⚔️ Simple CASE vs Searched CASE

There are two ways to write CASE expressions:

1️⃣ Simple CASE (Compare one value)

Like a light switch with labels:

CASE column_name
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default
END

Example:

SELECT day_number,
  CASE day_number
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednesday'
    ELSE 'Other Day'
  END AS day_name
FROM calendar;

2️⃣ Searched CASE (Complex conditions)

Like a detective checklist:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default
END

Example:

SELECT product, price,
  CASE
    WHEN price > 100 THEN 'Expensive'
    WHEN price > 50 THEN 'Medium'
    WHEN price > 0 THEN 'Cheap'
    ELSE 'Free!'
  END AS category
FROM products;

🆚 When to Use Which?

🎨 Putting It All Together

Here’s a real-world query using everything:

SELECT
  name,
  COALESCE(email, phone, 'No Contact')
    AS contact_info,
  CASE
    WHEN age IS NULL THEN 'Unknown Age'
    WHEN age < 18 THEN 'Minor'
    WHEN age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group,
  salary / NULLIF(hours, 0) AS hourly_rate
FROM employees
WHERE status IS NOT NULL;

🧠 Quick Memory Tricks

graph TD
    A[Got NULL?] --> B{What do you need?}
    B --> C[Find NULLs?]
    B --> D[Replace NULL?]
    B --> E[Create NULL?]
    B --> F[Make decisions?]
    C --> G[IS NULL / IS NOT NULL]
    D --> H[COALESCE]
    E --> I[NULLIF]
    F --> J[CASE Expression]

Remember This:

• IS NULL = “Is the box empty?”
• COALESCE = “Use backup if empty”
• NULLIF = “Make it empty if matching”
• CASE = “If this, then that”

🎉 You Did It!

You now understand the mysterious world of NULL in SQL! Remember:

1. NULL means “I don’t know” — not zero, not empty
2. Use IS NULL to find missing values
3. COALESCE gives you backup plans
4. NULLIF prevents division disasters
5. CASE makes smart decisions

Now go handle those NULLs like a pro! 🚀