How the NULLIF() function works in Mariadb?

The NULLIF() function is a useful function in Mariadb that returns NULL if two expressions are equal, or the first expression if they are not. This function can be used to avoid division by zero errors, handle missing values, or simplify complex queries.

Syntax

The syntax of the NULLIF() function is as follows:

NULLIF(expr1, expr2)

The function takes two arguments, expr1 and expr2, which can be any valid expressions of the same or compatible data types. The function compares expr1 and expr2 using the equal (=) operator, and returns NULL if they are equal, or expr1 if they are not.

Examples

Let’s look at some examples of how to use the NULLIF() function in Mariadb.

Example 1: Avoiding division by zero errors

One common use case of the NULLIF() function is to avoid division by zero errors. For example, suppose we have a table called sales that stores the sales data of different products, as shown below:

If we want to calculate the average price per unit for each product, we can use the following query:

SELECT product, price / quantity AS avg_price
FROM sales;

However, this query will fail with a division by zero error, because the quantity of product C is zero. To avoid this error, we can use the NULLIF() function to replace the zero quantity with NULL, as shown below:

SELECT product, price / NULLIF(quantity, 0) AS avg_price
FROM sales;

This query will return the following result:

| product | avg_price |
| ------- | --------- |
| A       | 0.1       |
| B       | 0.1       |
| C       | NULL      |
| D       | 0.8       |

As you can see, the NULLIF() function returns NULL for the product C, which avoids the division by zero error and indicates that the average price is not applicable for that product.

Example 2: Handling missing values

Another use case of the NULLIF() function is to handle missing values in the data. For example, suppose we have a table called customers that stores the customer information, as shown below:

If we want to display the contact information of each customer, we can use the following query:

SELECT name, email, phone
FROM customers;

However, this query will return some NULL values for the email and phone columns, which may not be desirable. To replace the NULL values with some default values, we can use the NULLIF() function along with the COALESCE() function, as shown below:

SELECT name, COALESCE(NULLIF(email, ''), 'N/A') AS email, COALESCE(NULLIF(phone, ''), 'N/A') AS phone
FROM customers;

This query will return the following result:

| name  | email             | phone      |
| ----- | ----------------- | ---------- |
| Alice | [email protected] | 1234567890 |
| Bob   | [email protected]   | N/A        |
| Carol | N/A               | 9876543210 |
| David | [email protected] | N/A        |

As you can see, the NULLIF() function returns NULL for the empty strings, and the COALESCE() function returns the first non-null value from the list of arguments. This way, we can handle the missing values and display some default values instead.

Example 3: Simplifying complex queries

The NULLIF() function can also be used to simplify complex queries that involve conditional logic. For example, suppose we have a table called employees that stores the employee information, as shown below:

If we want to calculate the total income of each employee, we can use the following query:

SELECT name, salary + bonus AS income
FROM employees;

However, this query will return zero for the income of David, because his bonus is zero. To avoid this, we can use the NULLIF() function to replace the zero bonus with NULL, as shown below:

SELECT name, salary + NULLIF(bonus, 0) AS income
FROM employees;

This query will return the following result:

| name  | income |
| ----- | ------ |
| Alice | 5500   |
| Bob   | 6600   |
| Carol | 7700   |
| David | 8000   |

As you can see, the NULLIF() function returns NULL for the zero bonus, and the addition operator returns the salary as the income for David. This way, we can simplify the query and avoid using a CASE expression or an IF() function.

Related Functions

There are some other functions in Mariadb that are related to the NULLIF() function, such as:

• The IFNULL() function, which returns the first argument if it is not NULL, or the second argument if it is. For example, IFNULL(NULL, 'N/A') returns 'N/A'.
• The COALESCE() function, which returns the first non-null value from a list of arguments. For example, COALESCE(NULL, '', 'N/A') returns ''.
• The ISNULL() function, which returns 1 if the argument is NULL, or 0 otherwise. For example, ISNULL(NULL) returns 1.

Conclusion

In this article, we have learned how the NULLIF() function works in Mariadb, and how to use it to avoid division by zero errors, handle missing values, or simplify complex queries. We have also seen some examples and related functions that can help us work with NULL values in Mariadb.