Using WHERE IN and WHERE NOT IN in the same query

advertisements

Is it possible to use WHERE IN and WHERE NOT IN in mysql query?

I have tried using them in one query but with wrong results. So i want to ask is theoretically possible to exclude some results with NOT IN and IN statement?

EDIT 1: i am using statements on the same column.


Yes, it's entirely possible, but unless you're using them with different columns, it doesn't make a lot of sense unless you don't control the sets involved. If you're using them with different columns, it can make perfect sense.

E.g., this doesn't make sense most of the time:

WHERE A IN (1, 2, 3) AND A NOT IN (4, 5, 6)

...because if A is in 1, 2, 3 it is, by definition, not in 4, 5, 6. The second part is redundant. Just

WHERE A IN (1, 2, 3)

is all you need. But you can do (for instance, when you don't know what the sets are, you're getting them from some other source):

WHERE A IN (1, 2, 3) AND A NOT IN (3, 4, 5)

that will boil down to:

WHERE A IN (1, 2)

But this can make sense:

WHERE A IN (1, 2, 3) AND B NOT IN (4, 5, 6)

(The strikeout is because of the edit to the question.)