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?

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.

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.