The BETWEEN keyword specifies an inclusive range to search. For example, to find all books with sales between (and including) 4095 and 12,000, use this query:
SELECT title_id, ytd_sales |
||
FROM titles |
||
WHERE ytd_sales BETWEEN 4095 AND 12000 |
||
title_id |
ytd_sales |
|
-------- |
--------- |
|
BU1032 |
4095 |
|
BU7832 |
4095 |
|
PC1035 |
8780 |
|
PC8888 |
4095 |
|
TC7777 |
4095 |
|
(5 row(s) affected) |
Notice that books with sales of 4095 are included in the results. If there were any books with sales of 12,000, they would also be included.
To specify an exclusive range, use the greater-than and less-than operators (> and <). The preceding query using the greater-than and less-than operators returns different results because these operators are not inclusive:
SELECT title_id, ytd_sales |
|||||
FROM titles |
|||||
WHERE ytd_sales > 4095 AND ytd_sales < 12000 |
|||||
title_id |
ytd_sales |
||||
-------- |
--------- |
||||
PC1035 |
8780 |
||||
(1 row(s) affected) |
|||||
NOT BETWEEN finds all rows outside the range you specify. To find all books with sales outside the 4095 to 12,000 range:
SELECT title_id, ytd_sales |
||
FROM titles |
||
WHERE ytd_sales NOT BETWEEN 4095 AND 12000 |
||
title_id |
ytd_sales |
|
-------- |
--------- |
|
BU1111 |
3876 |
|
BU2075 |
18722 |
|
MC2222 |
2032 |
|
MC3021 |
22246 |
|
PS1372 |
375 |
|
PS2091 |
2045 |
|
PS2106 |
111 |
|
PS3333 |
4072 |
|
PS7777 |
3336 |
|
TC3218 |
375 |
|
TC4203 |
15096 |
|
(11 row(s) affected) |
||
When you specify ranges, be sure to put single quotation marks around all char, varchar, text, datetime, and smalldatetime data.