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.

score 0 · Answer 1 · edited Jan 13 '17 at 12:56

0

contact_CompanyID may be carrying white space .

Please check following query

 SELECT contact_ID, contact_FirstName, contact_LastName, contact_CompanyID 
 FROM Contact 
 WHERE contact_LastName = 'Dodd' 
 and length(trim(contact_CompanyID)) = 0

edited Jan 13 '17 at 12:56

ScaisEdge

131,976
10
91
107

answered Jan 13 '17 at 12:55

Gyro

733
7
11

score 0 · Accepted Answer · answered Jan 13 '17 at 12:58

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 ;

Results not showing in MySQL with NULL values

2 Answers2