- 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
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:
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