Skip to content
Quantoros

What is NULLIF?

SQLLevel: IntermediateNULL Handling

What is NULLIF?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

NULLIF(a, b) returns NULL if a = b, otherwise returns a. Use it to convert a specific value to NULL.

-- Avoid division by zero
SELECT total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM daily_metrics;
-- If total_orders = 0, NULLIF returns NULL, and NULL / anything = NULL (no crash)

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions