Skip to content

# Syntax of the day: IS TRUE and IS FALSE

MySQL SQL Syntax January 26, 2012

What makes for a true statement?

We usually test statements using a WHERE clause:

>     SELECT * FROM world.City WHERE Population > 1000000

The “Population > 1000000” statement makes for a boolean expression. Using WHERE is just one way of evaluating it. One can also test with IF():

>     SET @val := 7;
>     SELECT IF(@val > 2, 'Yes', 'No')

#### TRUE and FALSE

The two are keywords. They also map for the numerals 1 and 0, as follows:

>     mysql> SELECT TRUE, FALSE;
>     +------+-------+
>     | TRUE | FALSE |
>     +------+-------+
>     |    1 |     0 |
>     +------+-------+

Like in the C programming language, a nonzero value evaluates to a true value. A zero evaluates to false. A NULL evaluates to… well, NULL. But aside from 3-valued logic, what’s important in our case is that it is not true.

However, simple value comparison is incorrect:

>     mysql> SELECT @val, @val > 3, @val > 3 = TRUE as result;
>     +------+----------+--------+
>     | @val | @val > 3 | result |
>     +------+----------+--------+
>     |    7 |        1 |      1 |
>     +------+----------+--------+

>     mysql> SELECT @val, @val = TRUE as result;
>     +------+--------+
>     | @val | result |
>     +------+--------+
>     |    7 |      0 |
>     +------+--------+

To test for the truth value of an expression, the correct syntax is by using IS TRUE:

>     SELECT @val, @val IS TRUE as result;
>     +------+--------+
>     | @val | result |
>     +------+--------+
>     |    7 |      1 |
>     +------+--------+

Likewise, one may use IS FALSE to test for falsehood. However, if you wish to note NULL as a false value this does not work:

>     SELECT @empty, @empty IS TRUE, @empty IS FALSE;
>     +--------+----------------+-----------------+
>     | @empty | @empty IS TRUE | @empty IS FALSE |
>     +--------+----------------+-----------------+
>     | NULL   |              0 |               0 |
>     +--------+----------------+-----------------+

If you’re unsure why, you should read more on three-valued logic in SQL. To solve the above, simply use IS NOT TRUE:

>     SELECT @empty, @empty IS NOT TRUE;
>     +--------+--------------------+
>     | @empty | @empty IS NOT TRUE |
>     +--------+--------------------+
>     | NULL   |                  1 |
>     +--------+--------------------+

In summary, use IS TRUE and IS NOT TRUE so as to normalize truth values into a 0, 1 value range, C style, including handling of NULLs.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
4
Leave a Reply

4 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
2 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Mark Grennan

if (1 = true) and ( 1 is TRUE) and (NULL is not TRUE), TRUE, FALSE);

Or,

NULL is never TRUE or FALSE but
NULL is always NOT TRUE or NOT FALSE;

mysql> select NULL is not FALSE;
+——————-+
| NULL is not FALSE |
+——————-+
| 1 |
+——————-+

Guest
Daniël van Eeden

And there also is the <=> operator.

To NULL or NOT to NULL that is the question. | MySQL Fanboy

[…] liked Shlomi Noach post on “IS TRUE and IS FALSE“. This kind of logic create […]

Log Buffer #256, A Carnival of the Vanities for DBAs | The Pythian Blog

[…] makes for a true statement? Shlomi Noach […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.