ISNULL and NULLIF functions

MySQL is a popular and widely used DBMS (Database Management System) that helps us store, organize and retrieve our data from the stored database. There are several functionalities in MySQL, making this tool reliable. One of the major functionalities in MySQL is functions. Functions are set of instructions or chunks of code that can be reused to perform certain specific tasks. In this topic, we'll learn about ISNULL() and NULLIF() functions in MySQL.

Let us suppose a company is recording its employees' data. While collecting the data of the employees, certain data have been repeated, and, in some cases, some of the data have been left empty or have yet to be recorded. With such errors, the company may face difficulties in managing the data of the employees. Let us see how we can solve the issues with the functions, ISNULL() and NULLIF().

Here's the data they collected in the table Employees:

ISNULL() function

In the simplest of terms, to understand this function, let us just break down the word ISNULL. Breaking down the word ISNULL, we are asking the question, "Is it NULL (empty)? The ISNULL() function in MySQL is used to check if there are any null values in the database. This function returns a user-specified value if the column/expression is null. We can replace NULLs with any statements or values that signify a NULL statement.

Syntax: ISNULL(expression, Given_Value)

Let us understand with an example:

Suppose we need to check if there are any null values in the column hireDate of the table Employees. We can use the ISNULL() Function to find such null values by:

SELECT EmployeeID, FirstName, LastName, ISNULL(hireDate, 'Not available') AS hireDate
FROM Employees;

The result would be:

In this query, ISNULL(hireDate, 'Not available') checks if any of the values in the column hireDate are null and if they are, the null values in the table are replaced by the statement that is given in the code 'Not available' by the function. If there are no values found the values remain as it is.

NULLIF() Function

The NULLIF() function takes two parameters. If they are equal or if the first parameter is null, it returns NULL, else it returns the first expression.

Syntax: NULLIF(expression1, expression2)

Let us understand the NULLIF() Function with an example:

Let us suppose that the first name and the last name in the table cannot be the same as it may be considered an invalid name. We can do that using NULLIF() function by:

SELECT EmployeeID, NULLIF(firstName, lastName) AS firstName
FROM Employees
WHERE EmployeeID = 1;

The result for the code will be:

In the query above, NULLIF(First_Name, Last_Name) checks the column named First_Name and Last_Name if the selected data where Employee_ID = 1 is the same then it would return a NULL value but in this case, since the value is not the same, the first name remains as it is.

Let us see what happens when the value is null:

SELECT EmployeeID, NULLIF(First_Name, Last_Name) AS FirstName
FROM Employees
WHERE EmployeeID = 8;

The result for the code will be:

Similar to the situation above, the query checks the column firstName and lastName where the employeeID = 8. Since the values are the same the column firstName returns a null value.

There is one more case where the NULLIF returns a null value. It is when the first value of the parameter is empty. Let's see how that works.

SELECT employeeID, NULLIF(firstName, lastName) AS firstName
FROM Employees
WHERE EmployeeID = 4;

The result would be:

Since First_Name where the Employee_ID = 4 is empty, it also returns the null value.

Differences between ISNULL() and NULLIF()

Both the function ISNULL() and NULLIF() are functions that work with null values, but, there are some major differences between them. Let us see the differences:

Conclusion

In today's topic, we got acquainted with two SQL functions: ISNULL() and NULLIF(), here are some main takeaways:

• ISNULL() and NULLIF() are functions that work with NULL values.
• ISNULL() function checks if there are any null values in the database and returns a user-specified expression if the column is null.
• Syntax for ISNULL() function: ISNULL(expression, givenValue).
• NULLIF() function returns a null value if the first and second parameters are equal; otherwise, it returns the value of the first expression.
• Syntax for NULLIF() function: NULLIF(expression1, expression2)