In this tutorial, we will look at the coalesce function in detail and explore how we can use it in various SQL operations.
Function Syntax:
The following shows the syntax of the coalesce function:
In this case, the function evaluates the provided expressions. This can be basic expressions, values, or table columns.
The function then returns the first non-null value when evaluating the expressions.
Example 1: Basic Usage
Let us start with the basics of a simple coalesce function usage. Suppose we have a table called “employees” with the first name, last name, and middle name columns.
Let us assume that in this table, there are some employees who do not have a middle name while others do.
If we want to create a full name of the employees by combining the three columns, it can lead to problems where the middle name is NULL.
To avoid such an issue, we can use the coalesce function as demonstrated in the following example query:
first_name,
COALESCE(middle_name, '') AS middle_name,
last_name,
CONCAT_WS(' ', first_name, COALESCE(middle_name, ''), last_name) AS full_name
FROM employees;
In this query, we use the coalesce function to handle the cases where the value of the “middle_name” column is NULL.
The function evaluates the value. If it is NULL, the function returns an empty string which ensures that the concatenation does not fail.
Example 2: Default Value
We can also use the coalesce function to assign a default value in case the column is NULL.
Take for example, we have a table called “products” with the “product_id”, “product_name”, and “price” columns.
Some products may not have a specified price, and we want to display a default price of 0.00 for such products.
We can accomplish this with the coalesce function as follows:
product_id,
product_name,
COALESCE(price, 0.00) AS price
FROM products;
This should set the price to 0.00 for any NULL value in the “price” column.
Conclusion
In this post, we covered the fundamentals of working with the coalesce function including how to handle the null values and how to work with default values.
