Hit enter after type your search item
Home / SQL Tutorials / How to use IFNULL Function in MySQL?

How to use IFNULL Function in MySQL?

The IFNULL function replaces the NULL values by given expression. For example:

SELECT IFNULL(NULL, “alt_val”) AS “Alternative Value”;

In MySQL database tables, the NULL values can be stored if a column is created with Allow NULLs option.

For generating reports or viewing data generally or calculating numeric column values, you may want to replace NULL values in a column with the specific given value.

The MySQL IFNULL function enables us replacing the NULL values to the given values. These can be string, numbers, date etc.

As it can be seen in above statement, the first argument in the IFNULL SQL function is to check if the expression is NULL. You may provide a static value or a table column there. If that value is NULL, this is replaced by the second argument.

If not NULL then IFNULL returns the same static value or table column value. I will show you both in the examples below.

The example of using static value in IFNULL function

Let me start with a simple example by using a NULL expression and an alternative value in the IFNULL function. See the query and output:

Query:


The result:

SQL IFNULL example

You can see, the NULL is replaced by the second argument value.

What if the first expression is not NULL?

See the query and output below when I provided a non-null value in the MySQL IFNULL function. See if this is replaced by the second argument or not:


The result:

SQL IFNULL not null

The output shows that the value is not replaced by second argument this time.

Did you know? The SQL IFNULL function is available in MySQL database. In MS SQL Server, the function for checking and replacing the NULL values is ISNULL function.

The same can be done by NVL() function in Oracle database.

Using table column in IFNULL function

Now let us test this function in MySQL database table. For that, we have a demo table, tbl_products with a few entries.

The table contains product names, quality, and quantity. The last record contains the NULL value for quantity column.

See the two queries below where the first one is used to fetch table records without using the IFNULL function. In the second query, I used the IFNULL function and specified the product_qunatity column and also provided the replacement value. See the difference:


The resultsets:

SQL IFNULL table

You can see, all the values for product_qunaity columns are unchanged in the second query except the last record. As it contained a NULL value, so this is replaced by “100 Bags” by IFNULL function.

Using the alternative value for numeric column example

For showing the numeric column replacement of values by using IFNULL function, I added a product price column in the tbl_products table (just for the sake of demonstration).

The initial values are NULL for all rows in the table. By using IFNULL function, I will change each row value from NULL to 50.99. Have a look:


The result sets with original and after using IFNULL function:

SQL IFNULL numeric

You can see, all rows are showing 50.99 for the product price column. The numeric value is provided without quotes.

This div height required for enabling the sticky sidebar