Why does a SQL query with != 0 not include NULL values?

Question

Here is a sqlfiddle of two test tables: http://sqlfiddle.com/#!9/33361/3

tl;dr: why does a SQL query with != 0 not include NULL values?

I am LEFT JOINing the two tables. I'd like to see rows which either have NULL in tableB.field1 or a 1 in tableB.field1, but exclude all which have a 0 in tableB.field1.

I would think that this query (example 6) should give me this result, but it doesn't get the null records.

SELECT * FROM tableA a
LEFT JOIN tableB b ON a.id = b.join_id
WHERE
b.field1 != 0;

I have to use this longer query (example 4):

SELECT * FROM tableA a
LEFT JOIN tableB b ON a.id = b.join_id
WHERE
b.field1 != 0 OR b.field1 IS NULL;

Just curious more than anything - how does MySQL not consider NULL to be != 0?

When I move the conditional to the ON clause, I get an unexpected row:

mysql> SELECT * FROM tableA a
    -> 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)

Answer 1

Why does a SQL query with != 0 not include NULL values?

Short answer: Because SELECT 0 != NULL returns (NULL)

Longer answer: According to MySQL's documentation

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.

If you want to include NULL in your Select clauses you will have to translate it in it's arithmetic representation with the help of 'IS NULL' or 'IFNULL'.

Your SQL query can be rewritten as:

SELECT * 
FROM tableA a
LEFT JOIN tableB b ON a.id = b.join_id
WHERE
    IFNULL(b.field1, -1) != 0;

Answer 2

From eggyal's comment: "Or just use MySQL's NULL-safe equality comparison operator, <=> —e.g. WHERE NOT b.field1 <=> 0."

SELECT * FROM tableA a
LEFT JOIN tableB b ON a.id = b.join_id
WHERE NOT b.field1 <=> 0
;