COALESCE() Function

COALESCE() It is a function that returns the first non-NULL value in a list of expressions. If all the values in the list are NULL, the COALESCE() function will return NULL.

Syntax:

COALESCE(value1, value2, ..., valueN)

The COALESCE() function is often used to replace NULL values with a default value.

Examples:

1. Basic Usage:

   SELECT COALESCE(NULL, NULL, 'ozgur', NULL, 'ozkok');

This will return 'ozgur' because it’s the first non-NULL value in the list.

2. With Tables: Suppose you have a table named orders with columns product_name and alternative_product_name. If you want to select product names, but for rows where the product name is NULL, you want to get the alternative product name, you can use:

   SELECT COALESCE(product_name, alternative_product_name) AS product
   FROM orders;

3. Default Value: If you’re going to replace NULL values with a default value, you can use COALESCE():

   SELECT COALESCE(product_name, 'Default Product') AS product
   FROM orders;

In this example, if product_name is NULL, the query will return 'Default Product'.

COALESCE() It is a standard SQL function, and its behavior is consistent across many relational database systems, not just MySQL.

In essence, the COALESCE() function provides a convenient way to handle NULL values by offering fallbacks or default values, making it a valuable tool in data retrieval and transformation tasks.

MySQL IFNULL Keyword: A Practical Guide