Skip to main content

nullif

1. NULLIF Function​

The NULLIF function in H2 database is used to compare two expressions and return NULL if they are equal, or the first expression otherwise.

2. Syntax​

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

NULLIF(expression1, expression2)

Arguments​

  • expression1: The first expression to compare.
  • expression2: The second expression to compare.

Return​

  • If expression1 and expression2 are equal, the NULLIF function returns NULL. Otherwise, it returns expression1.

3. Notes​

  • The NULLIF function in H2 database is commonly used to handle situations where dividing by zero could occur.
  • It is important to ensure that the two expressions being compared have compatible data types. If they have incompatible data types, an error may occur.
  • NULLIF is a conditional function that simplifies handling specific cases where NULL values need to be controlled.

4. Examples​

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

Example 1 - Comparing two expressions:​

SELECT NULLIF(5, 5) AS result;

Output:

result
------
NULL

Example 2 - Handling division by zero:​

SELECT 10 / NULLIF(0, 0) AS result;

Output:

result
------
NULL

Example 3 - Using NULLIF in a CASE statement:​

SELECT id, name,
  CASE NULLIF(age, 0)
    WHEN NULL THEN 'Unknown'
    ELSE age
  END AS modified_age
FROM users;

Output:

id | name  | modified_age
---+-------+--------------
1  | John  | 25
2  | Alice | 0
3  | Bob   | Unknown
  • coalesce - Returns the first non-null expression from a set of expressions.
  • IFNULL - Returns the first expression if it is not null, otherwise returns the second expression.