21. MySQL: COALESCE() Function

The COALESCE function in MySQL is used to return the first non-NULL value in a list of expressions. If all expressions are NULL, the function returns NULL.

This function is useful for handling missing or optional data, replacing NULL values with a default value, or simply making the result of a query easier to understand.

POINTS TO REMEMBER:

1. COALESCE returns the first non-NULL value in a list of expressions. If all expressions are NULL, the function returns NULL.

2. COALESCE only evaluates the expressions that are necessary to determine the first non-NULL value. It stops evaluating expressions as soon as it finds a non-NULL value.

---

COALESCE() function:

The COALESCE function in MySQL is used to return the first non-NULL value in a list of expressions. If all expressions are NULL, the function returns NULL.

Example:

Let's see some records in the film table which have null original_language_id asNULL.

SELECT film_id, title, description, original_language_id
FROM film
WHERE original_language_id IS NULL
AND film_id IN ('1','2','3');

Output:

Now we will extract the same records, but while extracting we will use the COALESCE() function to convert the NULL values in original_language_id into 6.

SELECT film_id, title, description, COALESCE(film.original_language_id, 6) AS original_language_id
FROM film
where film_id in (1,2,3);

Output:

---

Connect with me on LinkedIn: linkedin.com/in/nikhil-shenoy07

To see more examples, you can always refer w3 schools; it has always been my go-to website to understand things.

*****All the outputs provided are a snippet of the actual result.