14.1. Optimizing the IN() Predicate
Most database engines have no statistics about the relative frequency of the values in a list of constants, so they will scan them in the order in which they appear in the list. People like to order lists alphabetically or by magnitude, but it would be better to order the list from most frequently occurring values to least frequent. It is also pointless to have duplicate values in the constant list, since the predicate will return TRUE if it matches the first duplicate it finds, and never get to the second occurrence. Likewise, if the predicate is FALSE for that value, it wastes computer time to traverse a needlessly long list.
Many SQL engines perform an IN() predicate with a subquery by building the result ...
Get Joe Celko's SQL for Smarties, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
