How to Handle NULL Values in MySQL

Key Insights

NULL represents the absence of a value and behaves fundamentally differently from empty strings or zero—you cannot use standard comparison operators like = or != to check for NULL values
Use IFNULL() for simple single-value substitutions and COALESCE() when you need multiple fallback options, while the NULL-safe equality operator <=> handles comparisons where both sides might be NULL
Aggregate functions like COUNT(), SUM(), and AVG() silently exclude NULL values from calculations, which can lead to unexpected results if you’re not aware of this behavior

Understanding NULL in MySQL

NULL is not a value—it’s a marker indicating the absence of a value. This fundamental concept trips up many developers because NULL behaves completely differently from what you might expect based on other programming languages or database systems.

A NULL is not the same as an empty string (''), zero (0), or false. An empty string is a string with zero length. Zero is a numeric value. NULL means “unknown” or “not applicable.” This distinction matters enormously when you’re writing queries.

Consider this demonstration:

CREATE TABLE value_comparison (
    id INT PRIMARY KEY,
    null_value VARCHAR(50),
    empty_value VARCHAR(50),
    zero_value INT
);

INSERT INTO value_comparison VALUES 
    (1, NULL, '', 0),
    (2, 'actual value', 'actual value', 100);

-- These comparisons behave differently
SELECT 
    id,
    null_value = NULL AS null_equals_null,           -- Returns NULL, not TRUE!
    empty_value = '' AS empty_equals_empty,          -- Returns TRUE
    zero_value = 0 AS zero_equals_zero,              -- Returns TRUE
    null_value IS NULL AS null_is_null,              -- Returns TRUE
    LENGTH(null_value) AS null_length,               -- Returns NULL
    LENGTH(empty_value) AS empty_length              -- Returns 0
FROM value_comparison;

The most important takeaway: NULL = NULL doesn’t return true or false—it returns NULL. In boolean contexts, NULL is treated as false, which means conditions like WHERE column = NULL will never match any rows, even rows where that column is NULL.

Checking for NULL Values

Because NULL represents an unknown value, you can’t compare it using standard operators. MySQL provides specific operators for NULL checking: IS NULL and IS NOT NULL.

Here’s the right and wrong way to check for NULL values:

-- WRONG: This will never return any rows
SELECT * FROM users WHERE email = NULL;

-- WRONG: This also won't work as expected
SELECT * FROM users WHERE email != NULL;

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

-- CORRECT: Use IS NOT NULL
SELECT * FROM users WHERE email IS NOT NULL;

-- Combining with other conditions
SELECT * FROM orders 
WHERE shipped_date IS NULL 
  AND order_date < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Finding rows with any NULL values across columns
SELECT * FROM products
WHERE description IS NULL 
   OR price IS NULL 
   OR category_id IS NULL;

The reason standard comparison operators fail is logical: if NULL means “unknown,” then asking “does this unknown value equal this other unknown value?” can only be answered with “unknown” (which MySQL represents as NULL). You need explicit NULL-checking operators to get definitive true/false results.

NULL-Safe Functions and Operators

MySQL provides several functions and operators to handle NULL values gracefully in your queries.

IFNULL(expression, alternative) returns the alternative value if the expression is NULL:

SELECT 
    product_name,
    IFNULL(discount_price, regular_price) AS final_price
FROM products;

COALESCE(value1, value2, ..., valueN) returns the first non-NULL value from a list:

SELECT 
    customer_name,
    COALESCE(mobile_phone, home_phone, work_phone, 'No phone') AS contact_number
FROM customers;

-- COALESCE is more flexible than IFNULL for multiple fallbacks
SELECT 
    order_id,
    COALESCE(
        express_shipping_date,
        standard_shipping_date,
        estimated_shipping_date,
        order_date
    ) AS ship_date
FROM orders;

The NULL-safe equality operator <=> returns true if both operands are NULL or if they’re equal:

-- Regular equality fails with NULL
SELECT NULL = NULL;  -- Returns NULL

-- NULL-safe equality works
SELECT NULL <=> NULL;  -- Returns 1 (TRUE)

-- Practical use case: finding changed values
SELECT * FROM user_updates
WHERE old_email <=> new_email;  -- Finds unchanged emails, including NULL to NULL

-- This is equivalent to but more concise than:
SELECT * FROM user_updates
WHERE (old_email = new_email) 
   OR (old_email IS NULL AND new_email IS NULL);

Handling NULL in Aggregations and JOINs

Aggregate functions have specific NULL-handling behavior that you must understand to avoid incorrect results.

CREATE TABLE sales (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),
    commission DECIMAL(10,2)
);

