{"id":4198,"date":"2012-01-26T06:09:23","date_gmt":"2012-01-26T04:09:23","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4198"},"modified":"2012-01-26T06:09:23","modified_gmt":"2012-01-26T04:09:23","slug":"syntax-of-the-day-is-true-and-is-false","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/syntax-of-the-day-is-true-and-is-false","title":{"rendered":"Syntax of the day: IS TRUE and IS FALSE"},"content":{"rendered":"

What makes for a true<\/em> statement?<\/p>\n

We usually test statements using a WHERE clause:<\/p>\n

\n
SELECT * FROM world.City WHERE Population > 1000000<\/pre>\n<\/blockquote>\n
The “Population > 1000000”<\/strong> statement makes for a boolean expression. Using WHERE<\/strong> is just one way of evaluating it. One can also test with IF()<\/strong><\/a>:<\/p>\n
\n
SET @val := 7;\r\nSELECT IF(@val > 2, 'Yes', 'No')<\/pre>\n<\/blockquote>\n
TRUE and FALSE<\/h4>\n
The two are keywords. They also map for the numerals 1<\/strong> and 0<\/strong>, as follows:<\/p>\n
\n
mysql> SELECT TRUE, FALSE;\r\n+------+-------+\r\n| TRUE | FALSE |\r\n+------+-------+\r\n|\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0 0 |\r\n+------+-------+<\/pre>\n<\/blockquote>\n
Like in the C<\/strong> programming language, a nonzero value evaluates to a true<\/em> value. A zero evaluates to false<\/em>. A NULL evaluates to… well, NULL. But aside from 3-valued logic, what’s important in our case is that it is not true<\/em>.<\/p>\n
However, simple value comparison is incorrect:<\/p>\n
\n
mysql> SELECT @val, @val > 3, @val > 3 = TRUE as result;\r\n+------+----------+--------+\r\n| @val | @val > 3 | result |\r\n+------+----------+--------+\r\n|\u00a0\u00a0\u00a0 7 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+------+----------+--------+\r\n\r\nmysql> SELECT @val, @val = TRUE as result;\r\n+------+--------+\r\n| @val | result |\r\n+------+--------+\r\n|\u00a0\u00a0\u00a0 7 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+------+--------+<\/pre>\n<\/blockquote>\n
To test for the truth value of an expression, the correct syntax is by using IS TRUE<\/strong>:<\/p>\n
\n
SELECT @val, @val IS TRUE as result;\r\n+------+--------+\r\n| @val | result |\r\n+------+--------+\r\n|\u00a0\u00a0\u00a0 7 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+------+--------+<\/pre>\n<\/blockquote>\n
Likewise, one may use IS FALSE<\/strong> to test for falsehood. However, if you wish to note NULL<\/strong> as a false<\/em> value this does not work:<\/p>\n
\n
SELECT @empty, @empty IS TRUE, @empty IS FALSE;\r\n+--------+----------------+-----------------+\r\n| @empty | @empty IS TRUE | @empty IS FALSE |\r\n+--------+----------------+-----------------+\r\n| NULL\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+--------+----------------+-----------------+<\/pre>\n<\/blockquote>\n
If you’re unsure why, you should read more on three-valued logic in SQL. To solve the above, simply use IS NOT TRUE<\/strong>:<\/p>\n
\n
SELECT @empty, @empty IS NOT TRUE;\r\n+--------+--------------------+\r\n| @empty | @empty IS NOT TRUE |\r\n+--------+--------------------+\r\n| NULL\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+--------+--------------------+<\/pre>\n<\/blockquote>\n
In summary, use IS TRUE<\/strong> and IS NOT TRUE<\/strong> so as to normalize truth values into a 0<\/strong>, 1<\/strong> value range, C<\/strong> style, including handling of NULL<\/strong>s.<\/p>\n","protected":false},"excerpt":{"rendered":"
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, […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[21,20],"class_list":["post-4198","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-sql","tag-syntax"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-15I","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4198","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4198"}],"version-history":[{"count":11,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4198\/revisions"}],"predecessor-version":[{"id":4638,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4198\/revisions\/4638"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}