title: MySQL: "= true" vs "is true" on BOOLEAN. When is it advisable to use which one? And Which one is vendor independent?
body: <p>MySQL provides 2 ways to check truth value of <code>boolean</code> columns, those are <code>column_variable = true</code> and <code>column_variable is true</code>. I created a table, inserted few values &amp; tried a few <code>select</code> statements. Here are the results:</p>
  
  <p>First I created this table:</p>
  
  <pre><code>mysql&gt; create table bool_test (
      -&gt; id int unsigned not null auto_increment primary key,
      -&gt; flag boolean );
  Query OK, 0 rows affected (0.13 sec)
  </code></pre>
  
  <p>Then I inserted 4 rows:</p>
  
  <pre><code>mysql&gt; insert into bool_test(flag) values (true),(false),(9),(null);
  
  mysql&gt; select * from bool_test;
  +----+------+
  | id | flag |
  +----+------+
  |  1 |    1 |
  |  2 |    0 |
  |  3 |    9 |
  |  4 | NULL |
  </code></pre>
  
  <p>Here are all the <code>select</code> queries I fired on this table:</p>
  
  <pre><code>mysql&gt; select * from bool_test where flag;
  +----+------+
  | id | flag |
  +----+------+
  |  1 |    1 |
  |  3 |    9 |
  +----+------+
  2 rows in set (0.49 sec)
  
  mysql&gt; select * from bool_test where flag = true;
  +----+------+
  | id | flag |
  +----+------+
  |  1 |    1 |
  +----+------+
  1 row in set (0.02 sec)
  
  mysql&gt; select * from bool_test where flag is true;
  +----+------+
  | id | flag |
  +----+------+
  |  1 |    1 |
  |  3 |    9 |
  +----+------+
  2 rows in set (0.04 sec)
  
  mysql&gt; select * from bool_test where flag = false;
  +----+------+
  | id | flag |
  +----+------+
  |  2 |    0 |
  +----+------+
  1 row in set (0.01 sec)
  
  mysql&gt; select * from bool_test where flag is false;
  +----+------+
  | id | flag |
  +----+------+
  |  2 |    0 |
  +----+------+
  1 row in set (0.00 sec)
  
  mysql&gt; select * from bool_test where !flag;
  +----+------+
  | id | flag |
  +----+------+
  |  2 |    0 |
  +----+------+
  1 row in set (0.00 sec)
  
  mysql&gt; select * from bool_test where not flag;
  +----+------+
  | id | flag |
  +----+------+
  |  2 |    0 |
  +----+------+
  1 row in set (0.00 sec)
  
  mysql&gt; select * from bool_test where flag != true;
  +----+------+
  | id | flag |
  +----+------+
  |  2 |    0 |
  |  3 |    9 |
  +----+------+
  2 rows in set (0.00 sec)
  
  mysql&gt; select * from bool_test where flag is not true;
  +----+------+
  | id | flag |
  +----+------+
  |  2 |    0 |
  |  4 | NULL |
  +----+------+
  2 rows in set (0.00 sec)
  
  mysql&gt; select * from bool_test where flag != false;
  +----+------+
  | id | flag |
  +----+------+
  |  1 |    1 |
  |  3 |    9 |
  +----+------+
  2 rows in set (0.04 sec)
  
  mysql&gt; select * from bool_test where flag is not false;
  +----+------+
  | id | flag |
  +----+------+
  |  1 |    1 |
  |  3 |    9 |
  |  4 | NULL |
  +----+------+
  3 rows in set (0.00 sec)
  </code></pre>
  
  <p>My Question is: when is it advisable to use <code>is</code>/<code>is not</code> and when is it advisable to use <code>=</code>/<code>!=</code>  with <code>true</code>/<code>false</code> ? Which one is vendor independent?</p>