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

Home > Articles

  • Print
  • + Share This
This chapter is from the book

Range Filtering with BETWEEN

Use BETWEEN to determine whether a given value falls within a specified range. The BETWEEN condition’s important characteristics are:

  • BETWEEN works with character strings, numbers, and datetimes.
  • The BETWEEN range contains a low value and a high value, separated by AND. The low value must be less than or equal to the high value.
  • BETWEEN is a convenient, shorthand clause that you can replicate by using AND.

    WHERE test_column BETWEEN
          low_value AND high_value

    is equivalent to:

    WHERE (test_column >= low_value)
      AND (test_column <= high_value)
  • BETWEEN specifies an inclusive range, in which the high value and low value are included in the search. To specify an exclusive range, which excludes endpoints, use > and < comparisons instead of BETWEEN:
    WHERE (test_column > low_value)
      AND (test_column < high_value)
  • 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 a BETWEEN condition with NOT BETWEEN.
  • You can combine BETWEEN conditions and other conditions with AND and OR.

To filter rows by using a range:

  • Type:

    SELECT columns
      FROM table
      WHERE test_column [NOT] BETWEEN
            low_value AND high_value;

    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 low_value and high_value specify the endpoints of the range that is compared with the value in test_column. low_value must be less than or equal to high_value, and both values must be the same as or comparable to the data type of test_column. Specify NOT BETWEEN to match values that lie outside the range (Listings 4.35 through 4.37, Figures 4.35 through 4.37).

    Listing 4.35. List the authors who live outside the zip range 20000–89999. See Figure 4.35 for the result.

    SELECT au_fname, au_lname, zip
      FROM authors
      WHERE zip NOT BETWEEN '20000' AND '89999';

    Figure 4.35 Result of Listing 4.35.

    au_fname  au_lname zip
    --------- -------- -----
    Sarah     Buchman  10468
    Hallie    Hull     94123
    Klee      Hull     94123
    Christian Kells    10014
              Kellsey  94305

    Listing 4.36. List the titles priced between $10 and $19.95, inclusive. See Figure 4.36 for the result.

    SELECT title_id, price
      FROM titles
      WHERE price BETWEEN 10 AND 19.95;

    Figure 4.36 Result of Listing 4.36.

    title_id price
    -------- -----
    T02      19.95
    T04      12.99
    T06      19.95
    T08      10.00
    T09      13.95
    T12      12.99

    Listing 4.37. List the titles published in 2000. See Figure 4.37 for the result.

    SELECT title_id, pubdate
      FROM titles
      WHERE pubdate BETWEEN DATE '2000-01-01'
            AND     DATE '2000-12-31';

    Figure 4.37 Result of Listing 4.37.

    title_id pubdate
    -------- ----------
    T01      2000-08-01
    T03      2000-09-01
    T06      2000-07-31
    T11      2000-11-30
    T12      2000-08-31
  • + Share This
  • 🔖 Save To Your Account