| Previous | Table of Contents | Next |
BETWEEN Is Inclusive, NOT BETWEEN Is Exclusive
In the last example, none of the rows matched the endpoints of the BETWEEN. If you use BETWEEN to search for a range of values, and the values exist, BETWEEN returns them; NOT BETWEEN does not.
To demonstrate this, I have created a very simple table with one integer column (see Table 3.4). The table is called t1, and the column is called c1. The c1 column has all the integers from zero to five, inclusive.
| c1 |
|---|
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
To demonstrate how BETWEEN works, consider the following query, which retrieves data from the middle of the table:
select c1 from t1 where c1 between 1 and 3
c1 ------ 1 2 3 (3 row(s) affected)
BETWEEN is inclusive, so all the values between 1 and 3 are returned, including 1 and 3. The server changed this query into select c1 from t1 where c1 >=1 and c1 <=3, and if you wrote that query, you would get the same results.
Now, consider the inverse of the last query:
select c1 from t1 where c1 not between 1 and 3
c1 ---- 0 4 5 (3 row(s) affected)
NOT negates the effect of BETWEEN. Whatever rows showed up in the first query will not show up here, and whatever rows were excluded in the first query will be included here. When using NOT BETWEEN, the values listed in the query are not returned, if they exist.
BETWEEN can be helpful when performing range searches. Remember, though, that you can always write a BETWEEN query using two inequality expressions. Next, I’ll talk about using the IN keyword to search a list of values.
Using IN
The IN keyword, when used in a WHERE clause, allows comparison to a list of values. If the values that you want are not contiguous (there are rows mixed in the middle that you don’t want), it is easier to use an IN list than to use many values all separated by an OR.
Suppose that I want the address information for a list of four authors, and I have their author IDs. First, I write the query without using IN :
select au_id, address, city, state
from authors
where au_id = “172-32-1176”
or au_id = “238-95-7766”
or au_id = “486-29-1786”
or au_id = “648-92-1872”
Then I write that same query using an IN to do the work:
select au_id, address, city, state
from authors
where au_id in (“172-32-1176”, “238-95-7766”,
“486-29-1786”, “648-92-1872”)
au_id address city state ----------- ------------ -------------- ------ 172-32-1176 10932 Bigge Rd. Menlo Park CA 238-95-7766 589 Darwin Ln. Berkeley CA 486-29-1786 18 Broadway Av. San Francisco CA 648-92-1872 55 Hillsdale Bl. Corvallis OR (4 row(s) affected)
As you can see, the in list is a more convenient way to test a list of values. Both queries return the same results.
Using NOT with IN
Just as with BETWEEN, the NOT keyword can be used to modify IN. Using NOT IN can be helpful when you know information about rows that you don’t want. Suppose I have an incomplete list of authors, and now I would like to get a list of IDs that weren’t on the original list.
select au_id
from authors
where au_id not in(“172-32-1176”, “238-95-7766”,
“486-29-1786”, “648-92-1872”)
au_id ----------- 213-46-8915 267-41-2394 274-80-9391 341-22-1782 409-56-7008 427-17-2319 472-27-2349 527-72-3246 672-71-3249 712-45-1867 722-51-5454 724-08-9931 724-80-9391 756-30-7391 807-91-6654 846-92-7186 893-72-1158 899-46-2035 998-72-3567 (19 row(s) affected)
When the server gets this query, it checks the author ID for each row in the authors table. If the ID is not in the list I provided in this query, the row is included in the result set.Using LIKE
The LIKE operator allows you to use wildcard characters to retrieve rows when you may know only general information about the row you want. You ordinarily use LIKE to search fixed-length character columns (char) and variable-length character columns, but you may also use LIKE to search for dates.
Wildcards are special characters that are used to represent any character in a string.
| Previous | Table of Contents | Next |