The IFNULL() function in MySQL is a useful tool that allows you to replace NULL values with a specified value.
Points To Remember:
The IFNULL() function takes two arguments: the expression to be evaluated, and the replacement value to be used if the expression is NULL.
The IFNULL() function returns the first non-NULL value in the expression. If the expression is NULL, the function returns the specified replacement value.
The IFNULL() function can be used in SELECT, UPDATE, and SET statements.
The IFNULL() function is often used to replace NULL values in columns with a default value, to avoid errors when performing calculations or aggregating data.
The IFNULL() function is similar to the ISNULL() function, but the ISNULL() function only returns a Boolean value (1 or 0) indicating whether the expression is NULL or not. The IFNULL() function returns the first non-NULL value in the expression, or the specified replacement value if the expression is NULL.
IFNULL() Function:
NULL values can cause problems in your database, especially if you are trying to perform calculations or aggregate data.
The IFNULL() function is a simple way to solve these issues and ensure that your data is consistent and accurate.
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 IFNULL function to convert the NULL values in original_language_id into 1000.
SELECT film_id, title, description, IFNULL(original_language_id,1000)
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.