Beginner

287. NULLIF Function

Learn how to return NULL when two expressions have the same value.

📘 Lesson 287 of 728 ⏱ 10 min 🎯 Intermediate

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

NULLIF is useful for converting unwanted placeholder values into NULL and preventing certain calculation problems such as division by zero.

Prevent division by zero when calculating a ratio.

SQL 
SELECT
    SalesAmount / NULLIF(TargetAmount, 0) AS AchievementRatio
FROM SalesPerformance;

NULLIF is useful in financial calculations, data cleaning, validation, and safe ratio calculations.

  • NULLIF compares two expressions.
  • Equal expressions produce NULL.
  • Otherwise the first expression is returned.
  • It can prevent division-by-zero errors.
  • It is useful for data cleaning.
What does NULLIF return?

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

How can NULLIF prevent division by zero?

NULLIF can convert a zero denominator into NULL so the division does not directly divide by zero.

Have you finished this lesson?

Introduction to SQL – All Lessons