MySQL IS NOT NULL operator

IS NOT NULL operator

MySQL IS NOT NULL operator will check whether a value is not NULL.

Syntax:

IS NOT NULL

MySQL Version: 8.0

Example: MySQL IS NOT NULL operator

The following MySQL statement it is checked whether 5, 0 and NULL is not NULL.

Code:

-- This query checks if the value 5 is not NULL.
SELECT 5 IS NOT NULL,
-- This part returns 1 (true) because 5 is not NULL.

-- This query checks if the value 0 is not NULL.
0 IS NOT NULL,
-- This part returns 1 (true) because 0 is not NULL.

-- This query checks if the value NULL is not NULL.
NULL IS NOT NULL;
-- This part returns 0 (false) because NULL is indeed NULL.

Explanation:

• The purpose of this SQL query is to evaluate whether certain values are not NULL and return the corresponding boolean result (1 for true, 0 for false).


• SELECT 5 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL: This part of the query performs three boolean checks using the IS NOT NULL operator.


• 5 IS NOT NULL: This checks if the value 5 is not NULL. Since 5 is a non-null value, this expression evaluates to 1 (true).


• 0 IS NOT NULL: This checks if the value 0 is not NULL. Since 0 is a non-null value, this expression also evaluates to 1 (true).


• NULL IS NOT NULL: This checks if the value NULL is not NULL. Since NULL is the representation of a missing or undefined value, this expression evaluates to 0 (false).


• The query will return a row with the results of these evaluations:


• The first column will be 1 because 5 is not NULL.


• The second column will be 1 because 0 is not NULL.


• The third column will be 0 because NULL is indeed NULL.

Output:

mysql> SELECT 5 IS NOT NULL,0 IS NOT NULL, NULL IS NOT NULL;
+---------------+---------------+------------------+
| 5 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL |
+---------------+---------------+------------------+
|             1 |             1 |                0 | 
+---------------+---------------+------------------+
1 row in set (0.00 sec)

Slideshow of MySQL Comparison Function and Operators

Previous: INTERVAL()
Next: IS NOT