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)

Ruthven

The first example is negating the outer join -- you can move that criteria to the on and it should work fine without the null check. – Formenti

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;

Unfolded

Or just use MySQL's NULL-safe equality comparison operator, <=>—e.g. WHERE NOT b.field1 <=> 0. – Eckardt

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
;

Seizing

Note that this was implemented in MySQL 5.6, which I initially found hard to discover. – Rollie

Answer 3

When using an outer join, SQL engines represent the absence of data in combined sets using null values. equality checks against null values always return false.

Consider: case when null = null then 1 else 0 end this will always yield 0!

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

In your query the where clause is applied AFTER the engine generates the joined data set.
Assume A has ID's 1,2 and B only has 1.

Your result set from the left join would be without a where clause:

  A.ID B.ID
    1  1
    2  NULL

Your where clause is applied b.field !=0

and you get

  A.ID B.ID
    1  1

Null equality checks always evaluates to FALSE so line 2 is excluded and line 1 is kept.

If you move the filter to the join criteria, the limit is applied BEFORE the tables are joined.

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

  A.ID B.ID
    1  1
    2  NULL

and since there is no record in B for line 2, you get both lines.

How does MySQL not consider NULL to be != 0?

Null is a special case. Equality comparisons against null will always yield false.

is null, is not null are two methods you can use to check if a value is null.

you could have also used where coalesce(b.field1,1) != 0 to ensure the 2nd record is returned. Though you're better off simply moving any filters on the 2nd table in a left join to the join critiera.

Chocolate

Not strictly correct—comparisons involving NULL yield NULL, not FALSE. – Eckardt

select case when null = null then 1 when null <> null then 0 else -1 end Good point! – Chocolate

I wish I could accept both. Thanks for the in-depth answer. – Ruthven