The IN Operator

Parentheses have another very different use in WHERE clauses. 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 parentheses. The following example demonstrates this:

SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

The above statement displayed the following output:

+------------+------------+
| prod_name  | prod_price |
+------------+------------+
| product 1  |       5.99 |
| product 4  |      13.99 |
| product 5  |       9.09 |
| product 9  |      20.00 |
| product 10 |       4.75 |
| product 11 |      10.25 |
+------------+------------+

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

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 previous example:

SELECT prod_name, prod_price
FROM products
WHERE vend_id  = 1002 OR vend_id = 1003
ORDER BY prod_name;

The above statement displayed the following result:

+------------+------------+
| prod_name  | prod_price |
+------------+------------+
| product 1  |       5.99 |
| product 4  |      13.99 |
| product 5  |       9.09 |
| product 9  |      20.00 |
| product 10 |       4.75 |
| product 11 |      10.25 |
+------------+------------+

Advantages of IN Operator

• 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 (as there are fewer operators used).

• 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.