Beginner

376. NULLIF Function

Learn how to convert a specified value into NULL using NULLIF.

📘 Lesson 376 of 728 ⏱ 9 min 🎯 Intermediate

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

NULLIF is useful for avoiding unwanted values and handling situations such as division by zero.

Prevent division by zero when calculating an average amount.

SQL 
SELECT
    SalesAmount / NULLIF(Quantity, 0) AS AveragePrice
FROM Sales;

Analysts use NULLIF in calculations, data cleaning, financial analysis, and exception handling.

  • NULLIF compares two expressions.
  • It returns NULL when they are equal.
  • Otherwise it returns the first expression.
  • It can help prevent division-by-zero errors.
  • It is useful for conditional NULL handling.
What does NULLIF return when both expressions are equal?

It returns NULL.

How can NULLIF help with division by zero?

Using NULLIF(denominator, 0) converts a zero denominator to NULL instead of attempting division by zero.

Have you finished this lesson?

Introduction to SQL – All Lessons