Oracle IS NULL & IS NOT NULL ⚪

Mentor's Note: NULL does not mean "Zero" or "Blank Space". It means "I don't know". Because it's unknown, you can't compare it using =. You must use the special IS NULL test. 💡

---

🌟 The Scenario: The Missing Phone Number 📱

You are looking at a stack of registration forms.

• Some people left the "Phone" box completely empty.
• The Question: "Who didn't give us their number?"
• The Logic: You don't look for people whose phone is "Zero"; you look for those whose phone is Missing.

---

💻 1. Syntax & Examples

-- ❌ This will return ZERO results (even if nulls exist)
SELECT * FROM employees WHERE manager_id = NULL;

-- ✅ This is the correct way
SELECT * FROM employees WHERE manager_id IS NULL;

Finding Records with Values

SELECT * FROM employees 
WHERE commission_pct IS NOT NULL;

---

🏗️ Architect's Note: NULL Math 🛡️

In Oracle, any math operation with NULL results in NULL.

• 100 + NULL = NULL
• String || NULL = String (Note: Oracle is unique here; it treats empty strings as NULLs!)
• Architect's Tip: Use NVL(column, 0) to replace NULLs with a default value before doing math to prevent your reports from showing blank results.

---

📈 Learning Path

• ← Back: LIKE Operator
• Next: Row Limiting (FETCH) →