NULLIF

Source data — Pattern 3: Built-in Functions

All queries run against the shared employees table (see schema.sql). This pattern needs two columns not used before: hire_date (topic 17) and bonus, nullable (topics 19–20).

---

What it does

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. It's the reverse of COALESCE, and its classic use is guarding a division: dividing by an actual 0 errors or returns infinity depending on the engine, but dividing by NULLIF(x, 0) turns that 0 into NULL first, so the division safely returns NULL instead.

Problem

Avoid division by zero using NULLIF.

SELECT employee_id,
       salary,
       bonus,
       salary / NULLIF(bonus, 0) AS salary_to_bonus_ratio
FROM employees;

Result