Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
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
  • + Share This
  • 🔖 Save To Your Account