Back to the Basics: Understanding the BETWEEN Operator
Posted by decipherinfosys on November 7, 2008
At one of the client sites, a junior developer asked this question which is worth posting over here. The question was pertaining to the BETWEEN operator. As you know, this operator is used to test the existence of the values between two expressions. Here is the entry from BOL for the syntax:
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
The key to understanding is this section (from BOL):
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.
What the developer was trying to do was – use the criteria in any order … so, if we have this query against the AdventureWorks database:
select * from Production.Product where SellStartDate between ‘2001-06-29’ and ‘2001-07-02’
It will return 72 records. If the values are swapped:
select * from Production.Product where SellStartDate between ‘2001-07-02’ and ‘2001-06-29’
it will return zero records and the reason is that internally the query (as mentioned by BOL and the ANSI SQL Standards as well) gets translated to:
select * from Production.Product where SellStartDate >= @first_value and SellStartDate <= @last_value
So, in the event of the second query, you will never find an intersection set of the data. Please do note that the equality ( = ) is also included when you use the BETWEEN clause.
It would have been perfectly fine if one wanted to avoid all the records where the dates were falling between those 2 ranges – in that case, one then needs to use the “NOT BETWEEN” operator:
select * from Production.Product where SellStartDate not between ‘2001-07-02’ and ‘2001-06-29’
Which returns 504 records.