Fun with substring (as replacement for IN clause)
Different approach I was shown by a colleague to replace an IN clause in SQL in some cases.
Suppose you have a limited set of small values (I wouldn’t go beyond 255 for this) and want to do have an IN clause. You can substitute it for a well crafted substring.
Create a string (in your language of choice) as long as required (as long as the max value in your field). Make it all zeroes (or character of choice). At indexes of the values desired, change for 1’s. Then, what you’re going to want to do is use a substring function (not two are identical across the many implementations of SQL, it seems), and make the search target your prepared string, the start index will be your database value, and length 1. All this equal to ‘1’ for IN or different for NOT IN.
For example, suppose:
WHERE SmallValue IN (3,4,8)
would then become (in Access):
WHERE Mid('00110001',SmallValue,1) = '1'
In some cases (long table, values not unique) this will work faster than IN/NOT IN. Your mileage may vary.
Please mind index start (1 for Access, other databases might start at 0).