SQL between and usage

Between

Specify the test range.

Syntax

Test_expression[Not]Begin_expressionAndEnd_expression

Parameters

Test_expression

Is Used inBegin_expressionAndEnd_expressionThe expression for testing within the defined range.Test_expressionMust beBegin_expressionAndEnd_expressionHas the same data type.

Not

The result of the specified predicate is reversed.

Begin_expression

Is any valid Microsoft SQL Server expression.Begin_expressionMust beTest_expressionAndEnd_expressionHas the same data type.

End_expression

Is any valid SQL Server expression.End_expressionMust beTest_expressionAndBegin_expressionThe same data type.

And

As a placeholderTest_expressionIt should be inBegin_expressionAndEnd_expressionWithin the specified range.

Result type

Boolean

Result Value

IfTest_expressionThe value is greater than or equalBegin_expressionAnd less than or equalEnd_expressionValue, then between returns true.

IfTest_expressionThe value is lessBegin_expressionOr greaterEnd_expressionNot between returns true.

Note

To specify the exclusion range, use the greater than (>) and less than (<) operators. If the input of any between or not between predicates is null, the result is unknown.

Example A. Use

In this example, the Head identifier of the book is returned. The annual sales of these books are from 4,095 to 12,000.

Use pubsgoselect title_id, ytd_salesfrom titleswhere ytd_sales between 4095 and 12000go
 

The following 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

In this example, the greater than (>) and less than (<) operators are used. Because these operators are not included, different results are returned.

 
Use pubsgoselect title_id, ytd_sales from titles where ytd_sales> 4095 and ytd_sales <12000 go
 

The following is the result set:

 
Title_id ytd_sales -------- ----------- pc1035 8780 (1 row (s) affected)
 

C. Use not

In this example, all rows within the specified range (from 4,095 to 12,000) are located.

 

 
Use pubsgoselect title_id, ytd_salesfrom titleswhere ytd_sales not between 4095 and 12000go
 

The following 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