ISNULL

Grammar Description

You can use the ISNULL() function instead of = to test whether the value is NULL. (Comparing the value with NULL using = always produces NULL.)

If the expression is NULL, the function returns true. Otherwise, it returns false.

The ISNULL() function shares some special behavior with the IS NULL comparison operator. See the description of IS NULL.

Grammar Structure

> ISNULL(expr)

Example

Example 1:

mysql> SELECT ISNULL(1+1);
+--------------------+
| isnull(1 + 1) |
+--------------------+
| false |
+--------------------+
1 row in set (0.01 sec)

Example 2:

CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, birth_date DATE );

INSERT INTO students (name, birth_date) VALUES ('John Doe', '2000-05-15'), ('Alice Smith', NULL), ('Bob Johnson', '1999-10-20');

-- Use the ISNULL() function to find students who did not fill in their date of birth:
mysql> SELECT * FROM students WHERE ISNULL(birth_date);
+-------+--------------------------------+
| id | name | birth_date |
+-------+--------------------------------+
| 2 | Alice Smith | NULL |
+-------+--------------------------------+
1 row in set (0.00 sec)

-- The ISNULL() function can also use IS NULL to implement the same function, so the following query is also equivalent:
mysql> SELECT * FROM students WHERE birth_date IS NULL;
+-------+--------------------------------+
| id | name | birth_date |
+-------+--------------------------------+
| 2 | Alice Smith | NULL |
+-------+--------------------------------+
1 row in set (0.01 sec)