COALESCE and NULLIF

---

Handling Nulls: COALESCE

The COALESCE function is used to handle NULL (missing) values in your data. It evaluates a list of arguments from left to right and returns the first non-null value it encounters.

Why it is essential

In SQL, any mathematical operation or string concatenation involving a NULL usually results in NULL ($5 + NULL = NULL$). COALESCE prevents your calculations from "breaking" by providing a fallback value.

Standard SQL Examples

1. Providing a Default String

If a field like middle_name is missing, you can replace the null with an empty string or a placeholder.

SELECT 
    first_name || ' ' || COALESCE(middle_name, '') || ' ' || last_name AS full_name
FROM users;

2. Numeric Fallbacks

Ensures that calculations continue even if a specific fee or value is missing.

-- If 'tax' is NULL, it is treated as 0
SELECT 
    price + COALESCE(tax, 0) AS total_cost
FROM sales;

3. Choosing between Multiple Columns

You can check multiple columns in order of priority.

-- Use mobile if available; if not, use home_phone; if both are null, use 'N/A'
SELECT 
    user_id, 
    COALESCE(mobile, home_phone, 'N/A') AS primary_contact
FROM contacts;

---

Turning Values to Null: NULLIF

The NULLIF function does the exact opposite of COALESCE. It takes two arguments and returns NULL if they are equal; otherwise, it returns the first argument.

The "Divide by Zero" Protection

The most critical use for NULLIF in Data Engineering is preventing query crashes during division. If you try to divide a number by zero, the database will throw an error. By turning the zero into a NULL, the result of the division becomes NULL instead of a crash.

-- Syntax: NULLIF(expression, value_to_match)

SELECT 
    total_revenue / NULLIF(total_units, 0) AS price_per_unit
FROM sales_data;

• How it works: If total_units is 0, NULLIF returns NULL. Since total_revenue / NULL is safe (it just results in NULL), your data pipeline keeps running.

---

Combining CAST and COALESCE

In production data pipelines, these two are often used together to "clean" raw data into a usable format.

-- Strategy: Convert the type first, then fill the gaps
SELECT
    COALESCE(CAST(raw_value AS INTEGER), 0) AS clean_integer_value
FROM raw_staging_table;

Summary Reference Table

Function	Logical Action	Data Engineering Use Case
COALESCE(val, 0)	If val is NULL, use 0.	Filling in missing data / defaults.
NULLIF(val, 0)	If val is 0, return NULL.	Preventing "Divide by Zero" errors.

---