Oracle NULLIF Function: A Simple Guide

The NULLIF function in Oracle SQL is a simple but powerful control-flow function. It compares two expressions. If they are equal, it returns NULL. If they are not equal, it returns the first expression.

This function is most famously used as an elegant way to prevent "division by zero" errors in calculations.

What is the NULLIF Function in Oracle?

The NULLIF(expr1, expr2) function is a concise way to write a CASE statement.

It is logically identical to this:

CASE 
  WHEN expr1 = expr2 THEN NULL 
  ELSE expr1 
END

This is very useful when you want to "nullify" a value if it matches a specific, known value. For example, you might want to treat the number 0 or the string 'N/A' as NULL in your query.

NULLIF Function Syntax

The syntax for NULLIF requires two arguments:

NULLIF(expr1, expr2)

Let's break that down:

• expr1: The first expression or value. This is the value that will be returned if the comparison is false. You cannot use the literal NULL here.
• expr2: The second expression or value. This is the value you are comparing against expr1.

Oracle NULLIF Function Examples

Here are two practical examples of how to use NULLIF.

Example 1: Preventing a "Division by Zero" Error using NULLIF

This is the classic use case for NULLIF. Imagine you have a table inventory and you want to calculate the cost_per_item by dividing total_cost by quantity_on_hand.

If quantity_on_hand is 0, your query will crash with an ORA-01476: divisor is equal to zero error.

NULLIF can fix this. By writing NULLIF(quantity_on_hand, 0), you are telling Oracle: "If the quantity is 0, replace it with NULL." Since dividing by NULL just results in NULL (not an error), your query can run safely.

Query:

-- This query simulates an inventory table
WITH inventory AS (
  SELECT 'Product A' AS product, 1000 AS total_cost, 50 AS quantity FROM DUAL UNION ALL
  SELECT 'Product B' AS product, 750 AS total_cost, 0 AS quantity FROM DUAL
)
SELECT
  product,
  total_cost / NULLIF(quantity, 0) AS "Cost_Per_Item"
FROM inventory;

Result: (The division for 'Product B' results in NULL instead of an error)

PRODUCT   Cost_Per_Item
--------- -------------
Product A            20
Product B        (NULL)

Example 2: Replacing a "Default" String with NULL using NULLIF

Sometimes, an application will store a "default" string like 'N/A' or '(empty)' in a column instead of NULL. NULLIF is the perfect tool to clean this up in your query.

Query:

-- This query simulates a user_profiles table
WITH user_profiles AS (
  SELECT 'Alice' AS user_name, '555-1234' AS phone FROM DUAL UNION ALL
  SELECT 'Bob' AS user_name, 'N/A' AS phone FROM DUAL
)
SELECT
  user_name,
  NULLIF(phone, 'N/A') AS "Cleaned_Phone"
FROM user_profiles;

Result: (The 'N/A' string for Bob is converted to NULL)

USER_NAME Cleaned_Phone
--------- -------------
Alice     555-1234
Bob       (NULL)