Skip to main content

coalesce

1. COALESCE Function​

The COALESCE function in H2 database is used to return the first non-null value from a list of expressions.

2. Syntax​

The syntax for the COALESCE function in H2 database is as follows:

COALESCE(expression1, expression2, ...)

Arguments​

  • expression1, expression2, ...: The expressions to be evaluated. These can be literals, column names, or other functions.

Return​

  • The COALESCE function returns the first non-null value among the given expressions.

3. Notes​

  • The COALESCE function in H2 database evaluates the expressions in the order they are provided and returns the first non-null value.
  • If all the expressions evaluate to null, the COALESCE function will return null.
  • It is important to ensure that the expressions have compatible datatypes, as the COALESCE function will return the datatype of the first non-null expression.

4. Examples​

Here are a few examples demonstrating the usage of the COALESCE function in H2 database:

Example 1 - Returning the first non-null value:​

SELECT COALESCE(NULL, 'Hello', 'World') AS result;

Output:

result
------
Hello

Example 2 - Returning the first non-null value from a column:​

CREATE TABLE items (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  description VARCHAR(200),
  price DOUBLE
);

INSERT INTO items VALUES (1, 'Item A', NULL, 10.99), (2, 'Item B', 'Description B', NULL), (3, 'Item C', 'Description C', 15.99);

SELECT id, COALESCE(description, 'No description available') AS description
FROM items;

Output:

id |     description
---+-----------------------
1  | No description available
2  | Description B
3  | Description C
  • IFNULL - Return alternative value if expression is null
  • nullif - Return null if two expressions are equal