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