This chapter is from the book
This chapter is from the book
This chapter is from the book
The IN Operator
Just as BETWEEN represents a special case of the AND operator, the IN operator allows for a special case of the OR operator. Suppose we want to see rows where the state is Illinois or New York. Without the IN operator, this can be accomplished with this statement:
SELECT CustomerName, State FROM Purchases WHERE State = 'IL' OR State = 'NY'
Here is an equivalent statement using the IN operator:
SELECT
CustomerName,
State
FROM Purchases
WHERE State IN ('IL', 'NY')
In either case, the data retrieved is:
CustomerName |
State |
Kim Chiang |
IL |
James Turban |
NY |
The IN operator requires a subsequent listing of values enclosed within parentheses. Commas must be used to separate all values.
The usefulness of the IN operator may not be obvious in this example, where only two states are listed. However, the IN can just as easily be used in situations involving dozens of specific values. This greatly reduces the amount of typing required for such a statement. Another handy use for the IN operator comes in situations where values are obtained from an Excel spreadsheet. To obtain multiple values from adjacent cells in a spreadsheet for a SQL statement, knowledgeable Excel users can copy those values into a CSV (comma separated values) file with a comma delimiter. The values can then be pasted inside parentheses following the IN operator.
As with the BETWEEN operator, the NOT operator can be used with the IN operator, as shown in this example:
SELECT
CustomerName,
State
FROM Purchases
WHERE State NOT IN ('IL', 'NY')
The data retrieved is:
CustomerName |
State |
Sandy Harris |
CA |
One final note about the IN operator. There is a second way to use the IN operator that is substantially different from the syntax just discussed. In the second format, an entire SELECT statement is specified within parentheses, allowing the individual values to be created logically when needed. This is called a subquery, which will be covered in detail in Chapter 14.