SQL NULL Handling ⚪¶
Mentor's Note: NULL is the most misunderstood concept in SQL. NULL does not mean zero
0. It does not mean an empty string''. NULL means "I don't know the value yet." It is a state of unknown data. 💡
🌟 The Scenario: The Missing Phone Number 📱¶
Imagine you are filling out a physical registration form. - Zero (0): You write '0' in the phone number box. (Valid, but weird!). - Empty (''): You rub out the box so it's blank. (You actively decided it's blank). - NULL: You leave the box completely untouched. We don't know if you have a phone or not.
🎨 Visual Logic: The Comparison¶
| Value | Box Analogy | Logic |
|---|---|---|
| 0 | A box with a '0' written inside. | Numeric Value |
| ' ' | A box with an empty piece of paper. | Text Value |
| NULL | No box exists. | Unknown State |
💻 1. The "IS NULL" Syntax¶
You cannot use = with NULL because NULL is not equal to anything (not even itself!).
-- ❌ WRONG (Returns 0 results)
SELECT * FROM students WHERE email = NULL;
-- ✅ CORRECT
SELECT * FROM students WHERE email IS NULL;
💻 2. The "IS NOT NULL" Syntax¶
Use this to find records that have a value.
-- Scenario: Find all students who HAVE provided a phone number
SELECT name FROM students
WHERE phone IS NOT NULL;
💻 3. Dealing with NULLs (Default Values) 🛠️¶
Sometimes you want to show "N/A" instead of a blank NULL space in your reports.
💡 Interview Tip 👔¶
"Interviewers love to ask: 'What is 10 + NULL?' The answer is NULL. Anything combined with 'Unknown' stays 'Unknown'. This is why handling NULLs is critical for accurate math!"