How To Receive Data from a Single Table, continued - How to use the IN operator
(Page 6 of 6 )
Figure 3-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 3-12. How to use the IN operator
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter three of the book Murach's SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 6 
