NULLIF(expression1, expression2)
-- Returns NULL if expression1 = expression2
-- Otherwise returns expression1

IFF(condition, true_value, false_value)
-- Snowflake's shorthand for simple CASE

IF(condition, true_value, false_value)
-- BigQuery's conditional function

SELECT 
    product_name,
    total_revenue,
    units_sold,
    -- Without NULLIF (would error on 0)
    -- total_revenue / units_sold as avg_price,  -- ERROR if units_sold = 0
    
    -- With NULLIF (safe division)
    total_revenue / NULLIF(units_sold, 0) as avg_price,
    
    -- Alternative with CASE
    CASE 
        WHEN units_sold = 0 THEN NULL
        ELSE total_revenue / units_sold
    END as avg_price_alternative
FROM

SELECT 
    customer_id,
    customer_name,
    email,
    phone,
    -- Convert empty strings to NULL
    NULLIF(TRIM(email), '') as email_clean,
    NULLIF(TRIM(phone), '') as phone_clean,
    NULLIF(TRIM(secondary_email), '') as secondary_email_clean,
    
    -- Count non-empty contact methods
    (CASE WHEN NULLIF(TRIM(email), '') IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN NULLIF(TRIM(phone), '') IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN NULLIF(TRIM(secondary_email), '') IS NOT NULL THEN 1 ELSE 0 END) as contact_methods_count
FROM

SELECT 
    customer_id,
    total_purchases,
    -- Simple if-then-else
    IFF(total_purchases > 1000, 'VIP', 'Standard') as customer_tier,
    IFF(total_purchases > 500, 'Yes', 'No') as qualifies_for_discount,
    IFF(total_purchases = 0, 'Never Purchased', 'Active') as status,
    
    -- Nested IFF
    IFF(total_purchases > 5000, 'Platinum',
        IFF(total_purchases > 1000, 'Gold',
            IFF(total_purchases > 500, 'Silver', 'Bronze')
        )
    ) as loyalty_level
FROM

SELECT 
    order_id,
    order_total,
    shipping_cost,
    -- Simple conditional
    IF(order_total > 100, 0, shipping_cost) as final_shipping_cost,
    IF(order_total > 100, 'Free Shipping', 'Standard') as shipping_type,
    
    -- Calculate final total
    order_total + IF(order_total > 100, 0, shipping_cost) as final_total,
    
    -- Nested IF
    IF(order_total > 500, 'Express Free',
        IF(order_total > 100, 'Standard Free', 'Standard Paid')
    ) as shipping_tier
FROM

SELECT 
    product_id,
    product_name,
    price,
    discount_price,
    -- Only show discount if it's actually different from regular price
    NULLIF(discount_price, price) as actual_discount_price,
    
    -- Calculate savings (NULL if no discount)
    price - NULLIF(discount_price, price) as savings,
    
    -- Percentage discount (NULL if no discount)
    ROUND(
        (price - NULLIF(discount_price, price)) * 100.0 / price,
        2
    ) as discount_percentage
FROM

SELECT 
    employee_id,
    base_salary,
    bonus,
    commission,
    
    -- Total compensation with safe division
    base_salary + COALESCE(bonus, 0) + COALESCE(commission, 0) as total_comp,
    
    -- Bonus as percentage of base (NULL if no bonus)
    ROUND(
        NULLIF(bonus, 0) * 100.0 / NULLIF(base_salary, 0),
        2
    ) as bonus_percentage,
    
    -- Use bonus if exists, otherwise commission, otherwise 0
    COALESCE(
        NULLIF(bonus, 0),
        NULLIF(commission, 0),
        0
    ) as variable_pay
FROM

NULLIF(column, value)

CASE WHEN column = value THEN NULL ELSE column END

IFF(condition, true_value, false_value)  -- Snowflake
IF(condition, true_value, false_value)   -- BigQuery

CASE WHEN condition THEN true_value ELSE false_value END