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

user_399696 0
Viewed 11355

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)
sqlfiddle null mysql comparison
3 Answers

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
;
user_70328560
Related