MySQL Null Functions

Key Null Functions

SELECT 
    name,
    IFNULL(phone, 'No Phone')
FROM customers;

SELECT 
    COALESCE(phone, mobile, email)
FROM contacts;

SELECT 
    NULLIF(quantity, 0)
FROM inventory;

SELECT * FROM users
WHERE email IS NULL;

🔹 IFNULL Function

IFNULL takes two arguments and returns the first one if it's not NULL, otherwise returns the second. It's the simplest way to provide a default value when data is missing, perfect for displaying user-friendly text instead of empty fields.

-- Replace NULL phone numbers with a message
SELECT 
    customer_name,
    email,
    IFNULL(phone, 'Not Provided') AS phone_number
FROM customers;

-- Use 0 for NULL quantities
SELECT 
    product_name,
    IFNULL(stock_quantity, 0) AS available_stock
FROM inventory;

🔹 COALESCE Function

COALESCE accepts multiple arguments and returns the first non-NULL value. It's more flexible than IFNULL because you can check several columns in order, making it ideal for finding the best available contact method or fallback values across multiple fields.

-- Find the first available contact method
SELECT 
    customer_name,
    COALESCE(mobile_phone, home_phone, work_phone, 'No Contact') AS contact_number
FROM customers;

-- Use the first available address
SELECT 
    name,
    COALESCE(shipping_address, billing_address, 'Address Unknown') AS address
FROM orders;

🔹 NULLIF Function

NULLIF compares two values and returns NULL if they're equal, otherwise returns the first value. This is useful for converting specific values to NULL, like turning zero into NULL to avoid division errors or filtering out placeholder values from calculations.

-- Convert 0 to NULL to avoid division by zero
SELECT 
    product_name,
    total_sales / NULLIF(quantity_sold, 0) AS average_price
FROM sales_report;

-- Convert empty strings to NULL
SELECT 
    customer_name,
    NULLIF(middle_name, '') AS middle_name
FROM customers;

🔹 IS NULL and IS NOT NULL

Use IS NULL to find rows where a column has no value, and IS NOT NULL to find rows with values. These are essential for filtering data, finding incomplete records, or ensuring required fields are populated before processing.

-- Find customers without email addresses
SELECT 
    customer_id,
    customer_name,
    phone
FROM customers
WHERE email IS NULL;

-- Find completed orders (with delivery date)
SELECT 
    order_id,
    customer_name,
    delivery_date
FROM orders
WHERE delivery_date IS NOT NULL;

-- Find products with missing descriptions
SELECT product_name, price
FROM products
WHERE description IS NULL OR description = '';

🔹 Practical Examples

Real-world scenarios for handling NULL values in your database. These examples show how to calculate with NULL-safe operations, display user-friendly messages, handle optional fields, and ensure your reports and calculations work correctly even with missing data.

-- Calculate total with optional discount
SELECT 
    product_name,
    price,
    IFNULL(discount, 0) AS discount,
    price - IFNULL(discount, 0) AS final_price
FROM products;

-- Display full name with optional middle name
SELECT 
    CONCAT(
        first_name, 
        ' ',
        COALESCE(middle_name, ''),
        ' ',
        last_name
    ) AS full_name
FROM employees;

-- Safe division avoiding divide by zero
SELECT 
    department,
    total_sales,
    employee_count,
    total_sales / NULLIF(employee_count, 0) AS sales_per_employee
FROM department_stats;

-- Find incomplete customer profiles
SELECT 
    customer_id,
    customer_name,
    CASE
        WHEN email IS NULL THEN 'Missing Email'
        WHEN phone IS NULL THEN 'Missing Phone'
        WHEN address IS NULL THEN 'Missing Address'
        ELSE 'Complete'
    END AS profile_status
FROM customers;