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