NULLIF and IIF
Part of the Beyond the Basics section of Coddy's SQL journey — lesson 4 of 27.
Two more conditional helpers, both small and useful.
NULLIF(a, b) returns NULL when a = b, otherwise a. The classic use is guarding against division by zero:
SELECT total / NULLIF(count, 0) AS average
FROM statsIf count is zero we get NULL instead of an error.
IIF(cond, a, b) is a two-branch shortcut for CASE WHEN cond THEN a ELSE b END. Reach for it when the choice is binary; reach for CASE when there are more branches.
Challenge
EasyAvailable tables and columns:
<strong>matches</strong>:<strong>id</strong>,<strong>shots</strong>,<strong>goals</strong>
For each match return:
idconversion:goals * 1.0 / shots, butNULLwhenshotsis0(useNULLIF)verdict:'win'whengoals > 0, otherwise'no goals'(useIIF)
Order by id.
Cheat sheet
NULLIF(a, b) returns NULL when a = b, otherwise returns a. Useful for avoiding division by zero:
SELECT total / NULLIF(count, 0) AS average
FROM statsIIF(cond, a, b) is a shortcut for a two-branch CASE:
SELECT IIF(goals > 0, 'win', 'no goals') AS verdict
FROM matchesTry it yourself
SELECT id,
-- conversion using NULLIF
-- verdict using IIF
FROM matches
ORDER BY id
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.