The [NOT] IN() Predicate

The IN() predicate is one of the “abbreviations” that is allowed in SQL. It can be expanded into the usual AND, OR, and NOT logical operators, but it is easier to see the intent of the programmer with this syntax. New SQL programmers stick to the more familiar “nonabbreviated” logic for two reasons: (1) it looks like their procedural language and (2) they think the optimizer will do better with nonabbreviated logic. This is not true at all.

The IN() syntax is very natural and was borrowed and generalized from the Pascal language. It takes a value on the left side and sees if it is in a list of comparable values on the right side. Standard SQL allows value expressions in the list or for you to use a query to construct ...

Get Joe Celko's SQL for Smarties, 4th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.