The IFNULL function replaces the NULL values with a given expression. For example:
- 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 to replace the NULL values with the given values.
- These can be strings, numbers, or dates.
- This can be seen in the above statement that 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:
SELECT IFNULL(NULL, "Some other value") AS "Alternative Value";
The result:
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:
SELECT IFNULL("Not NULL", "Some other value") AS "Alternative Value";
The result:
The output shows that the value is not replaced by the second argument this time.
Using the 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 the 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:
SELECT PRODUCT_Name, Product_quality, Product_quantity FROm tbl_products; SELECT PRODUCT_Name, Product_quality, IFNULL(Product_quantity,"100 Bags") FROm tbl_products;
The resultsets:
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, this is replaced by “100 Bags” by the IFNULL function.
Using the alternative value for numeric column example
For showing the numeric column replacement of values by using the 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:
SELECT PRODUCT_Name, Product_quality, IFNULL(Product_quantity,"100 Bags") AS "Product Quantity", IFNULL(Product_price, 50.99) AS "Product Price" FROM tbl_products;
The result sets with the original and after using IFNULL function:
You can see, all rows are showing 50.99 for the product price column. The numeric value is provided without quotes.