MySQL NULL Values

NULL Concepts

🔹 Understanding NULL

NULL is a special marker in SQL that indicates the absence of data. It's different from zero, empty strings, or spaces. NULL means "unknown" or "not applicable." You cannot use regular comparison operators like = or != with NULL values; instead, you must use IS NULL or IS NOT NULL operators.

-- ❌ Wrong: This won't work
SELECT * FROM users WHERE email = NULL;

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

-- Check for existing values
SELECT * FROM users WHERE email IS NOT NULL;

🔹 Testing for NULL Values

Use IS NULL to find records with missing data and IS NOT NULL to find records with existing data. These operators are essential for data validation and quality checks. They help you identify incomplete records, ensure data integrity, and filter results based on whether specific fields contain values or not.

-- Find users without phone numbers
SELECT name, phone FROM users 
WHERE phone IS NULL;

-- Find products with prices
SELECT product_name, price FROM products 
WHERE price IS NOT NULL;

-- Combine with other conditions
SELECT * FROM orders 
WHERE shipped_date IS NULL AND order_date < '2024-01-01';

🔹 IFNULL() Function

The IFNULL() function replaces NULL values with a specified default value. It takes two arguments: the column to check and the replacement value. This is useful for displaying user-friendly output or performing calculations where NULL values would cause problems. It ensures your queries always return meaningful data instead of NULL.

-- Replace NULL with default text
SELECT name, IFNULL(email, 'No email') AS email 
FROM users;

-- Replace NULL with zero for calculations
SELECT product_name, IFNULL(discount, 0) AS discount 
FROM products;

-- Use in calculations
SELECT name, price * IFNULL(quantity, 1) AS total 
FROM orders;

🔹 COALESCE() Function

COALESCE() returns the first non-NULL value from a list of expressions. It's more flexible than IFNULL() because it can check multiple columns or values. This function is perfect when you have backup fields or want to prioritize data from different sources, ensuring you always get a value when possible.

-- Return first available contact method
SELECT name, 
       COALESCE(mobile, home_phone, work_phone, 'No phone') AS contact 
FROM users;

-- Use multiple fallback values
SELECT product_name, 
       COALESCE(sale_price, regular_price, 0) AS price 
FROM products;

-- Combine multiple columns
SELECT COALESCE(nickname, first_name, username) AS display_name 
FROM users;

🔹 NULL in Calculations

Any arithmetic operation with NULL results in NULL. This can cause unexpected results in calculations and aggregations. Always handle NULL values explicitly in mathematical expressions using IFNULL() or COALESCE() to ensure accurate calculations. Understanding NULL behavior in math operations prevents data analysis errors and ensures reliable query results.

-- ❌ Problem: NULL in calculation returns NULL
SELECT price * quantity AS total FROM orders;
-- If quantity is NULL, total will be NULL

-- ✅ Solution: Handle NULL values
SELECT price * IFNULL(quantity, 0) AS total FROM orders;

-- Safe division
SELECT total / IFNULL(count, 1) AS average FROM stats;

🔹 NULL Best Practices

Follow these guidelines when working with NULL values to maintain data quality and avoid common pitfalls. Design your database schema carefully to decide which fields should allow NULL and which should require values. Proper NULL handling improves data integrity, query reliability, and application robustness throughout your database system.