Can you use NOT IN and NOT EXISTS in the same query?

Question

Theoretically, can you use both NOT IN and NOT EXISTS as part of the same query, perhaps in a sub query?

Answer 1

Yes, you can.

WHERE EXISTS (SELECT * FROM cities_stores
                WHERE cities_stores.store_type = stores.store_type) and stores.store_type NOT IN(...);

Answer 2

Why not..

CREATE TABLE Test9 (ID int)

INSERT INTO Test9 values (1), (2), (3), (4)

SELECT * 
FROM test9 
WHERE id NOT IN (1) 
 AND EXISTS (SELECT 1 FROM test9 WHERE id = 1)

Answer 3

Of course you can, but be aware of the differences between both in regard to empty (NULL) attributes. There you can get mixed results.When the subquery returns even one null, NOT IN will not match any rows.

The reason for this can be found by looking at the details of what the NOT IN operation actually means.

Let’s say, for illustration purposes that there are 4 rows in the table called t, there’s a column called ID with values 1..4

WHERE SomeValue NOT IN (SELECT AVal FROM t)

is equivalent to

WHERE SomeValue != (SELECT AVal FROM t WHERE ID=1)
  AND SomeValue != (SELECT AVal FROM t WHERE ID=2)
  AND SomeValue != (SELECT AVal FROM t WHERE ID=3)
  AND SomeValue != (SELECT AVal FROM t WHERE ID=4)

Let’s further say that AVal is NULL where ID = 4. Hence that != comparison returns UNKNOWN. The logical truth table for AND states that UNKNOWN and TRUE is UNKNOWN, UNKNOWN and FALSE is FALSE. There is no value that can be AND’d with UNKNOWN to produce the result TRUE

Hence, if any row of that subquery returns NULL, the entire NOT IN operator will evaluate to either FALSE or NULL and no records will be returned

Have a look at this post for more info.