IS NULL and IS NOT NULL

IS NULL and IS NOT NULL

NULL represents a missing or unknown value. You can't compare NULL with = or <>.

💡 NULL is not zero, not empty string, not false — it means 'unknown' or 'no value'.

Checking for NULL

-- Find customers without an email
SELECT * FROM customers WHERE email IS NULL;

-- Find customers who have an email
SELECT * FROM customers WHERE email IS NOT NULL;

Why Not = NULL?

SELECT * FROM customers WHERE email = NULL;   -- Returns NO rows!
SELECT * FROM customers WHERE email IS NULL;  -- Correct way

⚠️ NULL = NULL is not TRUE — it's UNKNOWN. That's why you must use IS NULL instead of = NULL.

NULL in Calculations

-- Any arithmetic with NULL results in NULL
SELECT
    price,
    discount,
    price - COALESCE(discount, 0) AS final_price  -- Replace NULL with 0
FROM products;

NULL in Aggregations

SELECT COUNT(*) FROM employees;       -- Counts ALL rows
SELECT COUNT(email) FROM employees;   -- Counts non-NULL emails only
SELECT AVG(salary) FROM employees;    -- Ignores NULL salaries

✅ Key Takeaways

1. NULL means unknown or no value
2. Use IS NULL and IS NOT NULL to check for NULL
3. NULL in calculations produces NULL
4. Use COALESCE to handle NULL values
5. Aggregates (except COUNT) ignore NULLs