COALESCE and NULLIF

The COALESCE and NULLIF

Handle NULL values gracefully with COALESCE and NULLIF.

💡 COALESCE and NULLIF are essential tools for handling conditional logic and NULL values in SQL.

COALESCE Syntax

-- Simple CASE (exact match)
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

-- Searched CASE (conditions) -- COALESCE returns first non-NULL value
COALESCE(value1, value2, ..., default)
    
    
    
END

Example

SELECT
    first_name,
    salary,
    CASE
        WHEN salary >= 80000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level,
    COALESCE(commission, 0) AS commission_fixed
FROM employees;

NULLIF Example

-- Prevent division by zero
SELECT total / NULLIF(quantity, 0) AS unit_price FROM order_items;

✅ Key Takeaways

1. COALESCE returns the first non-NULL value from a list
2. NULLIF returns NULL when two values are equal
3. CASE has two forms: simple (exact match) and searched (conditions)
4. NULLIF is great for preventing division by zero
5. Use COALESCE instead of ISNULL for better portability between databases