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

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
;

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;

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.