COALESCE expression

In SQL, the COALESCE function is used to return the first non-null expression in a list of expressions. It takes multiple arguments and returns the value of the first expression that is not null. If all expressions are null, it returns null.

The syntax for the COALESCE function is as follows:

COALESCE(expression1, expression2,...,expressionN)

In this syntax, expression1, expression2,...,expressionN are the expressions to be evaluated. The function returns the value of the first expression that is not null.

Here's an example of how to use the COALESCE function in SQL:

SELECT COALESCE(customer_name, 'Anonymous') AS name FROM customers;

In this example, the COALESCE function is used to return the customer_name if it is not null. If the customer_name is null, then the function returns the string 'Anonymous' instead. This allows you to handle null values by providing a default value when a value is missing.

More examples, how the COALESCE function can be used in SQL:

1. Using COALESCE to handle null values in a WHERE clause

SELECT product_name, price FROM products WHERE COALESCE(discounted_price, price) < 50;

In this example, the COALESCE function is used to handle null values in the discounted_price column. If discounted_price is null then the function returns the value of the price column instead. This allows to filter products based on their discounted price if it's available or their regular price if it's not.

2. Using COALESCE to provide a default value

SELECT product_name, COALESCE(description, 'No description available') AS description FROM products;

In this example, the COALESCE function is used to provide a default value for the description column. If description is null then the function returns the string 'No description available' instead. This allows to display a more informative message to users when no description is available.

3. Using COALESCE to concatenate values from multiple columns

SELECT COALESCE(first_name || ' ', ' ') || COALESCE(last_name, 'Unknown') AS full_name FROM customers;

In this example, the COALESCE function is used to concatenate the first_name and last_name columns into a single full_name column. If first_name is null then the function returns an empty string instead. If last_name is null then the function returns the string 'Unknown' instead. This allows you to handle null values in a more efficient way when concatenating strings.