Syntax of the day: IS TRUE and IS FALSE

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.

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

4 Comments to "Syntax of the day: IS TRUE and IS FALSE"

  1. Mark Grennan wrote:

    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 |
    +-------------------+

  2. Daniël van Eeden wrote:

    And there also is the <=> operator.

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

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

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

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

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org