MySQL IFNULL() function

Last update on June 29 2024 07:45:23 (UTC/GMT +8 hours)

IFNULL() function

The IFNULL() function in MySQL is a versatile and straightforward way to handle NULL values in your data. In databases, NULL values often represent missing or unknown information, which can complicate queries and data processing.

By using IFNULL(), you can provide default values and ensure that your queries return meaningful results even when some data is missing. This function is particularly useful in data cleaning, reporting, and transforming data for better readability and usability.

MySQL IFNULL() takes two expressions and if the first expression is not NULL, it returns the first expression. Otherwise, it returns the second expression. Depending on the context in which it is used, it returns either numeric or string value.

Syntax:

IFNULL(expression1, expression2);

Arguments:

Name	Description
expression1	The first expression to evaluate. If this expression is not NULL, IFNULL() returns this value.
expression2	The second expression to evaluate. If expression1 is NULL, IFNULL() returns this value.

Return Type:

The return type of IFNULL() depends on the context and types of the provided expressions. If the expressions are numeric, the return type will be numeric. If they are strings, the return type will be a string.

MySQL Version: 8.0

Comparing with Other Functions:

COALESCE(): Unlike IFNULL(), which only takes two arguments, COALESCE() can handle multiple arguments and returns the first non-NULL value from the list.

NULLIF(): Returns NULL if the two expressions are equal; otherwise, it returns the first expression.

Example: MySQL IFNULL()

IFNULL() function with zero 1st argument:

The following MySQL statement returns the first expression, i.e. 0, since the first expression is not NULL.

Code:


-- In this example, the first argument is 0 (which is not NULL),
-- and the second argument is 2.
SELECT IFNULL(0, 2);
-- The expected result will be 0 because the first argument is not NULL.

Explanation:

This SQL query uses the IFNULL() function to handle potential NULL values.

The IFNULL() function evaluates the first argument (0). Since 0 is not NULL, the function returns this value, ignoring the second argument (2).

Hence, the query returns 0 as the output. This function is useful for providing default values when handling potential NULLs in SQL queries.

Output:

mysql> SELECT IFNULL(0,2);
+-------------+
| IFNULL(0,2) |
+-------------+
|           0 | 
+-------------+
1 row in set (0.03 sec)

IFNULL() function with non zero 1st argument:

The following MySQL statement returns the first expression, i.e. 1, since the first expression is not NULL.

Code:


-- In this example, the first argument is 1 (which is not NULL),
-- and the second argument is 2.
SELECT IFNULL(1, 2);
-- The expected result will be 1 because the first argument is not NULL.

Explanation:

The SELECT IFNULL(1, 2); query utilizes the IFNULL() function to determine which value to return.

The function checks the first argument (1). Since 1 is not NULL, the function returns this value without considering the second argument (2).

Thus, the query returns 1 as the output.

The IFNULL() function is handy for replacing NULLs with a specified value, ensuring that a non-NULL result is always produced.

Output:

mysql> SELECT IFNULL(1,2);
+-------------+
| IFNULL(1,2) |
+-------------+
|           1 | 
+-------------+
1 row in set (0.00 sec)

IFNULL() function with string:

This SQL statement demonstrates the use of the IFNULL() function in MySQL with a NULL first argument and a string second argument.

Code:


-- In this example, the first argument is NULL,
-- and the second argument is a string 'default value'.
SELECT IFNULL(NULL, 'default value') AS result;
-- The expected result will be 'default value' because the first argument is NULL.
-- The result is labeled as 'result' using the AS keyword for better readability.

Explanation:

The query SELECT IFNULL(NULL, 'default value') AS result; uses the IFNULL() function to handle a situation where the first argument is NULL.

Since the first argument is NULL, the function returns the second argument, which is the string 'default value'.

Therefore, the output of this query is 'default value'. The use of AS result renames the output column to result, providing a clear and meaningful label for the returned value.

This function is particularly useful for substituting NULL values with a specific fallback value, ensuring consistent and predictable outputs.

Output:

result       |
-------------+
default value|

IFNULL() function with number:

This SQL statement demonstrates the use of the IFNULL() function in MySQL with a NULL first argument and a numeric second argument.

Code:


-- In this example, the first argument is NULL,
-- and the second argument is the number 2.
SELECT IFNULL(NULL, 2);
-- The expected result will be 2 because the first argument is NULL.
-- Since expression1 is NULL, the function returns expression2.

Explanation:

The query SELECT IFNULL(NULL, 2); uses the IFNULL() function to provide a default value when dealing with NULL values.

Here, since the first argument is NULL, the IFNULL() function returns the second argument, which is 2.

This behavior ensures that instead of returning NULL, the query outputs the fallback value 2.

This is useful in scenarios where you want to replace potential NULL values with a specific number or a default value to maintain data consistency.

Output:

mysql> SELECT IFNULL(NULL,2);
+----------------+
| IFNULL(NULL,2) |
+----------------+
|              2 | 
+----------------+
1 row in set (0.00 sec)

IFNULL() function with date:

This SQL statement demonstrates the use of the IFNULL() function in MySQL with a NULL first argument and the CURDATE() function as the second argument.

Code:


-- In this example, the first argument is NULL,
-- and the second argument is the CURDATE() function.
-- CURDATE() is a MySQL function that returns the current date in 'YYYY-MM-DD' format.
SELECT IFNULL(NULL, CURDATE()) AS "current date";
-- The expected result will be the current date because the first argument is NULL.
-- Since expression1 is NULL, the function returns the value from the CURDATE() function.

