CODEWITHSUNDEEP
ms-access
toolshed
toolshed

Reputation: 2029

Is there an efficient alternative to "Not In" in MS Access Query?

It appears that doing a Not In query is expensive in MS Access, as these queries generally run very slowly. Is there an alternative method to conduct such a query as to avoid this overhead?

Upvotes: 2

Views: 1914

Answers (2)

user3913686
user3913686

Reputation:

you can use Joins!!!

Joins

Using either the Left Join or Right Join should do what you want! (Include the WHERE Clause IS NULL

(So that's either the middle left or middle right diagrams.)

So it will be something like:

SELECT * 
FROM Table a
LEFT JOIN Table b
ON a.Value = b.Value
WHERE b.AnotherOrSameValue IS NULL

Please Note

As HansUp has informed me, The Full Outer Join is not available for MS Access SQL (that's the lower right diagram)

Upvotes: 4

Grantly
Grantly

Reputation: 2556

Using in and not in translate to very long and or or statements by the pre parser.

So ABC IN(1,2,3,4) translates to (ABC = 1 OR ABC = 2 OR ABC = 3 OR ABC = 4)

Similarly for NOT IN, although probably using ANDs in the actual query processing.

This can result in poor performance of queries, if there is a large (-ish) number of values in the IN or NOT IN.

It might be best to redesign your query to use joins instead, as the database engines are highly optimized for fast Joins.

EDIT: Explained the pitfalls of using IN or NOT IN a bit better

Upvotes: 1

Related Questions