A report contains missing values across primary_phone, alternate_phone, and emergency_phone. How would you return the first available value and show 'Not Available' when all three are NULL?
Interview preparation resource from Gate Smashers.
Use COALESCE(primary_phone, alternate_phone, emergency_phone, 'Not Available') to return the first non-NULL phone value and fall back to 'Not Available' if all three are NULL. If blank strings should also be treated as missing, wrap each column with NULLIF(column, '') inside COALESCE.
SELECT customer_name,
COALESCE(primary_phone, alternate_phone, emergency_phone, 'Not Available') AS contact_phone
FROM Customers;Direct answer
Return the first available phone with COALESCE and provide a fallback literal when all values are NULL. Example: COALESCE(primary_phone, alternate_phone, emergency_phone, 'Not Available'). This returns the first non-NULL argument or 'Not Available' if none exist.
How COALESCE works
COALESCE evaluates its arguments from left to right and returns the first expression that is not NULL. Evaluation stops once a non-NULL value is found, so later arguments are not evaluated for that row.
- Behavior: Returns the first non-NULL argument; stops evaluating after finding one.
- Type compatibility: All arguments should have compatible data types (the result type is determined by the DBMS rules for COALESCE).
Handling blank strings (not just NULL)
An empty string ('') is not NULL, so COALESCE will treat it as a valid value. If you need to treat empty strings as missing values, use NULLIF(column, '') to convert blanks to NULL before applying COALESCE.
- Transformation: Use NULLIF(primary_phone, '') so blank values become NULL and are skipped by COALESCE.
- Combined usage: COALESCE(NULLIF(primary_phone, ''), NULLIF(alternate_phone, ''), NULLIF(emergency_phone, ''), 'Not Available')
Example usage
Use the following SQL to select the customer name and the chosen contact phone, falling back to 'Not Available' when all phone columns are NULL (or treated as NULL when wrapped with NULLIF if desired). The exact code block is provided below.