How to Retrieve Data from a Single Table - How to Use the IN Operator
(Page 12 of 17 )
Figure 12 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 TermsID 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 detail 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 VendorID values for vendors who have invoices dated May 1, 2002. 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, VendorID.
Figure 12: The syntax of the WHERE clause with an IN phrase
WHERE test _expression [NOT] IN ({subquery|expression_1 [, expression_2]...})
Examples of the IN phrase
An IN phrase with a list of numeric literals
WHERE TermsID IN (1, 3, 4)
An IN phrase preceded by NOT
WHERE VendorState NOT IN ('CA', 'NV', 'OR')
An IN phrase with a subquery
WHERE VendorID IN
(SELECT VendorID
FROM Invoices
WHERE InvoiceDate = '2002-05-01')
Description
- You can use the IN phrase 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.
- The list of expressions can be coded in any order without affecting the order of the rows in the result set.
- You can use the NOT operator to test for an expression that's not in the list of expressions.
- 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.
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Use the BETWEEN Operator >>
More MS SQL Server Articles
More By Bryan Syverson