INSERT INTO sales VALUES 
    (1, 100.00, 10.00),
    (2, 200.00, NULL),
    (3, 150.00, NULL),
    (4, 300.00, 30.00);

-- COUNT(*) counts all rows
SELECT COUNT(*) FROM sales;  -- Returns 4

-- COUNT(column) counts non-NULL values only
SELECT COUNT(commission) FROM sales;  -- Returns 2

-- SUM, AVG, MIN, MAX ignore NULL values
SELECT 
    SUM(commission) AS total_commission,    -- 40.00 (only sums non-NULL)
    AVG(commission) AS avg_commission,      -- 20.00 (40/2, not 40/4)
    COUNT(*) AS total_rows,                 -- 4
    COUNT(commission) AS rows_with_commission  -- 2
FROM sales;

This behavior can cause problems. If you’re calculating average commission, you might expect NULL values to be treated as zero, but they’re excluded entirely. To include them as zero:

SELECT AVG(IFNULL(commission, 0)) AS avg_commission_with_nulls_as_zero
FROM sales;  -- Returns 10.00 (40/4)

JOINs introduce NULL values when there’s no match in the joined table:

SELECT 
    customers.name,
    orders.order_id,
    orders.total,
    IFNULL(orders.total, 0) AS total_with_default
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.order_id IS NULL;  -- Customers with no orders

Understanding this behavior is critical when working with outer joins, as unmatched rows will have NULL for all columns from the non-matching table.

Setting Default Values and Constraints

Preventing NULL values at the schema level is often better than handling them in queries. Use NOT NULL constraints and DEFAULT values strategically.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,                    -- Must have a value
    username VARCHAR(50) NOT NULL UNIQUE,
    bio TEXT,                                       -- NULL allowed
    is_active BOOLEAN NOT NULL DEFAULT TRUE,        -- Has default
    login_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

-- Adding NOT NULL to existing table (must handle existing NULLs first)
UPDATE products SET description = 'No description' WHERE description IS NULL;
ALTER TABLE products MODIFY description TEXT NOT NULL;

-- Adding DEFAULT value
ALTER TABLE products 
ALTER COLUMN stock_quantity SET DEFAULT 0;

-- Removing NOT NULL constraint
ALTER TABLE users MODIFY bio TEXT NULL;

When deciding whether to allow NULL, consider:

Allow NULL for truly optional data (middle name, secondary phone number)
Use NOT NULL with DEFAULT for values that should always exist but can have a sensible default (status flags, counters)
Use NOT NULL without DEFAULT for required data that must be explicitly provided (email, username)

Best Practices for NULL Management

Apply these patterns to handle NULL values effectively in production systems.

Use COALESCE in ORDER BY to control NULL sorting:

-- NULLs sort first by default in ascending order
SELECT * FROM products ORDER BY discount_price;

-- Put NULLs last
SELECT * FROM products 
ORDER BY COALESCE(discount_price, 999999);

-- Or use explicit NULL handling
SELECT * FROM products 
ORDER BY discount_price IS NULL, discount_price;

Handle NULL in conditional logic:

-- Find products that need attention
SELECT 
    product_id,
    name,
    CASE 
        WHEN stock_quantity IS NULL THEN 'Unknown stock'
        WHEN stock_quantity = 0 THEN 'Out of stock'
        WHEN stock_quantity < 10 THEN 'Low stock'
        ELSE 'In stock'
    END AS stock_status
FROM products;

Be careful with NULL in WHERE clauses:

-- This might not work as expected
SELECT * FROM orders WHERE discount_amount > 10;  -- Excludes NULL discounts

-- More explicit
SELECT * FROM orders 
WHERE IFNULL(discount_amount, 0) > 10;

-- Or be explicit about what you want
SELECT * FROM orders 
WHERE discount_amount > 10 
   OR discount_amount IS NULL;  -- Include unknowns if that's your intent

Index considerations:

MySQL indexes include NULL values (unlike some databases). A column with many NULL values might benefit from a partial index or a different schema design:

-- Instead of nullable flag with mostly NULL values
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NULL;

-- Consider a separate table or a NOT NULL column with default
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT FALSE;

Application-level handling:

Always handle NULL values in your application code when displaying data or performing calculations. Don’t rely solely on database defaults. Use your programming language’s NULL-coalescing operators or explicit checks to provide meaningful defaults to users.

The key to working with NULL in MySQL is remembering that NULL is not a value—it’s the absence of a value. Once you internalize this distinction and use the appropriate operators and functions, NULL handling becomes straightforward and predictable.