Skip to content

Oracle IS NULL & IS NOT NULL ⚪

Prerequisites: WHERE Clause

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