sql / intermediate
Snippet
Handling Missing Data Safely with COALESCE
The standard COALESCE scalar function evaluates arguments in sequence and returns the first non-NULL value. It prevents NULL values from propagating through arithmetic operations or output reports, ensuring data completeness.
snippet.sql
sql
1
2
3
4
5
SELECTcustomer_id,COALESCE(phone_number, mobile_number, 'N/A') AS primary_contact,COALESCE(account_balance, 0.00) AS safe_balanceFROM customer_accounts;
Breakdown
1
SELECT
Begins the column selection statement.
2
customer_id,
Retrieves the unique customer identification number.
3
COALESCE(phone_number, mobile_number, 'N/A') AS primary_contact,
Returns phone_number if available, falls back to mobile_number, or defaults to 'N/A'.
4
COALESCE(account_balance, 0.00) AS safe_balance
Replaces NULL numeric balances with a default floating zero decimal value.
5
FROM customer_accounts;
Specifies the target table for customer account entries.