MySQL query delivers wrong result when using (not equal) in WHERE clause

I came accross a strange problem with a MySQL Query

SELECT COUNT(id) FROM members
100

SELECT COUNT(id) FROM members WHERE lastname = 'Smith'
20

SELECT COUNT(id) FROM members WHERE lastname <> 'Smith'
0

The problem is, that the last query (Members with lastname != ‘Smith’) returns 0.

If there are 100 members in total and 20 members named ‘Smith’, the number of member with other last names should be 80, shouldn’t it?

I tried different version using <>, !=, enclosing Smith with ' or ". The result when using LIKE and NOT LIKE instead is the same.

How is this possible? It seems that I am missing something quite obvious, but what…?

Answer

because others are null

try this :

SELECT COUNT(id) FROM members WHERE IFNULL(lastname ,'--')<> 'Smith'

Example :

CREATE TABLE my_table 
SELECT 'ersin' name  FROM dual
union all
SELECT 'ersin' name  FROM dual
union all
SELECT 'ersin' name  FROM dual
union all
SELECT null name  FROM dual
union all
SELECT null name  FROM dual
union all
SELECT null name  FROM dual;

select script:

select count(*) from my_table where IFNULL(name ,'--') <> 'ersin' ;

output:

count(*)
3