MySQL NOT IN Query problem

Question

I am trying to search a table where the field name does not have a list of strings in it. I use:

SELECT * FROM members WHERE name NOT IN ('bob', 'jim', 'leroy');

but it still returns matches containing those words. I have searched high and low for the answer to this. Can anyone help?

could you please post a data set containing one of the names you want to filter out? — Simon
– Simon, Commented Oct 17, 2010 at 22:48

Brad Mace · Accepted Answer · 2010-10-17 22:47:47Z

3

name NOT IN ('bob', 'jim', 'leroy') is equivalent to name!='bob' and name!='jim' and name!='leroy'.

Perhaps you want

name not like '%bob%' and name not like '%jim%' and name not like '%leroy%'

instead?

answered Oct 17, 2010 at 22:47

28k19 gold badges110 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

yeah but I thought there would be a more efficient way, perhaps not

@Adam within mysql I think that's as good as it gets. If you need to improve performance look into an external search engine like sphinxsearch.com .

@OMG Ponies - I suppose I should have mentioned that, but I didn't because it's way slower. Might be good enough for his needs though.

Slower than a table scan? I don't think so, but constraining because of only supporting MyISAM.

|

slosd · Accepted Answer · 2010-10-17 22:48:55Z

This only NOT matches values that are exactly one of the names. You can try WHERE name NOT LIKE "%bob%" AND NOT LIKE "%jim%" AND NOT LIKE "%leroy%"