Beginner

125. NULLIF Function

Learn how NULLIF can convert a specific value into NULL when two expressions are equal.

📘 Lesson 125 of 728 ⏱ 9 min 🎯 Intermediate

NULLIF compares two expressions. If they are equal, it returns NULL; otherwise, it returns the first expression.

NULLIF is useful for preventing unwanted values from participating in calculations and for avoiding issues such as division by zero.

If a business dataset uses zero to represent an unavailable denominator, NULLIF can convert that zero to NULL before division.

SQL 
SELECT
    Revenue,
    Expenses,
    Revenue / NULLIF(Expenses, 0) AS RevenueRatio
FROM FinancialData;

NULLIF is useful in financial calculations, ratio analysis, data cleaning, and calculations involving potentially zero denominators.

  • NULLIF compares two expressions.
  • It returns NULL when both expressions are equal.
  • Otherwise it returns the first expression.
  • It can help avoid division-by-zero errors.
  • It is useful in calculations.
What does NULLIF return?

It returns NULL when its two expressions are equal; otherwise, it returns the first expression.

Why is NULLIF used in calculations?

It can convert problematic values such as zero into NULL and help prevent division-by-zero errors.

Have you finished this lesson?

Introduction to SQL – All Lessons