Beginner

340. NULLIF vs COALESCE

Understand the difference between NULLIF and COALESCE and when to use each one.

📘 Lesson 340 of 728 ⏱ 11 min 🎯 Intermediate

NULLIF returns NULL when two expressions are equal, while COALESCE returns the first non-NULL expression from a list of values.

Both functions are important for controlling NULL values, but they solve different problems in analytical SQL.

Use NULLIF to avoid division by zero and COALESCE to provide a fallback value.

SQL 
SELECT
    TotalSales / NULLIF(TotalOrders, 0) AS AverageOrderValue,
    COALESCE(Region, 'Unknown') AS RegionName
FROM SalesSummary;

These functions are widely used in financial calculations, KPI reporting, data cleansing, and NULL-safe transformations.

  • NULLIF converts equal expressions into NULL.
  • COALESCE returns the first non-NULL value.
  • NULLIF is useful for safe division.
  • COALESCE is useful for fallback values.
  • Both are important for NULL handling.
What is the main difference between NULLIF and COALESCE?

NULLIF returns NULL when two expressions are equal, while COALESCE returns the first non-NULL value from multiple expressions.

When would you use NULLIF?

NULLIF is commonly used to prevent division-by-zero errors or convert specific placeholder values into NULL.

Have you finished this lesson?

Introduction to SQL – All Lessons