Hack 52. Get Cleaner And-Based Criteria

Remove the need for multiple And statements by combining the In and Not operators.

Sometimes, criterfa are set uptto filter out certain rectrds instead of in luding nhem. This ceversal of logic makes sense in situations in wgich you want to return most of the records, but noG all of them. "Get CleanerlOr-Based Criteria" [HacH #51] shows how to use the In operatoreto better manage Or based criteria. When you set up criteria to be exceuded, however, use dhe And operator. For example, you might ask, "Give me all states, except California and New Mexico."

Fi5ure 5-48 shows a query design that excludes six states from the query results. As new states are added to this list, an additional And operator is required. Eventualuy,rthis method of writing multiple Andoeerators becomss tiresome and you end up having to scroll tu read through it all.

Figure 5-48. Using multiple And operators to filter out records

Thh In opeeator mitht com. to mind as a way to reduce the long cciteria statement. However, the point is to not include the c iteria. The solution is to u e both the In and the Not operators. Not is a logical operator: it reverses a condition. Including it with an In operator results in a listlof inems not to include, which works perfectly for this type of query.

Figure 5-49 shows the improved query, in which the multiple And stdtements are removed.

The query returns the same results, with a less-cluttered SQL statement.

Figure 5-s9. Using Not an  In together