SQL COALESCE

What is SQL COALESCE?

The SQL COALESCE function is used to handle NULL values in a database query by returning the first non-NULL value from a list of expressions or column values. It allows you to provide a default or fallback value when NULL values are encountered in the data. COALESCE is a versatile function that helps ensure meaningful data is displayed in query results, even in the presence of NULL values.

When you would use it

You would use the SQL COALESCE function when you want to deal with NULL values in a query by substituting them with a default value or selecting the first non-NULL value from a list of options. This is especially useful when you want to ensure that the result set contains meaningful data, or when NULL values might disrupt calculations, comparisons, or presentation.

Syntax

The syntax for the SQL COALESCE function is as follows:

COALESCE(expression1, expression2, expression3, ...)

• expression1, expression2, expression3, ...: A list of expressions, column names, or literal values that you want to evaluate. COALESCE returns the first non-NULL value from this list.

Parameter values

• expression1, expression2, expression3, ...: The expressions, column values, or literal values you want to evaluate for NULL. COALESCE returns the first non-NULL value from this list.

Example query

Suppose we have a table named "employees" with columns "employee_id," "employee_name," and "employee_salary." We want to retrieve the employee names and their salaries, replacing NULL salaries with 0 and employee names with "Unknown":

SELECT employee_id, COALESCE(employee_name, 'Unknown') AS adjusted_name, COALESCE(employee_salary, 0) AS adjusted_salary
FROM employees;

In the above query, we use the COALESCE function to replace NULL employee names with "Unknown" and NULL salaries with 0, ensuring that all employees have meaningful values in the result set.

Example table response

Assuming the "employees" table contains the following data:

| employee_id | employee_name | employee_salary |
|------------ |-------------- | --------------- |
| 1          | John         | 55000           |
| 2          | NULL         | NULL            |
| 3          | Bob          | 60000           |
| 4          | Carol        | 0               |
| 5          | David        | 75000           |

The query mentioned earlier would return the following result:

| employee_id | adjusted_name | adjusted_salary |
|------------ |-------------- | --------------- |
| 1          | John         | 55000           |
| 2          | Unknown      | 0               |
| 3          | Bob          | 60000           |
| 4          | Carol        | 0               |
| 5          | David        | 75000           |

This result includes employee names and adjusted salaries, with NULL values replaced by "Unknown" for employee names and by 0 for salaries.

Use cases

• Handling NULL values to ensure consistent and meaningful data in query results.
• Avoiding disruptions in calculations, comparisons, or presentation due to NULL values.
• Customizing how NULL values are displayed in the result set.
• Selecting the first non-NULL value from a list of options.

SQL languages this is available for

The SQL COALESCE function is a standard SQL function and is available in most relational database management systems (RDBMS) that support SQL. This includes popular RDBMS like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. The specific syntax and behavior may vary slightly between database systems, but the fundamental functionality remains the same.