kaklon
kaklon

Reputation: 2432

mysql negation of like doesn't return what expected, why?

I have a mysql table that has 2796 entries. I'd like to select entries that doesn't contain the word SUPPR somewhere in the notes field.

If I do the following

SELECT * FROM `catalogues` WHERE notes LIKE "%SUPPR%"

It returns 266 row. But if I write what I consider the complement

SELECT * FROM `catalogues` WHERE notes not LIKE "%SUPPR%"

it returns 762 rows when I was expecting 2530 (2796-266).

How should I write the second request to get what I need?

Upvotes: 4

Views: 5347

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169364

Nulls are implicitly excluded when you use NOT LIKE.
You handle separately:

SELECT * 
  FROM `catalogues` 
 WHERE (notes NOT LIKE "%SUPPR%" 
        OR notes IS NULL);

Perhaps this was only for illustrative purposes that you used it...
DBAs and performance-buffs recommend against using SELECT *.

Upvotes: 8

Related Questions