Joe Celko's SQL for Smarties, 3rd Edition

14.2. Replacing ORs with the IN() Predicate

A simple trick that beginning SQL programmers often miss is that an IN() predicate can often replace a set of ORed predicates. For example:

SELECT *
  FROM QualityControlReport
 WHERE test_1 = 'passed'
     OR test_2 = 'passed'
     OR test_3 = 'passed'
     OR test_4 = 'passed';

can be rewritten as:

SELECT *
  FROM QualityControlReport
 WHERE 'passed' IN (test_1, test_2, test_3, test_4);

The reason this is difficult to see is that programmers get used to thinking of either a subquery or a simple list of constants. They miss the fact that the IN() predicate list can be a list of expressions. The optimizer would have handled each of the original predicates separately in the WHERE clause, but it has to handle the IN() predicate ...