Working with NULL Values

# CHAPTER 11

Working with NULL Values #

1. Introduction #

2. Learning Objectives #

• Define the concept of NULL in relational databases.

• Understand why NULL cannot be compared using = or !=.

• Filter for missing data using the IS NULL operator.

• Filter out missing data using the IS NOT NULL operator.

• Use the COALESCE() function to safely replace NULL values in output.

3. What exactly is NULL? #

• 0 means the value is zero.

• "" means the value is empty text.

• NULL means we have absolutely no idea what the value is.

*Example:* If a new employee is hired but hasn't negotiated their salary yet, their salary column is NULL.

4. The Mathematical Trap of NULL #

-- DANGER: This will return ZERO rows, even if John's phone is NULL!
SELECT * FROM users 
WHERE phone_number = NULL;

-- DANGER: This will also return ZERO rows!
SELECT * FROM users 
WHERE phone_number != NULL;

*(Rule: You can NEVER use = or != with NULL!)*

5. The IS NULL Operator #

-- Correct! Find all users who have not provided a phone number
SELECT name, email FROM users 
WHERE phone_number IS NULL;

-- Find orders that have not been shipped yet
SELECT * FROM orders 
WHERE shipped_date IS NULL;

6. The IS NOT NULL Operator #

-- Find all users who HAVE provided a valid phone number
SELECT name, phone_number FROM users 
WHERE phone_number IS NOT NULL;

7. Safely Handling NULLs in Math (COALESCE) #

To fix this, we use the incredibly powerful COALESCE() function. It takes a list of values and returns the first one that is NOT NULL.

-- If bonus is NULL, COALESCE replaces it with a 0 on the fly!
SELECT 
    name, 
    salary + COALESCE(bonus, 0) AS 'Total Compensation' 
FROM employees;

8. Mini Project: Auditing the Database #

-- 1. Find all broken user profiles (missing an email address)
SELECT user_id, name FROM users 
WHERE email IS NULL;

-- 2. Find all products missing a price (so we don't accidentally give them away!)
SELECT product_id, product_name FROM products 
WHERE price IS NULL;

-- 3. Display the catalog, but if a product lacks a description, insert a default message
SELECT 
    product_name, 
    COALESCE(description, 'No description available for this item.') AS description 
FROM products;

9. Common Mistakes #

• Confusing NULL with Zero: If a customer's balance is 0, they owe you nothing. If their balance is NULL, the banking system doesn't know their account state. Never use them interchangeably.

• Using IS NULL in INSERT Statements: When inserting data, you don't use IS NULL. You simply type the unquoted keyword NULL. Example: INSERT INTO users (name, phone) VALUES ('John', NULL);

10. Best Practices #

• NOT NULL Constraints: When architecting a database schema (Chapter 4 & 14), you can append NOT NULL to a column definition (e.g., email VARCHAR(100) NOT NULL). This physically prevents the database from ever accepting a row without an email, guaranteeing data integrity.

11. Exercises #

1. 1. Why does the query WHERE age = NULL fail to return results?

1. 2. What SQL operator is explicitly required to filter out missing data?

12. SQL Challenges #

SELECT * FROM staff
WHERE department IS NOT NULL
AND manager_id IS NULL;

13. MCQ Quiz with Answers #

• Q: A financial query calculating basesalary + annualbonus is returning blank rows for several employees. You discover those employees did not receive an annualbonus this year. Explain the mechanical trap of NULL math causing this error, and how the COALESCE() function solves it.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #