SQL NULL Handling

In SQL, NULL represents a missing, undefined, or unknown value — it’s not the same as an empty string or zero. Understanding how to work with NULL is crucial to writing accurate queries.

🔹 What is NULL?

• NULL means no value or unknown.
• It’s a placeholder — not equal to anything, even another NULL.

🔹 Checking for NULL

Use IS NULL and IS NOT NULL — not = or !=.

-- Find rows where email is missing
SELECT * FROM users WHERE email IS NULL;

-- Find rows where email is present
SELECT * FROM users WHERE email IS NOT NULL;

🔹 NULL and Comparison Operators

-- This will NOT return any results
SELECT * FROM users WHERE email = NULL;  -- ❌ Wrong

-- Use IS NULL instead
SELECT * FROM users WHERE email IS NULL;  -- ✅ Correct

🔹 Handling NULL in Results

Use functions to deal with NULL values:

-- Replace NULL with a default value
SELECT COALESCE(phone, 'Not Provided') AS phone_display FROM users;

-- MySQL alternative: IFNULL(phone, 'N/A')
-- SQL Server alternative: ISNULL(phone, 'N/A')

🔹 NULL in Aggregates

• Aggregate functions like COUNT, AVG, SUM ignore NULLs (except COUNT(*) includes them).

SELECT COUNT(email) FROM users;         -- Ignores NULLs
SELECT COUNT(*) FROM users;             -- Counts all rows

🧠 Quick Recap

💡 Always be mindful of NULLs — they can silently break logic if not handled properly.