Detailed explanation
COALESCE and CASE are SQL expressions that return a value, but they solve different problems. COALESCE selects the first non-NULL value from an ordered list of arguments. CASE supports conditional selection: it chooses a result according to matching values or Boolean conditions. Use COALESCE when the requirement is simply “use this value, otherwise try the next one”; use CASE when the requirement involves comparisons, categories, or other business rules. Both can appear wherever a suitable scalar expression is allowed, including SELECT lists and ORDER BY clauses.
In COALESCE(nickname, first_name, 'guest'), SQL checks the arguments in their written order and returns the first one that is not NULL. If nickname is NULL but first_name is 'Asha', the result is 'Asha'. If both columns are NULL, it returns 'guest'. If every argument is NULL, the result is NULL. Importantly, COALESCE checks for NULL, not for values that seem missing or false: zero remains zero, and an empty string usually remains an empty string. Oracle is a notable exception because it treats empty character strings as NULL.
CASE has two forms. Searched CASE uses conditions, such as CASE WHEN salary > 100000 THEN 'high' WHEN salary > 50000 THEN 'mid' ELSE 'entry' END. The first condition that evaluates to TRUE determines the result, so order matters when conditions overlap. A NULL salary makes these comparisons UNKNOWN rather than TRUE, so this example returns 'entry'. Simple CASE compares one expression against listed values, for example CASE status WHEN 'A' THEN 'active' ELSE 'other' END. Without ELSE, an unmatched CASE returns NULL.
Conceptually, COALESCE(a, b, c) corresponds to CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END. This explains why COALESCE is convenient for fallback logic, while CASE is more flexible. Both require compatible result types under the database’s type-resolution rules; neither guarantees that arbitrary mixed types will work. Interviewers generally look for correct NULL handling, awareness of branch order, and an appropriate expression for the requirement—not a claim that either construct is always faster. Internal rewrites and expression-evaluation details vary by database.
Key points to remember
- COALESCE returns the first non-NULL argument; all-NULL arguments produce NULL.
- CASE supports both value matching and condition-based branching.
- CASE selects the first matching branch; omitting ELSE makes unmatched results NULL.
- Use IS NULL or IS NOT NULL to test for NULL, not = NULL.
- Both expressions follow database-specific type compatibility and conversion rules.
Common mistakes candidates make
- Assuming COALESCE skips zero or empty strings as well as NULL.
- Putting salary > 50000 before salary > 100000, making the 'high' branch unreachable.
- Claiming COALESCE is always faster than CASE without considering the database and execution plan.
Likely follow-up questions
Can CASE replace COALESCE?
Yes: COALESCE(a, b) corresponds to CASE WHEN a IS NOT NULL THEN a ELSE b END for ordinary column values. Evaluation details can differ for complex expressions, so this is not a universal guarantee of identical execution behaviour.
How can I treat an empty nickname as missing?
Use COALESCE(NULLIF(nickname, ''), first_name, 'guest'). NULLIF converts an empty nickname to NULL, allowing COALESCE to try the next value.
How should CASE handle a NULL salary separately?
Add WHEN salary IS NULL THEN 'unknown' before the salary comparisons. Otherwise, the comparisons do not evaluate to TRUE, and the original example returns 'entry'.
Interview tip: Start with “COALESCE is for NULL fallbacks; CASE is for conditional branching,” then explain the nickname and salary examples.