CODEWITHSUNDEEP
mysqlsql
happy_fist
happy_fist

Reputation: 171

Using WHERE IN and WHERE NOT IN in the same query

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.

Upvotes: 0

Views: 7046

Answers (2)

Jim
Jim

Reputation: 22656

Yes. Example:

SELECT * 
FROM someTable
WHERE id IN (1,2,3,4) AND name NOT IN ('foo','bar','baz');

Upvotes: 1

T.J. Crowder
T.J. Crowder

Reputation: 1075755

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.)

Upvotes: 6

Related Questions