This problem is addressed in ArcGIS 8.1 Service Pack 1. See the Related Information section to download this pack.
When executing a query using the Select by Attributes dialog box on a joined layer, all rows are selected when only a subset of rows should be selected.
This seems to happen only for certain queries that involve fields from more than one table in the join.
Before a query is executed, additional expressions are added behind the scenes. In certain very specific cases, these additional expressions alter the results of the query. In these cases, true is returned for all rows, resulting in all rows being selected.
Enclosing parentheses around the query ensures a correct result. For example, the following query will return the correct result:
(codemog.STATE_NAME = 'Colorado' or counties.STATE_NAME = 'California')