SQL: COALESCE picks the first non-NULL, NULLIF makes one

COALESCE is a default value; NULLIF turns a sentinel into a real NULL. Together they clean up columns where absence was recorded as an empty string or a zero.

Code

SELECT COALESCE(nickname, first_name, 'Anonymous') AS display
  FROM users;

-- '' meant "not set" -> make it a real NULL
SELECT COALESCE(NULLIF(nickname, ''), first_name) FROM users;

-- and the classic divide-by-zero guard
SELECT total / NULLIF(quantity, 0) AS unit_price FROM order_line;

Output

nickname | first_name | display
NULL     | Ann        | Ann
''       | Bob        | Bob         <- NULLIF turned '' into NULL first
'Zed'    | Carl       | Zed

-- x / NULL is NULL, not an error - which is usually the right answer for
-- a rate with no denominator.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25