- 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
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_valueis 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 94305Listing 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
Tips
- test_column can be an expression.
- The NOT that can precede BETWEEN is independent of the NOT that can precede test_column; see the Tips in “Matching Patterns with LIKE” earlier in this chapter.
Listing 4.38 shows how to rewrite Listing 4.36 with an exclusive range, which doesn’t include the $10 and $19.95 endpoints. See Figure 4.38 for the result.
Listing 4.38. List the titles priced between $10 and $19.95, exclusive. See Figure 4.38 for the result.
SELECT title_id, price FROM titles WHERE (price > 10) AND (price < 19.95);Figure 4.38 Result of Listing 4.38.
title_id price -------- ----- T04 12.99 T09 13.95 T12 12.99
Specifying a character range requires some thought. Suppose you want to search for last names that begin with the letter F. The following clause won’t work because it will retrieve someone whose last name is the letter G (is the letter G, not starts with the letter G):
WHERE last_name BETWEEN 'F' AND 'G'
This next clause shows the correct way to specify the ending point (in most cases):
WHERE last_name BETWEEN 'F' AND 'Fz'
In older PostgreSQL versions, convert the floating-point numbers in Listings 4.36 and 4.38 to DECIMAL; see “Converting Data Types with CAST()” in Chapter 5. To run Listings 4.36 and 4.38, change the floating-point literals to:CAST(19.95 AS DECIMAL)
In Microsoft Access date literals, omit the DATE keyword and surround the literal with # characters instead of quotes. To run Listing 4.37, change the dates in the WHERE clause to #2000-01-01# and #2000-12-31#.
In Microsoft SQL Server and DB2 date literals, omit the DATE keyword. To run Listing 4.37, change the dates in the WHERE clause to '2000-01-01' and '2000-12-31'.
In some DBMSs, low_value can exceed high_value; search your DBMS documentation for WHERE or BETWEEN.