Between
operator specifies range to test. If any input to the BETWEEN or NOT BETWEEN
predicate is NULL, the result is UNKNOWN. Rules for evaluating numbers and
strings are same but the result are not straightforward for strings. String
value is evaluated according to the characters in the value. And unless the
full string is specified, the border limit values are not included. You can
check that in examples explained below.
Syntax of Between operator
:
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
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 expression in sql server. begin_expression must be the same data type as both test_expression and end_expression.
end_expression is any valid expression in sql server. end_expression must be the same data type as bothtest_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.
Return type of result is 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.
Examples of BETWEEN Operator :
Example 1 : Using between operator to test
range of numbers in where cluse
SELECT ProductName
FROM Products
WHERE Unitprice BETWEEN
10 AND 20
Output
ProductName UnitPrice
Chai 18.00
Chang 19.00
Aniseed Syrup 10.00
Genen Shouyu 15.50
Pavlova 17.45
Sir Rodney's Scones
10.00
NuNuCa Nuß-Nougat-Crème 14.00
Gorgonzola Telino 12.50
Sasquatch Ale 14.00
Steeleye Stout 18.00
Inlagd Sill 19.00
Chartreuse verte 18.00
Boston Crab Meat 18.40
Singaporean Hokkien Fried Mee 14.00
Gula Malacca 19.45
Spegesild 12.00
Chocolade 12.75
Maxilaku 20.00
As you can check that above query displays unitprice greater
than and equal to $10 or less than and equal to $20. Unitprice $10 and $20 are
also included in result.
Example 2 : Using between operator to test range of characters
in where cluse
SELECT ProductName, UnitPrice
FROM Products
WHERE ProductName BETWEEN
'a' AND 'c'
Output
ProductName UnitPrice
Aniseed Syrup 10.00
Alice Mutton 39.00
Boston Crab Meat
18.40
Based on the output you can understand that the result set
includes only product name starting with character ‘a’ or ‘b’ and character ‘c’
is not incluced in the result set, although there are products starting with
character ‘c’. Unless you specify full string, the border limit values are not
included in result. You can get that in below example
SELECT ProductName, UnitPrice
FROM Products
WHERE ProductName BETWEEN
'a' AND 'chai' ORDER BY ProductName
Output
ProductName UnitPrice
Alice Mutton 39.00
Aniseed Syrup 10.00
Boston Crab Meat 18.40
Camembert Pierrot 34.00
Carnarvon Tigers 62.50
Chai 18.00
As you can see that in the above example full string is
specified in the border limit and it also included in the result set. Result
set included all products starting with character ‘a’or ’b’ but for charcter ‘c’
it includes all products whose name has these combinations ‘c’,‘c(a-h)’, ‘c(a-h)a’, c(a-h)a(a-h), as border limit is ‘chai’ it is not included in the result.
Example 3 : Using not between operator to test range of numbers in where cluse
SELECT ProductName, UnitPrice
FROM Products
WHERE Unitprice NOT BETWEEN
10 AND 20 ORDER BY UnitPrice
Output
ProductName UnitPrice
Geitost 2.50
Guaraná Fantástica 4.50
Teatime Chocolate Biscuits 9.20
Rogede sild 9.50
Zaanse koeken 9.50
Jack's New England Clam Chowder 9.65
Gustaf's Knäckebröd 21.00
Queso Cabrales 21.00
Louisiana Fiery Hot Pepper Sauce 21.05
Chef Anton's Gumbo Mix 21.35
Flotemysost 21.50
Above query displays the product whose unitprice is less than
$10 or greater than $20.