Beginner

222. NULLIF Function

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

📘 Lesson 222 of 728 ⏱ 9 min 🎯 Intermediate

The NULLIF function compares two expressions and returns NULL when they are equal. Otherwise, it returns the first expression.

NULLIF is useful for avoiding unwanted values and preventing problems such as division by zero.

Convert a zero denominator to NULL before performing a calculation.

SQL 
SELECT
    SalesAmount / NULLIF(UnitsSold, 0) AS AverageSalesPerUnit
FROM Sales;

NULLIF is commonly used in financial calculations, ratio analysis, data cleansing, and division-by-zero prevention.

  • NULLIF compares two expressions.
  • Equal values produce NULL.
  • Different values return the first expression.
  • It can help prevent division by zero.
  • It is useful in data transformation.
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 calculation does not attempt to divide by zero.

Have you finished this lesson?

Introduction to SQL – All Lessons