Using the IN Operator

The IN operator is used to specify a range of conditions, any of which can be matched. IN takes a comma-delimited list of valid values, all enclosed within parenthesis. The following input demonstrates this:

SELECT prod_name, prod_price
FROM Products
WHERE vend_id  IN ('DLL01','BRS01')
ORDER BY prod_name;

prod_name                prod_price
-------------------      ----------
12 inch teddy bear       8.99
18 inch teddy bear       11.99
8 inch teddy bear        5.99
Bird bean bag toy        3.49
Fish bean bag toy        3.49
Rabbit bean bag toy      3.49
Raggedy Ann              4.99

The SELECT statement retrieves all products made by vendor DLL01 and vendor BRS01. The IN operator is followed by a comma- delimited list of valid values, and the entire list must be enclosed within parenthesis.

If you are thinking that the IN operator accomplishes the same goal as OR, you are right. The following SQL statement accomplishes the exact same thing as the example above:

SELECT prod_name, prod_price
FROM Products
WHERE vend_id  = 'DLL01' OR vend_id = 'BRS01'
ORDER BY prod_name;

prod_name               prod_price
-------------------     ----------
12 inch teddy bear      8.99
18 inch teddy bear      11.99
8 inch teddy bear       5.99
Bird bean bag toy       3.49
Fish bean bag toy       3.49
Rabbit bean bag toy     3.49
Raggedy Ann             4.99

Why use the IN operator? The advantages are

• When you are working with long lists of valid options, the IN operator syntax is far cleaner and easier to read.

• The order of evaluation is easier to manage when IN is used in conjunction with other AND and OR operators.

• IN operators almost always execute more quickly than lists of OR operators.

• The biggest advantage of IN is that the IN operator can contain another SELECT statement, enabling you to build highly dynamic WHERE clauses. You'll look at this in detail in Lesson 11, "Working with Subqueries."