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

Question

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?

Answer 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.