Usage of coalesce function in sql

The COALESCE function in SQL is used to handle NULL values ​​and return the first non-NULL value in the expression list. If all expressions are NULL, return the default value. It is mainly used for replacing NULL values, providing default values ​​and cascading checks. To replace NULL values, use COALESCE(expr1, expr2, ...). To provide a default value, use COALESCE(expr1, expr2, ..., default_value). For cascading checks, COALESCE(expr1, expr2, ...) can be used.

Usage of COALESCE function in SQL

The COALESCE function is a commonly used function in SQL and is used to handle NULL values. . It takes multiple expressions as a parameter list and returns the first non-NULL value. If all expressions are NULL, the default value is returned.

Syntax

##COALESCE(expr1, expr2, ..., exprN, default_value)

Among them:

• expr1, expr2, ..., exprN: The expression to check.
• default_value: The default value returned if all expressions are NULL (optional).

Usage

The COALESCE function is mainly used to handle the following scenarios:

• Replace NULL values: The COALESCE function ensures data integrity by replacing NULL values ​​with other non-NULL values.
• Provide a default value: When no data is available, the COALESCE function can provide a default value to prevent errors or unexpected results.
• Cascade checking: The COALESCE function can cascade check multiple expressions until a non-NULL value is found.

Example

1. Replace NULL value

<code class="sql">SELECT COALESCE(name, 'Unknown') FROM users;</code>

This will

name All NULL values ​​in the column are replaced with the string "Unknown".

2. Provide a default value of

<code class="sql">SELECT COALESCE(age, 0) FROM users;</code>

This will replace all NULL values ​​with the number 0 in the

age column.

3. Cascade Check

<code class="sql">SELECT COALESCE(address, city, country) FROM users;</code>

This will first check the value in the

address column. If address is NULL, it checks the value of the city column. If city is also NULL, returns the value of the country column.

Note

The COALESCE function does not accept an empty parameter list.
• The COALESCE function returns the first non-NULL value, rather than a list of all non-NULL values ​​in the expression list.
• If all expressions are NULL and no
• default_value is provided, the COALESCE function returns NULL.

The above is the detailed content of Usage of coalesce function in sql. For more information, please follow other related articles on the PHP Chinese website!