Results not showing in MySQL with NULL values

Question

So I have a query set up in MySQL as below but for some reason it will not include results where the column 'contact_CompanyID' is NULL. In theory, the query should not include results where the 'contact_CompanyID' is equal to either 2311 or 1, so why is the results not showing where 'contact_CompanyID' is NULL (because NULL is not equal to 2311 or 1). As you can see in the query, I have tried different methods but none of them work (the commented out lines).

SELECT contact_ID, contact_FirstName, contact_LastName, contact_CompanyID 

FROM Contact WHERE 

NOT contact_IsUnsubscribed 
AND NOT contact_HasLeftCompany 

#AND contact_CompanyID <> 2311 
#AND contact_CompanyID <> 1

#AND NOT contact_CompanyID = 2311 
#AND NOT contact_CompanyID = 1

#AND NOT FIND_IN_SET(contact_CompanyID,'2311,1')

Many thanks.

Answer 1

Almost any comparison on a NULL value evaluates to NULL -- which is treated as false.

One method you can use is the NULL-safe comparison. In MySQL, this would look like:

SELECT contact_ID, contact_FirstName, contact_LastName, contact_CompanyID 
FROM Contact
WHERE NOT contact_IsUnsubscribed  AND
      NOT contact_HasLeftCompany AND
      NOT contact_CompanyID <=> 2311 ;