COALESCE and NULLIF for Null Handling

SELECT
    product_id,
    product_name,
    -- Provide default for NULL prices
    COALESCE(unit_price, 0.00) AS price,
    -- Chain fallbacks: discount > promo > default
    COALESCE(discount_rate, promo_rate, 0.0) AS effective_discount,
    -- NULLIF to prevent division by zero
    unit_price / NULLIF(stock_quantity, 0) AS price_per_unit_stock,
    -- COALESCE with computed fallback
    COALESCE(
        supplier_name,
        'Unknown Supplier'
    ) AS supplier,
    -- GREATEST/LEAST ignore NULLs
    GREATEST(cost_price, unit_price * 0.5) AS min_viable_price,
    -- Nested COALESCE for display
    COALESCE(
        phone,
        email,
        'No contact info'
    ) AS primary_contact,
    -- NULLIF to treat specific values as NULL
    NULLIF(status, 'pending') AS active_status
FROM products
WHERE COALESCE(stock_quantity, 0) > 0
ORDER BY COALESCE(unit_price, 999999) ASC;

-- Aggregate with COALESCE for complete summaries
SELECT
    department_id,
    COUNT(*) AS total_employees,
    COUNT(salary) AS employees_with_salary,
    COALESCE(AVG(salary), 0) AS avg_salary,
    COALESCE(MAX(salary), 0) AS max_salary,
    COALESCE(SUM(salary), 0) AS total_payroll
FROM employees
GROUP BY department_id;