COALESCE

Category: Conditional Expressions

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

COALESCE returns the first non-NULL value from a list of expressions. It's essential for handling NULL values, providing default values, and consolidating data from multiple columns. Evaluates arguments from left to right and stops at the first non-NULL value.

Syntax

COALESCE(value1, value2, ..., valueN)

Returns: The first non-NULL value, or NULL if all values are NULL

Platform-Specific Notes

Snowflake:

• Also supports NVL(expr1, expr2) - 2-argument version
• IFNULL(expr1, expr2) - alias for NVL
• IFF(condition, true_val, false_val) - conditional alternative

BigQuery:

• Also supports IFNULL(expr1, expr2) - 2-argument version
• IF(condition, true_val, false_val) - conditional alternative

Databricks:

• Also supports NVL(expr1, expr2) - 2-argument version
• IFNULL(expr1, expr2) - alias for NVL
• Standard Spark SQL function

Example 1: Handle NULL Contact Information

All Platforms:

SELECT 
    customer_id,
    customer_name,
    mobile_phone,
    home_phone,
    work_phone,
    COALESCE(mobile_phone, home_phone, work_phone, 'No phone available') as primary_contact,
    COALESCE(email, backup_email, 'noemail@company.com') as contact_email
FROM customers;

Sample Data (customers table):

Result:

Example 2: Consolidate Price Information

All Platforms:

SELECT 
    product_id,
    product_name,
    sale_price,
    regular_price,
    msrp,
    COALESCE(sale_price, regular_price, msrp) as final_price,
    COALESCE(sale_price, regular_price, msrp, 0) as display_price,
    CASE 
        WHEN sale_price IS NOT NULL THEN 'On Sale'
        WHEN regular_price IS NOT NULL THEN 'Regular Price'
        WHEN msrp IS NOT NULL THEN 'MSRP'
        ELSE 'Price Not Set'
    END as price_type
FROM products;

Sample Data (products table):

Result:

Example 3: Data Quality - Fill Missing Values

All Platforms:

SELECT 
    order_id,
    customer_id,
    -- Use shipping address if billing address is missing
    COALESCE(billing_address, shipping_address) as invoice_address,
    -- Use estimated date if actual date is missing
    COALESCE(actual_delivery_date, estimated_delivery_date) as delivery_date,
    -- Use default values for missing metrics
    COALESCE(discount_amount, 0) as discount,
    COALESCE(tax_amount, order_total * 0.08) as tax,
    order_total + COALESCE(shipping_cost, 9.99) as total_with_shipping
FROM orders;

Sample Data (orders table):

Result:

Example 4: Aggregation with NULL Handling

All Platforms:

SELECT 
    department,
    COUNT(*) as total_employees,
    -- Count employees with bonus (NULL-safe)
    COUNT(bonus_amount) as employees_with_bonus,
    -- Average bonus, treating NULL as 0
    AVG(COALESCE(bonus_amount, 0)) as avg_bonus_including_zeros,
    -- Average bonus, excluding NULLs
    AVG(bonus_amount) as avg_bonus_excluding_nulls,
    -- Total bonus pool
    SUM(COALESCE(bonus_amount, 0)) as total_bonus_pool
FROM employees
GROUP BY department;

Sample Data (employees table):

Result:

Example 5: Multi-Source Data Consolidation

All Platforms:

SELECT 
    user_id,
    -- Try profile name, then account name, then email prefix, then 'Anonymous'
    COALESCE(
        profile_display_name,
        account_full_name,
        SPLIT_PART(email, '@', 1),
        'Anonymous User'
    ) as display_name,
    -- Get most recent activity timestamp from multiple sources
    COALESCE(
        last_purchase_date,
        last_login_date,
        account_created_date
    ) as last_activity,
    -- User timezone preference with fallback
    COALESCE(
        user_timezone,
        account_country_timezone,
        'UTC'
    ) as timezone
FROM user_profiles;

Sample Data (user_profiles table):

Result:

COALESCE vs Other NULL Functions

Common Use Cases

1. Default values: Provide fallback when data is missing
2. Contact consolidation: Get best available contact method
3. Price selection: Choose best available price point
4. Data quality: Fill gaps in incomplete datasets
5. User experience: Display meaningful defaults
6. Calculations: Prevent NULL propagation in math operations
7. Report formatting: Replace NULL with readable text

COALESCE in Calculations

All Platforms:

SELECT 
    product_id,
    base_price,
    discount_pct,
    tax_pct,
    -- Prevent NULL in calculations
    base_price * (1 - COALESCE(discount_pct, 0)) as price_after_discount,
    base_price * (1 - COALESCE(discount_pct, 0)) * (1 + COALESCE(tax_pct, 0.08)) as final_price
FROM products;

Performance Considerations

• COALESCE short-circuits: stops at first non-NULL value
• Evaluates expressions left to right
• More arguments = more evaluation overhead
• Consider indexing frequently checked columns
• Use simple IFNULL/NVL for 2-argument cases (may be optimized)

Best Practices

1. Order by preference: Put most preferred value first
2. Always provide final default: Prevent unexpected NULLs
3. Document logic: Explain why each fallback is chosen
4. Type compatibility: All arguments must be compatible types
5. Avoid expensive expressions: Don't call functions unnecessarily
6. Consider CASE: For complex conditional logic
7. Test with real data: Ensure fallback logic works correctly