Opposite of IN in mysql query?


I often use something like ‘SELECT * FROM table WHERE id IN (1,2,3)’, but how do I do the opposite? Let’s say I want to select everyone except those with id 1,2 or 3. I’ve tried with things like NOT IN or NOTIN, but didn’t work, and searching the documentation on mysql.com gave nothing, so I hope someone can answer me.

the opposite of IN is NOT IN

… WHERE id NOT IN (1,2,3)

this is documented here: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

an equivalent condition is –

… WHERE NOT ( id IN (1,2,3) )

however, do not make the mistake and omit those outer parentheses –

… WHERE NOT id IN (1,2,3)

this is not the same thing!!


SELECT * FROM table WHERE id NOT IN (1,2,3) should work ok and does work ok for me :o

I must have made a mistake then because I did try NOT IN, I’m going to give it another look.

Thank you both for the answers and thanks r937 for the documentation link, I was unable to find that :slight_smile: