Beginner

327. NULLIF Function

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

📘 Lesson 327 of 728 ⏱ 10 min 🎯 Intermediate

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

NULLIF is particularly useful for preventing division-by-zero errors and converting unwanted placeholder values into NULL.

Prevent a division-by-zero error when calculating an average amount per transaction.

SQL 
SELECT
    TotalSales / NULLIF(TotalOrders, 0) AS AverageOrderValue
FROM SalesSummary;

NULLIF is commonly used in financial calculations, ratios, data cleaning, and safe division operations.

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

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

How does NULLIF prevent division by zero?

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

Have you finished this lesson?

Introduction to SQL – All Lessons