COALESCE
A table can hold empty (null) and filled (non-null) values. Yet, often, we prefer to overlook those empty values. This is where COALESCE() steps in.
COALESCE() function helps us when we want to ignore null values while processing data. It returns the first argument that is not null, while the remaining arguments from the first non-null argument are not evaluated.
If all arguments are null, the COALESCE function will return null.
The syntax for the COALESCE() function is as follows:
Key points from the syntax:
- COALESCE() requires a minimum of two inputs.
- It can take an unlimited number of arguments.
- Evaluation occurs sequentially from left to right, stopping at the first non-null value.
Here are some examples to illustrate the application of COALESCE():
In this example, we have a set of values. By using the COALESCE() function, we're going to get the first non-null value from this set.
The result will be 9, the first value without null among the provided options.
Letβs include NULL as the final argument and check the query output.
The function output is 3 because it returns the first non-null value.
Consider NULL as the first argument in the following example.
The output is 1, as it is the first non-null value of the argument.
In the following query, NULL appears in the first, second, fourth, and last positions.
The COALESCE() function ignores the first two NULLs and returns the first non-null value, 3. It does not process the subsequent NULL values.
Assume that the given values are entirely composed of nulls.
In this case, the COALESCE() function returns an empty value (null).
Imagine we have the employee_absent table, which comprises a mix of NULL and non-null values:
Use the SELECT statement to display all the records:
The query below uses the COALESCE() function on the absent column. It retrieves names and absences (with out of office for NULL values) for each employee.
When specifying arguments with different datatypes, they should be convertible.
If the datatypes cannot be converted, the COALESCE() function will generate an error, as shown below.
ο»Ώ