What Is COALESCE?

-- Basic COALESCE: returns first non-NULL value
SELECT
    first_name,
    COALESCE(nickname, first_name) AS display_name
FROM employees;

-- Multiple fallbacks: department, then team, then 'Unassigned'
SELECT
    employee_name,
    COALESCE(department, team, 'Unassigned') AS assignment
FROM staff;

-- Replacing NULL in aggregations
SELECT
    department,
    COALESCE(SUM(commission), 0) AS total_commission
FROM employees
GROUP BY department;

What Is NULLIF?

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

-- Convert a default value back to NULL
SELECT
    employee_name,
    NULLIF(bonus, 0) AS actual_bonus
FROM payroll;

-- Combined: COALESCE handles NULL, NULLIF handles zero
SELECT
    employee_name,
    COALESCE(NULLIF(salary, 0), 0) AS salary
FROM payroll
WHERE salary IS NOT NULL;

COALESCE vs CASE WHEN

Practical Examples

Employee Directory with Defaults

SELECT
    employee_id,
    first_name || ' ' || last_name AS full_name,
    COALESCE(phone, 'No phone') AS phone,
    COALESCE(email, 'No email') AS email,
    COALESCE(extension, 'N/A') AS extension
FROM employees;

Salary Report with Commission Handling

SELECT
    e.first_name || ' ' || e.last_name AS employee_name,
    e.salary AS base_salary,
    COALESCE(e.commission, 0) AS commission,
    e.salary + COALESCE(e.commission, 0) AS total_compensation,
    CASE
        WHEN e.salary >= 100000 THEN 'Senior'
        WHEN e.salary >= 60000 THEN 'Mid-Level'
        ELSE 'Junior'
    END AS level
FROM employees e;

Division Safety Pattern

-- Always use NULLIF when dividing to prevent runtime errors
SELECT
    department_name,
    total_budget,
    employee_count,
    total_budget / NULLIF(employee_count, 0) AS budget_per_employee
FROM department_stats;

Cleaning Import Data

-- Imported data may have 0 instead of NULL for missing values
SELECT
    customer_id,
    COALESCE(NULLIF(phone, ''), NULLIF(phone, '0'), 'Unknown') AS phone,
    COALESCE(NULLIF(address, ''), 'No address') AS address
FROM imported_customers;

NULL Behavior Quick Reference

Advanced Patterns

Conditional Aggregation with NULLIF

-- Calculate average salary only for departments with more than 3 employees
SELECT
    department_id,
    SUM(salary) / NULLIF(COUNT(*), 0) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3;

Pivoting with COALESCE

-- Pivot monthly sales into columns with NULL replacement
SELECT
    product_id,
    COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1 THEN amount END), 0) AS jan,
    COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2 THEN amount END), 0) AS feb,
    COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 3 THEN amount END), 0) AS mar
FROM sales
GROUP BY product_id;

Null-Safe Comparisons

-- NULL-safe equality: COALESCE makes NULLs comparable
SELECT *
FROM employees
WHERE COALESCE(phone, '') = COALESCE(:input_phone, '');

Common Mistakes

Performance Considerations

COALESCE and NULLIF are zero-cost at runtime. The database optimizer inlines them as simple comparisons. There is no performance penalty for using them liberally.

Portability Notes

When to Use Each