MySQL ISNULL

Parameters of MySQL ISNULL() Function

The MySQL ISNULL() function is a versatile tool for handling null values within database queries. It typically takes one parameter, although it can accept multiple parameters in certain contexts:

• expression: This is the value or column to be evaluated for nullity. It can be any valid expression, such as a column name, a literal value, or a function that returns a value.

Return Value of MySQL ISNULL() Function

The return value of the MySQL ISNULL() function is a boolean result indicating whether the evaluated expression is null or not. Specifically:

• If the expression is null, ISNULL() returns 1 (true).
• If the expression is not null, ISNULL() returns 0 (false).

Functionality of MySQL's ISNULL() Function

The MySQL ISNULL() function serves several key purposes in database operations:

• Null Value Detection: The primary purpose of ISNULL() is to detect null values within database queries. It evaluates an expression and returns true (1) if the expression is null, and false (0) otherwise. This functionality is essential for filtering, conditional logic, and data validation.
• Handling Null Values in Conditions: ISNULL() enables developers to construct conditional statements based on the presence or absence of null values. By incorporating ISNULL() within WHERE clauses or CASE statements, users can tailor query logic to accommodate null values appropriately.
• Default Value Assignment: In some scenarios, it's necessary to assign default values to null entries within query results. ISNULL() facilitates this task by providing a concise mechanism to substitute null values with predetermined defaults using the IFNULL() function or conditional expressions.
• Data Cleaning and Transformation: ISNULL() plays a vital role in data preprocessing tasks, allowing users to identify null values and perform necessary transformations or replacements. This functionality is particularly useful when preparing data for analysis, reporting, or migration.
• Complex Query Construction: When constructing complex queries involving multiple conditions or nested expressions, ISNULL() serves as a fundamental building block. Its ability to accurately identify null values enables developers to create robust and precise query logic tailored to specific business requirements.

Now, Let’s see some examples of the ISNULL Function in different scenarios.

Examples

Example 1: ISNULL Function having Expression as Column

mysql> SELECT * FROM Items;
-------------+-------------------------------
| ItemNumber |                  ItemPrice   |
|------------|----------------------------  |
| 1          |                          500 |
| 2          |                          200 |
| 3          |                         NULL |
| 41         |                         NULL |
| 5          |                           10 |
| 6          |                         NULL |
-------------+------------------------------
6 rows in set (0.00 sec)
mysql> SELECT IFNULL(SUM(ItemPrice), 0) as Total_price FROM Items;
+------------+
| Total_price |
+------------+
|         710 |
+------------+
1 row in set (0.02 sec)
mysql>

Code 

SELECT * FROM RANKHOLDERS;
UPDATE RANKHOLDERS SET PhoneNumber = 22567 Where Position = 2;
SELECT FirstName, SecondName FROM RankHolders Where ISNULL(PhoneNumber);

Output

In this example, we have a table ‘RankHolders’ with columns ‘Position,’ ‘FirstName,’ ‘SecondName’, and ‘PhoneNumber.’ Initially, all the records had NULL in their ‘PhoneNumber’ column. After that, we set the ‘PhoneNumber’ of the records with ‘Position’ equal to 2. At last, we are using the ISNULL Function to check which records have NULL in their ‘PhoneNumber’ column.

Example 2: ISNULL Function with IF Statement

mysql> SELECT * FROM Store;
+------------+---------------------------------------------------------------------------------------------------------------------------------------+-------+
| Model Number |                                                 ModelName                                                                          | Price |
+------------+---------------------------------------------------------------------------------------------------------------------------------------+-------+
|          102 | DELL XPS 13  Lenovo Ideapad                                                                                                         | 55000 |
|          404 |                                             Acer Extense                                                                             | NULL  |
|          540 |              Acer Extense                                                                             | NULL  |
|          35  |                 HP 14                                                                                   | 53000 |
|          71  |                 DELL New Vostro                                                                         | 48000 |
+------------+---------------------------------------------------------------------------------------------------------------------------------------+-------+
5 rows in set (0.00 sec)
mysql> SELECT ModelName,                     
            ->                     IF(ISNULL(Price), 'Price is not Updated', Price) AS Price_Status
            -> FROM Store;
