Why does a SQL query with != 0 not include NULL values?
<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>