title: Why does a SQL query with != 0 not include NULL values?
body: <p>Here is a sqlfiddle of two test tables: <a href="http://sqlfiddle.com/#!9/33361/3" rel="noreferrer">http://sqlfiddle.com/#!9/33361/3</a></p>
  
  <p>tl;dr: <strong>why does a SQL query with <code>!= 0</code> not include <code>NULL</code> values?</strong></p>
  
  <p>I am LEFT JOINing the two tables. I'd like to see rows which either have <code>NULL</code> in <code>tableB.field1</code> or a <code>1</code> in <code>tableB.field1</code>, but exclude all which have a <code>0</code> in <code>tableB.field1</code>.</p>
  
  <p>I would think that this query (example 6) should give me this result, but it doesn't get the null records.</p>
  
  <pre><code>SELECT * FROM tableA a
  LEFT JOIN tableB b ON a.id = b.join_id
  WHERE
  b.field1 != 0;
  </code></pre>
  
  <p>I have to use this longer query (example 4):</p>
  
  <pre><code>SELECT * FROM tableA a
  LEFT JOIN tableB b ON a.id = b.join_id
  WHERE
  b.field1 != 0 OR b.field1 IS NULL;
  </code></pre>
  
  <p>Just curious more than anything - how does MySQL not consider NULL to be != 0?</p>
  
  <p>When I move the conditional to the ON clause, I get an unexpected row:</p>
  
  <pre><code>mysql&gt; SELECT * FROM tableA a
      -&gt; LEFT JOIN tableB b ON a.id = b.join_id AND b.field1 != 0;
  +------+--------+--------+---------+--------+--------+
  | id   | field1 | field2 | join_id | field1 | field2 |
  +------+--------+--------+---------+--------+--------+
  |    1 | testA1 | testA1 |       1 | 1      | testB1 |
  |    2 | testA2 | testA2 |    NULL | NULL   | NULL   |
  |    3 | testA3 | testA3 |    NULL | NULL   | NULL   |
  +------+--------+--------+---------+--------+--------+
  3 rows in set (0.00 sec)
  </code></pre>