MySQL - results from "SELECT WHERE STH" and "SELECT WHERE NOT STH" are not summarized to the full table

Question

How is it possible what comes out of these two queries:

SELECT * FROM `workers` WHERE `name` = 'Smith`

and

SELECT * FROM `workers` WHERE NOT `name` = 'Smith`

not summarized with the whole table workers?

Answer 1

Since the NULLin field namedoes not fall into any of the queries.

In the ternary logic used SQL, NULL = 'Smith'both NOT NULL = 'Smith'are evaluated before NULLand filtered out.

Use the NULL-server comparison operator <=>:

SELECT * FROM `workers` WHERE `name` <=> 'Smith`

and

SELECT * FROM `workers` WHERE NOT `name` <=> 'Smith`