Mysql: LIKE + NOT LIKE < ALL

Question

Can't understand such results:

SELECT COUNT(*) count FROM `persons_tmp` WHERE `p`.`medical_result1` NOT LIKE '%СО%6%'

Result: 36191

SELECT COUNT(*) count FROM `persons_tmp` WHERE `p`.`medical_result1` LIKE '%СО%6%'

Result: 140

SELECT COUNT(*) count FROM `persons_tmp`

Result: 42360

36191 + 140 < 42360. What's wrong?

Answer 1

It's not counting null values because null essentially means "unknown". If it doesn't know the value, it can't do a comparison so it simply ignores those rows.

Try this to see if it makes up the difference:

SELECT COUNT(*) count FROM `persons_tmp` WHERE `p`.`medical_result1` IS NULL