+-----------------------------------------------------------------------------+----------------------+
| ModelName                                                                   |  Price_Status        |
+---------------------------------------------------------------------------------------+------------+
| DELL XPS 13  Lenovo Ideapad                                                 | Price is not Updated |
| Acer Extense                                                                | Price is not Updated |
| Acer Extense                                                                | Price is not Updated |
| HP 14                                                                       | 53000                |
| DELL New Vostro                                                             | 48000                |
+---------------------------------------------------------------------------------------+------------+
5 rows in set (0.00 sec)
mysql>

Code

SELECT * FROM Store;
SELECT ModelName, IF(ISNULL(Price), ‘Price is not updated’, Price) As Price_Status FROM Store;

Output

In this example, we have a table ‘Store’ with columns ‘ModelNumber,’ ‘ModelName’, and ‘Price.’  The two records have NULL values in their ‘Price’ column. We use the ISNULL Function with the IF Statement to print the ‘ModelName’ and the ‘Price’ columns with a message replacing the NULL values for every record.

Example 3: ISNULL Function with Case Statement

mysql> SELECT * FROM Auditions;                                        
+--------+------+-------+
| Name  | Age  | Gender |
+--------+------+-------+
| John  | 24   | Male   |
| kat   | NULL | Female |
| Adam  | 30   | Male   |
| Taylor| NULL | Male   |
| Emma  | NULL | Female |
+--------+------+-------+
5 rows in set (0.01 sec)
mysql> SELECT Name, COALESCE (Age, 20) AS Age, Gender FROM Auditions;
+--------+------+-------+
| Name  | Age  | Gender |
+--------+------+-------+
| John  | 24   | Male   |
| kat   | 20   | Female |
| Adam  | 30   | Male   |
| Taylor| 20   | Male   |
| Emma  | 20   | Female |
+--------+------+-------+
5 rows in set (0.00 sec)
mysql>

Code

SELECT * FROM STUDENT
SELECT FirstName, SecondName, CASE WHEN ISNULL(ADDRESS) THEN ‘Unknown Address’ ELSE Address END AS Student_info FROM Student;

Output

In this example, a ‘Student’ table has columns ‘RollNo,’ ‘FirstName,’ ‘SecondName’, and ‘Address.’ The two records have stored NULL in their ‘Address’ column. We are using ISNULL with the Case Statement by checking whether the ‘Address’ of any record is NULL or not. If it gets a NULL ‘Address’ for any record, it will replace it with the message ‘Unknown Address’; otherwise, it will take the value stored in that column.

Example 4: ISNULL Function with COUNT Function

mysql> SELECT * FROM Auditions;
| Name | Age | Gender |
|-------|------|-------|
| John  | 24   | Male   |
| kat   | NULL | Female |
| Adam  | 30   | Male   |
| Taylor| NULL | Male   |
| Emma  | NULL | Female |
5 rows in set (0.01 sec)
mysql> SELECT Name, COALESCE (Age, 20) AS Age, Gender FROM Auditions;
| Name | Age | Gender |
|-------|------|-------|
| John  | 24   | Male   |
| kat   | 20   | Female |
| Adam  | 30   | Male   |
| Taylor| 20   | Male   |
| Emma  | 20   | Female |
5 rows in set (0.00 sec)
mysql>

Code

SELECT * FROM TASK_Assignment
SELECT COUNT(*) AS Not_Assigned FROM Task_Assignment WHERE ISNULL(Total_tasks);

Output

Now we have a ‘Task_Assignment’ table with columns ‘Employee_id’ and ‘Total_tasks’. In this example we want to count the number of Employees having NULL stored in ‘Total_tasks’. So, we can use ISNULL Function with the COUNT Function to count the number of NULLS present int the column. You can see the query used for this in the image above.

Pros and Cons of using MySQL ISNULL Function

Pros
 

• We can use the ISNULL Function with Conditional Statements to check whether the value is NULL or not.
   
• It can improve performance by reducing the amount of data that needs to be processed.
   
• It can be used to join tables on columns that contain NULL values.
   
• It reduces memory usage by avoiding unnecessary data processing.
   
• It is used to transform data having NULL value to the default value or some other value which is useful when we want to analyze a data that should be clean or initialized.
   
• It works with numerical and non-numerical data, making it more flexible to handle NULL values in different scenarios.

Cons
 

• It is slower than some alternative methods to check the NULL value.
   
