COALESCE() in SQL
Returns the first non-null value in a list of expressions, evaluated from left to right.
Syntax
Return type
COALESCE() Function Example
What is COALESCE() in SQL?
The COALESCE() function in SQL returns the first non-null value from a list of expressions, evaluated from left to right. If all expressions are NULL, it returns NULL. This function is useful for handling missing data, providing default values, and avoiding null-related errors.
COALESCE() is ANSI SQL-compliant and is supported in SQL Server, MySQL, PostgreSQL, and Oracle.
The COALESCE() function is widely used in data cleaning, reporting, and ensuring completeness in SQL queries.
Parameters:
expression1, expression2, ...: List of expressions to evaluate
Minimum of two expressions required
Example Use Cases:
-- Default value handling
-- Contact preference
-- Report formatting
Notes:
Behavior: Returns NULL only if all arguments are NULL
Performance Considerations: Evaluates arguments in order until non-null found
Version Info: Core SQL feature available in all DBMS
Deprecated/Recommended Alternatives: None, standard functionality
Error Handling:
Error: Type mismatch between expressions
Recommendation: Ensure consistent data types across expressions
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
COALESCE()
COALESCE(col1, col2, 'default')
Returns first non-null
PostgreSQL
COALESCE()
COALESCE(col1, col2, 'default')
Returns first non-null
SQL Server
COALESCE()
COALESCE(col1, col2, 'default')
Returns first non-null
SQLite
COALESCE()
COALESCE(col1, col2, 'default')
Returns first non-null
BigQuery
COALESCE()
COALESCE(col1, col2, 'default')
Returns first non-null
Snowflake
COALESCE()
COALESCE(col1, col2, 'default')
Returns first non-null
Athena
COALESCE()
COALESCE(col1, col2, 'default')
Returns first non-null
