- Retrieving Columns with SELECT and FROM
- Creating Column Aliases with AS
- Eliminating Duplicate Rows with DISTINCT
- Sorting Rows with ORDER BY
- Filtering Rows with WHERE
- Combining and Negating Conditions with AND, OR, and NOT
- Matching Patterns with LIKE
- Range Filtering with BETWEEN
- List Filtering with IN
- Testing for Nulls with IS NULL
This chapter is from the book
This chapter is from the book
This chapter is from the book
List Filtering with IN
Use IN to determine whether a given value matches any value in a specified list. The IN condition’s important characteristics are:
- IN works with character strings, numbers, and datetimes.
- The IN list is a parenthesized listing of one or more comma-separated values. The list items needn’t be in any particular order.
IN is a convenient, shorthand clause that you can replicate by using OR.
WHERE test_column IN (value1, value2, value3)is equivalent to:
WHERE (test_column = value1) OR (test_column = value2) OR (test_column = value3)
- String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS Tip in “Filtering Rows with WHERE” earlier in this chapter.
- You can negate an IN condition with NOT IN.
- You can combine IN conditions and other conditions with AND and OR.
To filter rows by using a list:
Type:
SELECT columns FROM table WHERE test_column [NOT] IN (value1, value2,...);columns is one or more comma-separated column names, and table is the name of the table that contains columns.
In the search condition, test_column is the name of a column in table (test_column doesn’t have to be listed in columns), and value1, value2, ... are one or more comma-separated values that are compared with the value in test_column. The list values can appear in any order and must be the same as or comparable to the data type of test_column. Specify NOT IN to match values that aren’t in the list (Listings 4.39 through 4.41, Figures 4.39 through 4.41).
Listing 4.39. List the authors who don’t live in New York State, New Jersey, or California. See Figure 4.39 for the result.
SELECT au_fname, au_lname, state FROM authors WHERE state NOT IN ('NY', 'NJ', 'CA');Figure 4.39 Result of Listing 4.39.
au_fname au_lname state -------- ----------- ----- Wendy Heydemark CO Paddy O'Furniture FL
Listing 4.40. List the titles for which advances of $0, $1,000, or $5,000 were paid. See Figure 4.40 for the result.
SELECT title_id, advance FROM royalties WHERE advance IN (0.00, 1000.00, 5000.00);Figure 4.40 Result of Listing 4.40.
title_id advance -------- ------- T02 1000.00 T08 0.00 T09 0.00
Listing 4.41. List the titles published on the first day of the year 2000, 2001, or 2002. See Figure 4.41 for the result.
SELECT title_id, pubdate FROM titles WHERE pubdate IN (DATE '2000-01-01', DATE '2001-01-01', DATE '2002-01-01');Figure 4.41 Result of Listing 4.41.
title_id pubdate -------- ---------- T05 2001-01-01
Tips
- test_column can be an expression.
- The NOT that can precede IN is independent of the NOT that can precede test_column; see the Tips in “Matching Patterns with LIKE” earlier in this chapter.
- If your list contains a large number of values, your code will be easier to read if you use one IN condition instead of many OR conditions. (Also, one IN usually runs faster than multiple ORs.)
For speed, list the most likely values first. If you’re testing U.S. addresses, for example, list the most populous states first:
WHERE state IN ('CA', 'TX', 'NY', 'FL',...,'VT', 'DC', 'WY').The search condition
WHERE col1 BETWEEN 1 AND 5 AND col1 <> 3
usually is faster than
WHERE col1 IN (1, 2, 4, 5)
- A compound condition’s order of evaluation is easier to read and manage if you use IN instead of multiple ORs; see “Combining and Negating Conditions with AND, OR, and NOT” earlier in this chapter.
- You also can use IN to determine whether a given value matches any value in a subquery; see Chapter 8.
NOT IN is equivalent to combining tests for inequality with AND. This statement is equivalent to Listing 4.39:
SELECT au_fname, au_lname, state FROM authors WHERE state <> 'NY' AND state <> 'NJ' AND state <> 'CA';
In Microsoft Access date literals, omit the DATE keyword and surround the literal with # characters instead of quotes. To run Listing 4.41, change the WHERE clause to:WHERE pubdate IN (#1/1/2000#, #1/1/2001#, #1/1/2002#)In Microsoft SQL Server and DB2 date literals, omit the DATE keyword. To run Listing 4.41, change the WHERE clause to:
WHERE pubdate IN ('2000-01-01', '2001-01-01', '2002-01-01')In older PostgreSQL versions, convert the floating-point number in Listing 4.40 to DECIMAL; see “Converting Data Types with CAST()” in Chapter 5. To run Listing 4.40, change the WHERE clause to:
WHERE advance IN (CAST( 0.00 AS DECIMAL), CAST(1000.00 AS DECIMAL), CAST(5000.00 AS DECIMAL))