• If we use it with large data sets, then it may affect the performance of the Database.
   
• If we use it with some column with a value that is not NULL, it may lead to unexpected results or errors.
   
• It is not suitable to handle data types like JSON, BLOB, ENUM and XML.
   
• When we use it with joins then it takes a longer execution time which affects the performance of the Database.

Also see,  Tcl Commands in SQL

Alternatives to ISNULL Function in MySQL

IFNULL Function in MySQL

The IFNULL Function takes two arguments: the first is to check whether the expression is NULL or not, and the second is to return that value if the expression is NULL.
Let’s take an example to understand this Function.
We have a table ‘Items’ with columns ‘ItemNumber’ and ‘ItemPrice’. Now we want to calculate the total price of all the items in the table. If any given value is NULL then take that value as zero otherwise take the value given in the table. We can do this by using the IFNULL function and the Aggregate Function ‘SUM’. The image below shows the table and the query we have used to calculate the total price.

mysql> SELECT * FROM Customers;
+------------+--------------+-----------------+------------+
| CustomerID | CustomerName  | ContactNumber    | City     |
+------------+--------------+-----------------+------------+
|         1  | John Doe      | 555-555-5555     | LA       |
|         2  | Jane Doe      | 555-555-5556     | New York |
|         3  | Brad Pitt     | 555-555-5557     | London   |
|         4  | Angelina Jolie | 555-555-5558    | Paris    |
+------------+--------------+-----------------+------------+
4 rows in set (0.00 sec)
mysql> SELECT COUNT(*) AS Total_Customer FROM Customers;
+----------------+
| Total_Customer |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)
mysql>

Code

SELECT * FROM Items;
SELECT IFNULL(SUM(ItemPrice), 0) AS Total_price FROM Items;

Output

COALESCE Function in MySQL

The COALESCE Function returns the first expression which is not a NULL value. The Function returns a NULL value if all the expressions given are NULL.
Now, let's see an example to use this Function as an alternative Function for ISNULL.
We have a ‘Auditions’ table with the columns ‘Name’, ‘Age’ and ‘Gender’. Some records have NULL values in the ‘Age’ column. Now our task is to replace all these NULL values with 20. We have used the ‘COALESCE’ Function to do this task. The image below shows the queries we have used to do this task.

mysql> SELECT * FROM Products;
+--------+------------+-------+------------+
| ProductID | ProductName | Price | Stock  |
+--------+------------+-------+------------+
|         1 | Laptop     | 700   |  10     |
|         2 | Mobile     | 500   |  20     |
|         3 | Tablet     | 300   |  30     |
|         4 | Watch      | 200   |  15     |
+--------+------------+-------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT SUM(Price * Stock) AS Total_Price FROM Products;
+-------------+
| Total_Price |
+-------------+
|        23000|
+-------------+
1 row in set (0.00 sec)
mysql>

Code

SELECT * FROM AUDITIONS;
SELECT Name, COALESCE(Age, 20) AS Age, Gender FROM Auditions;

Output

Also see, Update Query in MySQL

Frequently Asked Questions

How do you handle the NULL values in MySQL using the ISNULL Function?

We can handle the NULL values in MySQL using the ISNULL Function by checking every column for each record or by replacing all the NULL values with the default value, or by the value given to us.

Why do we prefer IFNULL Function over ISNULL Function?

We prefer IFNULL Function over ISNULL Function because it is supported by the many other Databases like Oracle, SQL Server and PostgreSQL and it is useful in scenarios when there is need to take a default value instead of NULL value to perform calculations.

Can the ISNULL Function be used with other MySQL Functions?

Yes, we can use ISNULL Function with many MySQL Functions including aggregate FunctionS like AVG and SUM, Concat Function (to concatenate two or more strings) and Count Function (to count number of rows in table).

Conclusion

This article covers the concept of ISNULL Function in MySQL along with its syntax and examples. We have also discussed the pros and cons of ISNULL function and the alternatives to ISNULL Function in MySQL.
We hope you enjoyed the article and gained insight into this topic.
You can refer to MySQL Variables to know more about this topic.
Head over to our practice platform Coding Ninjas Studio to practice top problems, attempt mock tests, read interview experiences and interview bundles, follow guided paths for placement preparations, and much more!!
Happy Learning Ninja!