What is the difference between MySQL ISNULL() function and IS NULL operator?

Significantly both ISNULL() function and IS NULL operator do not have any difference and shares some common behaviors’. The only difference which we can see is in their syntax. ISNULL() function would have the expression as its argument whereas IS NULL comparison operator is having the expression to its left. Otherwise, both returns 1 if an expression is NULL and 0 if the expression is not NULL. Following example will demonstrate the above concept −

mysql> Select 1 IS NULL;
+-----------+
| 1 IS NULL |
+-----------+
| 0         |
+-----------+
1 row in set (0.00 sec)

mysql> Select ISNULL(1);
+-----------+
| ISNULL(1) |
+-----------+
| 0         |
+-----------+
1 row in set (0.00 sec)

mysql> Select ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
| 1           |
+-------------+
1 row in set (0.00 sec)

mysql> Select 1/0 IS NULL;
+-------------+
| 1/0 IS NULL |
+-------------+
| 1           |
+-------------+
1 row in set (0.00 sec)

mysql> Select * from Employee WHERE Salary IS NULL;
+----+-------+--------+
| ID | Name  | Salary |
+----+-------+--------+
| 7  | Aryan | NULL   |
| 8  | Vinay | NULL   |
+----+-------+--------+
2 rows in set (0.00 sec)

mysql> Select * from Employee WHERE ISNULL(Salary);
+----+-------+--------+
| ID | Name | Salary |
+----+-------+--------+
| 7  | Aryan | NULL   |
| 8  | Vinay | NULL   |
+----+-------+--------+
2 rows in set (0.00 sec)

Moumita

Published on 21-Feb-2018 10:01:54

• Related Questions & Answers
• In MySQL what is the difference between != NULL and IS NOT NULL?
• Difference Between MySql <> NULL and IS NOT NULL?
• What is the difference between MySQL NOW() and CURDATE() function?
• What is the difference between MySQL LENGTH() and CHAR_LENGTH() function?
• What is the difference between MySQL stored procedure and function?
• What is the significant difference between MySQL LIKE and equal to (=) operator?
• What is the difference between null and undefined in JavaScript?
• What is the significant difference between MySQL TRUNCATE() and ROUND() function?
• What is MySQL NULL-safe equal operator and how it is different from comparison operator?
• Difference between the Ternary operator and Null coalescing operator in php
• What is the difference Between AND, OR operator in MySQL while Retrieving the Rows?
• What is the difference between SQL and MySQL?
• Difference between == and is operator in python.
• What is the difference between operator and method on Python set?
• What is the difference between equals() method and == operator in java?