ISNULL

Description

The ISNULL() function can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields NULL.)

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

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

Syntax

  1. > ISNULL(expr)

Examples

  • Example 1:
  1. mysql> SELECT ISNULL(1+1);
  2. +---------------+
  3. | isnull(1 + 1) |
  4. +---------------+
  5. | false         |
  6. +---------------+
  7. 1 row in set (0.02 sec)
  • Example 2:
  1. CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, birth_date DATE );
  3. INSERT INTO students (name, birth_date) VALUES ('John Doe', '2000-05-15'), ('Alice Smith', NULL), ('Bob Johnson', '1999-10-20');
  5. -- Use the ISNULL() function to find students whose birth date is not filled in:
  6. mysql> SELECT * FROM students WHERE ISNULL(birth_date);
  7. +------+-------------+------------+
  8. | id   | name        | birth_date |
  9. +------+-------------+------------+
  10. |    2 | Alice Smith | NULL       |
  11. +------+-------------+------------+
  12. 1 row in set (0.00 sec)
  14. -- The ISNULL() function can also use IS NULL to achieve the same function, so the following queries are also equivalent:
  15. mysql> SELECT * FROM students WHERE birth_date IS NULL;
  16. +------+-------------+------------+
  17. | id   | name        | birth_date |
  18. +------+-------------+------------+
  19. |    2 | Alice Smith | NULL       |
  20. +------+-------------+------------+
  21. 1 row in set (0.01 sec)