MySQL IS NOT operator

IS NOT operator

MySQL IS NOT operator will test a value against a boolean value. A boolean value can be TRUE, FALSE, or UNKNOWN.

Syntax:

IS NOT boolean_value

MySQL Version: 8.0

Example: MySQL IS NOT operator

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

Code:

-- This query checks if the value 2 is not UNKNOWN.
SELECT 2 IS NOT UNKNOWN,
-- This part returns 1 (true) because 2 is a known value.
-- This query checks if the value 0 is not UNKNOWN.
0 IS NOT UNKNOWN,
-- This part returns 1 (true) because 0 is a known value.
-- This query checks if the value NULL is not UNKNOWN.
NULL IS NOT UNKNOWN;
-- This part returns 0 (false) because NULL is considered an unknown value.

Explanation:

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


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


• 2 IS NOT UNKNOWN: This checks if the value 2 is not UNKNOWN. Since 2 is a specific, known value, this expression evaluates to 1 (true).


• 0 IS NOT UNKNOWN: This checks if the value 0 is not UNKNOWN. Since 0 is also a specific, known value, this expression evaluates to 1 (true).


• NULL IS NOT UNKNOWN: This checks if the value NULL is not UNKNOWN. Since NULL represents an unknown or missing 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 2 is a known value and thus not UNKNOWN.


• The second column will be 1 because 0 is a known value and thus not UNKNOWN.


• The third column will be 0 because NULL is considered an unknown value.

Output:

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

Slideshow of MySQL Comparison Function and Operators

Previous: IS NOT NULL
Next: IS NULL