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.
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 |
+——————-+
And there also is the <=> operator.
[…] liked Shlomi Noach post on “IS TRUE and IS FALSE“. This kind of logic create […]
[…] makes for a true statement? Shlomi Noach […]