Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Operator RSS Feeds

Between Operator

Posted By: Sarita Patel     Category: SQL Server     Views: 3460

This article explains use between operator with different examples.

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.

  
Share: 

 
 
 

Didn't find what you were looking for? Find more on Between Operator Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of Between Operator is from United States.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!