The MySQL ISNULL() function provides you with a way to evaluate if an expression or literal value is actually NULL or not.
The function returns 1 when the value is NULL or 0 when the value is not NULL.
The ISNULL() function requires one parameter: an expression or value to evaluate.
ISNULL(expression);
Let’s learn how the function works. You can use the SELECT statement with the function to see the returned value as shown below:
The following SQL query:
SELECT ISNULL(NULL), ISNULL(2);
Will produce the following result set:
+--------------+-----------+
| ISNULL(NULL) | ISNULL(2) |
+--------------+-----------+
| 1 | 0 |
+--------------+-----------+
1 row in set (0.00 sec)
The function returns only two possible values: the integer 1 or 0.
You can use the function to check for NULL values in your MySQL table column.
For example, suppose you have a students table that records the score of tests taken by students as shown below:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 1 | Mark | NULL | 7 | male |
| 2 | Natalia | Math | 8 | female |
| 3 | Gary | Math | 6 | male |
| 4 | Joe | English | 8 | male |
| 5 | Sarah | Math | 6 | female |
| 6 | Peter | English | 6 | male |
| 7 | Nathan | NULL | 8 | male |
+----+---------+---------+-------+--------+
Notice how the first and last row of the table above has NULL value for the subject field.
The ISNULL() function allows you to check if the field value is actually NULL.
The following SQL statement:
SELECT id, ISNULL(subject) from students;
Will produce the following output:
+----+-----------------+
| id | ISNULL(subject) |
+----+-----------------+
| 1 | 1 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
+----+-----------------+
7 rows in set (0.00 sec)
And that’s how the ISNULL() function works.
MySQL ISNULL vs MSSQL IS NULL
The ISNULL() function is available on both MySQL and MSSQL (Microsoft SQL Server).
But the ISNULL() function in MSSQL works differently than the one in MySQL platform.
The MSSQL version of ISNULL() function allows you to check and replace an expression or literal value that returns NULL value with an alternative value.
SELECT ISNULL(NULL, 'Hello');
The statement above will throw an error on MySQL, but MSSQL will process the function and returns the string value 'Hello'.
The MSSQL version works like the MySQL version of IFNULL() function.