Using the IN and BETWEEN Operators on Tables

How to use the IN operator

Figure 3-13 shows how to code a WHERE clause that uses the IN operator. When you use this operator, the value of the test expression is compared with the list of expressions in the IN phrase. If the test expression is equal to one of the expressions in the list, the row is included in the query results. This is illustrated by the first example in this figure, which will return all rows whose terms_id column is equal to 1, 3, or 4.

You can also use the NOT operator with the IN phrase to test for a value that’s not in a list of expressions. This is illustrated by the second example in this figure. In this case, only those vendors who are not in California, Nevada, or Oregon are retrieved.

If you look at the syntax of the IN phrase shown at the top of this figure, you’ll see that you can code a subquery in place of a list of expressions. Subqueries are a powerful tool that you’ll learn about in chapter 6. For now, though, you should know that a subquery is simply a SELECT statement within another statement. In the third example in this figure, for instance, a subquery is used to return a list of vendor_id values for vendors who have invoices dated May 1, 2008. Then, the WHERE clause retrieves a vendor row only if the vendor is in that list. Note that for this to work, the subquery must return a single column, in this case, vendor_id.

The syntax of the WHERE clause with the IN operator

WHERE test_expression [NOT] IN ({subquery|expression_1 [, expression_2]...})

Examples of the IN operator

The IN operator with a list of numeric literals

WHERE terms_id IN (1, 3, 4)

The IN operator preceded by NOT

WHERE vendor_state NOT IN ('CA', 'NV', 'OR')

The IN operator with a subquery

WHERE vendor_id IN
(SELECT vendor_id
FROM invoices
WHERE invoice_date = '01-MAY-2008')

Description

1. You can use the IN operator to test whether an expression is equal to a value in a list of expressions. Each of the expressions in the list must evaluate to the same type of data as the test expression.
   
2. The list of expressions can be coded in any order without affecting the order of the rows in the result set.
   
3. You can use the NOT operator to test for an expression that’s not in the list of expressions.
   
4. You can also compare the test expression to the items in a list returned by a subquery as illustrated by the third example above. You’ll learn more about coding subqueries in chapter 6.

--------------------------------------------Figure 3-13 How to use the IN operator