Systems Engineering and RDBMS

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: