MySQL: “= true” vs “is true” on BOOLEAN. When is it advisable to use which one? And Which one is vendor independent?

MySQL provides 2 ways to check truth value of boolean columns, those are column_variable = true and column_variable is true. I created a table, inserted few values & tried a few select statements. Here are the results:

First I created this table:

mysql> create table bool_test (
    -> id int unsigned not null auto_increment primary key,
    -> flag boolean );
Query OK, 0 rows affected (0.13 sec)

Then I inserted 4 rows:

mysql> insert into bool_test(flag) values (true),(false),(9),(null);

mysql> select * from bool_test;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  2 |    0 |
|  3 |    9 |
|  4 | NULL |

Here are all the select queries I fired on this table:

mysql> select * from bool_test where flag;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  3 |    9 |
+----+------+
2 rows in set (0.49 sec)

mysql> select * from bool_test where flag = true;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.02 sec)

mysql> select * from bool_test where flag is true;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  3 |    9 |
+----+------+
2 rows in set (0.04 sec)

mysql> select * from bool_test where flag = false;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
+----+------+
1 row in set (0.01 sec)

mysql> select * from bool_test where flag is false;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from bool_test where !flag;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from bool_test where not flag;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from bool_test where flag != true;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
|  3 |    9 |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from bool_test where flag is not true;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
|  4 | NULL |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from bool_test where flag != false;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  3 |    9 |
+----+------+
2 rows in set (0.04 sec)

mysql> select * from bool_test where flag is not false;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  3 |    9 |
|  4 | NULL |
+----+------+
3 rows in set (0.00 sec)

My Question is: when is it advisable to use is/is not and when is it advisable to use =/!= with true/false ? Which one is vendor independent?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

MySQL is actually fooling you. It doesn’t have a boolean column type at all:

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered
false. Nonzero values are considered true:

Also, the boolean literals are not such:

The constants TRUE and FALSE evaluate to 1 and 0, respectively.

Considering that:

• Many database systems do not have booleans either (not at least in standard SQL and column types)
• MySQL doesn’t have an easy way to enforce 0 or 1 in BOOLEAN

My conclusion would be:

• You’ll have to use WHERE IS flag or just WHERE flag because = simply doesn’t work correctly. Which one, is possibly a matter of preference.
• Whatever you choose, no option will be vendor independent. For instance, Oracle won’t even run either of them.

Edit: if cross-platform is a must, I’d go for this:

WHERE flag=0
WHERE flag<>0

I’m sure we’ve all done it lots of times.

Method 2

If the flag column is indexed and all values are either 0 or 1, where flag = true is much faster than where flag is true.

During our testing, is true resulted in a “full table scan” and took 1.121 seconds, while = true was executed with “key lookup” and only took 0.167 seconds. The table had about 3 million rows.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0