COALESCE

Returns a copy of its first non-NULL argument, or NULL if all arguments are NULL.

This function corresponds to the coalesce(X, Y, ...) function in SQLite. It evaluates its arguments from left to right and returns the first one that is not NULL. All subsequent arguments are not evaluated. If all arguments are NULL, the function returns NULL.

COALESCE is often used to provide a default value for a column that might be NULL. For example, COALESCE(description, 'No description') would return the description if it's not NULL, otherwise it would return the string 'No description'.

The IFNULL function is a two-argument specific case of COALESCE.

Example

// Use nickname if name is NULL, otherwise use name.
val displayName = select(COALESCE(Users.name, Users.nickname))
    .from(Users)

// Provide a default value for a nullable column.
val emailOrDefault = select(COALESCE(Users.email, StringLiteral("N/A")))
    .from(Users)

Parameters

See also