Specifies an inclusive range to test.
test_expression [NOT] BETWEEN begin_expression AND end_expression
Boolean
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
NOT BETWEEN returns TRUE if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression.
To specify an exclusive range, use the greater-than and less-than operators (> and <). If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.
This example returns all books with year-to-date sales between and including 4,095 through 12,000. Notice that books with sales of 4,095 are included in the results. If there were any books with sales of 12,000, they would also be included.
USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales BETWEEN 4095 AND 12000
GO
Here is the result set:
title_id ytd_sales
-------- -----------
BU1032 4095
BU7832 4095
PC1035 8780
PC8888 4095
TC7777 4095
(5 row(s) affected)
This example, which uses > and < operators, returns different results because these operators are not inclusive.
USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales > 4095 AND ytd_sales < 12000
GO
Here is the result set:
title_id ytd_sales
-------- -----------
PC1035 8780
(1 row(s) affected)
This example finds all rows outside a specified range (4,095 through 12,000).
USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales NOT BETWEEN 4095 AND 12000
GO
Here is the result set:
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)
< (Less Than) | Operators (Logical Operators) |
> (Greater Than) | SELECT (Subqueries) |
Expressions | WHERE |
Functions |