Explanation:

The query SELECT IFNULL(NULL, CURDATE()) AS "current date"; uses the IFNULL() function to handle a potential NULL value by providing a fallback.

Here, the first argument is NULL, and the second argument is the CURDATE() function, which returns the current date in the format 'YYYY-MM-DD'.

Since the first argument is NULL, the IFNULL() function returns the result of CURDATE(), which is the current date.

This usage is particularly useful for ensuring that a date value is always returned, even if the original value is NULL.

Output:

current date|
------------+
  2024-06-28|

Example in complex queries:

This SQL query retrieves customer information from the 'customer' table.

Code:


SELECT 
    cust_code,  -- Selects the 'cust_code' column, which typically represents the unique customer identifier.
    IFNULL(phone_no, 'No phone provided') AS contact_info -- Uses the IFNULL() function to check the 'phone_no' column.
                                                          -- If 'phone_no' is NULL, it returns the string 'No phone provided'.
                                                          -- If 'phone_no' is not NULL, it returns the actual phone number.
FROM 
    customer; -- Specifies the 'customer' table as the source of data.

Explanation:

This query fetches customer data from the customer table.

It selects two columns: cust_code, which is likely a unique identifier for each customer, and contact_info, which uses the IFNULL() function to handle cases where a customer’s phone number (phone_no) is NULL.

If phone_no is NULL, the query returns the string 'No phone provided' in the contact_info column. Otherwise, it returns the actual phone number.

This approach ensures that there is always some contact information available for each customer in the result set, making it clear when a phone number is missing.

Output:

cust_code|contact_info|
---------+------------+
C00018   |NHBGVFC     |
C00021   |WERTGDF     |
C00019   |ZZZZBFV     |
C00005   |147-25896312|
C00007   |GHRDWSD     |
C00022   |113-12345678|
.........

Example with Joins:

This SQL query retrieves department and employee information from the 'departments' and 'employees' tables.

Code:


SELECT 
    departments.department_id,  
	-- Selects the 'department_id' column from the 'departments' 
	--table, representing the unique identifier for each department.
    IFNULL(employees.emp_name, 'Guest') AS employee_name 
	-- Uses the IFNULL() function to check the 'emp_name' column from the 'employees' table.
    -- If 'emp_name' is NULL, it returns the string 'Guest'.
    -- If 'emp_name' is not NULL, it returns the actual employee name.
FROM 
    employees -- Specifies the 'employees' table as the primary source of data for the query.
LEFT JOIN 
    departments ON departments.department_id = employees.department_id; 
	-- Performs a LEFT JOIN between the 'employees' and 'departments' tables.
    -- Matches records based on the 'department_id' column.
    -- This join includes all records from 'employees' and the matching records from 'departments'.
    -- If there is no matching 'department_id' in 'departments', 
	--the result will include NULL for 'departments' columns.

Explanation:

This SQL query retrieves information about departments and employees from the departments and employees tables.

It uses a LEFT JOIN to combine data from both tables based on the department_id column.

This join includes all rows from the employees table and the matching rows from the departments table.

The SELECT clause includes:

departments.department_id: The unique identifier for each department.

An alias employee_name, which uses the IFNULL() function to handle cases where an employee's name (emp_name) might be NULL. If emp_name is NULL, the query returns 'Guest'; otherwise, it returns the actual employee name.

Output:

department_id|employee_name|
-------------+-------------+
           90|Steven       |
           90|Neena        |
           90|Lex          |
           60|Alexander    |
           60|Bruce        |
.........

N.B.: In the above example the no name in the 'employees' table are NULL.

Frequently Asked Questions (FAQ) - MySQL IFNULL() Function

1. What is the MySQL IFNULL() function?

The IFNULL() function in MySQL is used to handle NULL values by providing a default value. This ensures that queries return meaningful results even when some data might be missing or unknown.

2. How does the IFNULL() function work?

The IFNULL() function takes two arguments. If the first argument is not NULL, it returns the first argument. If the first argument is NULL, it returns the second argument.

3. In what scenarios is IFNULL() useful?

IFNULL() is particularly useful in scenarios such as data cleaning, reporting, and data transformation. It helps to replace NULL values with specified default values, enhancing data readability and usability.

4. What are the benefits of using the IFNULL() function in MySQL?

Using IFNULL() ensures that queries provide consistent and non-NULL results, which is crucial for accurate data processing and reporting. It simplifies handling of NULL values, which can otherwise complicate query logic.

5. How does the return type of IFNULL() function work?

The return type of the IFNULL() function depends on the types of the provided expressions. If the arguments are numeric, it returns a numeric value. If the arguments are strings, it returns a string value.

6. Which MySQL versions support the IFNULL() function?

The IFNULL() function is supported in MySQL version 8.0 and later.

7. How does IFNULL() compare with other functions like COALESCE() and NULLIF()?

COALESCE(): Unlike IFNULL(), which only handles two arguments, COALESCE() can take multiple arguments and returns the first non-NULL value.

NULLIF(): This function returns NULL if the two arguments are equal; otherwise, it returns the first argument. It is used for a different purpose than IFNULL().

8. Why is it important to handle NULL values in SQL?

Handling NULL values is important because NULL represents missing or unknown information. Without proper handling, NULL values can lead to inaccurate results in queries and reports, complicating data processing and analysis.

Previous: IF()
Next: NULLIF()