Beginner

86. NULLIF Function

Learn how NULLIF can return NULL when two expressions are equal.

📘 Lesson 86 of 728 ⏱ 8 min 🎯 Beginner

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

NULLIF can help prevent unwanted values from being treated as meaningful data and can be useful when handling calculations involving zero or placeholder values.

A system may store zero as a placeholder for an unavailable quantity. NULLIF can convert that zero to NULL before a calculation.

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

NULLIF is useful in financial calculations, data cleansing, ratio calculations, and preventing division-by-zero problems.

  • NULLIF compares two expressions.
  • Equal expressions return NULL.
  • Different expressions return the first expression.
  • It can help prevent division-by-zero errors.
  • It is useful in data transformation.
What does NULLIF return when two expressions are equal?

NULLIF returns NULL when the two expressions are equal.

How can NULLIF help with division by zero?

NULLIF can convert a zero denominator to NULL, preventing a direct division-by-zero operation.

Have you finished this lesson?

Introduction to SQL – All Lessons