BETWEEN

Specifies a range to test.

Syntax

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

Arguments

test_expression

Is the expression to test for in the range defined by begin_expression and end_expression. test_expression must be the same data type as both begin_expression and end_expression.

NOT

Specifies that the result of the predicate be negated.

begin_expression

Is any valid Microsoft® SQL Server™ expression. begin_expression must be the same data type as both test_expression and end_expression.

end_expression

Is any valid SQL Server expression. end_expression must be the same data type as both test_expression and begin_expression.

AND

Acts as a placeholder indicating that test_expression should be within the range indicated by begin_expression and end_expression.

Result Types

Boolean

Result Value

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.

Remarks

To specify an exclusive range, use the greater than (>) and less than operators (<). If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.

Examples

A. Use BETWEEN

This example returns title identifiers for books with year-to-date unit sales from 4,095 through 12,000.

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)

B. Use > and < instead of BETWEEN

This example, which uses greater than (>) and less than (<) 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)

C. Use NOT BETWEEN

This example finds all rows outside a specified range (from 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)

See Also

> (Greater Than)

< (Less Than)

Expressions

Functions

Operators (Logical Operators)

SELECT (Subqueries)

WHERE