A ratio needs both a numerator and a meaningful denominator. When the denominator is zero, a report must decide how to represent the unavailable calculation. Simply replacing the result with zero can make the output look complete while communicating the wrong meaning.
Oracle’s NULLIF offers a concise way to prevent a zero denominator from being used in a division. Its value is greatest when the surrounding report also explains what the missing result means.
Understand what NULLIF returns
NULLIF(first_expression, second_expression) compares its arguments. If they are equal, it returns null. Otherwise, it returns the first expression. Oracle documents this behavior as equivalent to a conditional expression that returns null when the comparison matches.
For an illustrative table containing Oracle NUMBER columns, a completion percentage could be calculated as:
SELECT project_id,
100 * completed_items
/ NULLIF(planned_items, 0) AS completion_pct
FROM project_summary;When planned_items is zero, NULLIF produces a null denominator. Oracle arithmetic involving null produces null, so the percentage is unavailable rather than a division by zero.
Distinguish zero performance from an undefined percentage
Consider three projects. The first has zero completed items out of ten planned items. Its completion percentage is zero.
The second has no planned items. A completion percentage based on that denominator is undefined. The third has a missing planned-item count, so the necessary information is unavailable.
These situations should not automatically receive the same display value.
A dashboard that labels all three as “0%” encourages readers to interpret them as equivalent performance outcomes. A more informative design can show the numerical percentage where defined and a clear status elsewhere.
For example, “No plan recorded” may be appropriate when the denominator is absent, while “No planned items” describes a known zero. The exact wording should follow the data’s meaning.
Keep data validation separate from calculation handling
NULLIF does not establish whether the denominator is reasonable. A negative planned-item count remains negative because it is not equal to zero.
Similarly, if completed items exceed planned items, the resulting percentage may exceed 100. That could be a valid situation, an outdated plan, or a data error. The expression cannot decide which interpretation applies.
Document these cases independently. A useful review includes zero, null, negative, and positive denominator values, plus a numerator larger than the denominator.
This makes the reporting rule explicit rather than allowing an arithmetic safeguard to become an accidental data-quality policy.
Design the display before adding a fallback
It may be tempting to wrap every unavailable result in an expression that produces zero. Do that only when zero is the intended business meaning.
For a project overview, an unavailable percentage can be accompanied by the underlying counts. Showing “Completed: 4; Planned: not recorded” helps the reader understand why there is no percentage.
Keep the raw status available in exports as well. A blank cell without an explanation can be misread as an omitted calculation.
Used carefully, NULLIF lets a report remain readable without pretending that every ratio exists. The goal is to preserve the distinction between a real zero and a result that cannot be meaningfully calculated.