MySQL IS operator

IS operator

MySQL IS operator tests a value against a Boolean value. A boolean value can be TRUE, FALSE, or UNKNOWN.

Syntax:

IS boolean_value

MySQL Version: 8.0

Example: MySQL IS operator

In the following MySQL statement, it is checked whether 5 is TRUE, 0 is TRUE and NULL is UNKNOWN using IS operator. For the first and third case it returns 1, for the second case, it returns 0.

Code:

-- This query checks if the value 5 is TRUE.
SELECT 5 IS TRUE,
-- This part returns 1 (true) because any non-zero value is considered TRUE in MySQL.
-- This query checks if the value 0 is TRUE.
0 IS TRUE,
-- This part returns 0 (false) because 0 is considered FALSE in MySQL.
-- This query checks if the value NULL is UNKNOWN.
NULL IS UNKNOWN;
-- This part returns 1 (true) because NULL is considered UNKNOWN in MySQL.

Explanation:

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


• SELECT 5 IS TRUE, 0 IS TRUE, NULL IS UNKNOWN: This part of the query performs three boolean checks using the IS TRUE and IS UNKNOWN operators.


• 5 IS TRUE: This checks if the value 5 is TRUE. In MySQL, any non-zero value is considered TRUE. Therefore, this expression evaluates to 1 (true).


• 0 IS TRUE: This checks if the value 0 is TRUE. In MySQL, 0 is considered FALSE. Therefore, this expression evaluates to 0 (false).


• NULL IS UNKNOWN: This checks if the value NULL is UNKNOWN. In MySQL, NULL represents a missing or undefined value and is considered UNKNOWN. Therefore, this expression evaluates to 1 (true).

Output:

mysql> SELECT 5 IS TRUE, 0 IS TRUE, NULL IS UNKNOWN;
+-----------+-----------+-----------------+
| 5 IS TRUE | 0 IS TRUE | NULL IS UNKNOWN |
+-----------+-----------+-----------------+
|         1 |         0 |               1 | 
+-----------+-----------+-----------------+
1 row in set (0.02 sec)

Slideshow of MySQL Comparison Function and Operators

Previous: IS NULL
Next: ISNULL()