THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Problems using BETWEEN

The BETWEEN operator is a handy SQL construct, but it can cause unexpected results when it isn’t understood. Consider the following code snippet:

where x between .9 and 1.10

One of the questions you should ask is this: What is x?

What if x has a float, real, or double data type? These data types do not store exact representations of numbers, only approximations. When 0.9 is stored in a real column or variable, it may be between 0.9 and 1.1. Or it may not. When you set a real to 0.9, internally it becomes something very close to but maybe not quite equal to 0.9. Even though you entered 0.9, it could be stored as something slightly less than 0.9.

Now consider this:

where x between 9 and 11

If x is an integer and is set to 9, it really is 9 and it will pass the test. Because of data types, never let it be said that all numbers are created equal.

Let's refactor the statement to be free of hardcoded values:

where x between y and z

What's wrong with that? I'll simplify it for you. Everything is an integer, x, y, and z are all declared with the integer data type. Once again, the question to ask is what are x, y, and z? Or to be a little clearer, ask what are the values of x, y, and z. The uncertainty arises over the potential values of y and z. Do you know that y will never be more than z? After performing substitutions, what if we have the following?

where 10 between 9 and 11

The statement would be true. But what if after performing substitutions we would have obtained this?

where 10 between 11 and 9

The statement would be false even though 10 is between 9 and 11. We see that order matters - no surprise if you've ever read the documentation. Order is easy to see and deal with when the values are hardcoded. But when you have variables, what if you really don't know a priori if y <= z? You need to think of BETWEEN as a number line. Look at these next two snippets to see what happens when the order of the BETWEEN values is changed: 

where -10 between  -9 and -11 -- this does NOT work
where -10 between -11 and  -9 -- this works

Here is a workaround to this dilemma of not knowing the relative positions on y and z on the number line:

where x between y and z or x between z and y

I've left you with a workaround and a challenge. I like interactivity with my readers, so I ask you to post your workaround as a comment. Another workaround is to refactor the problem into checking for a +/- 10% change. Here's something you can copy and paste to get started:

declare @x int = 10;
declare @y int =  9;
declare @z int = 11;

select 'this works' where @x between @y and @z or @x between @z and @y;

There is one more thing that about the first code snippet shown above that caught my attention. The two numbers .9 and 1.10 violate the Joint Commission's guidelines on numbers used in prescriptions. The Joint Commission issues guidelines for healthcare institutions to prevent errors and not kill patients, among other things. There is a "Do Not Use" list of abbreviations and formatting styles that have been proven to cause medical errors. A number such as .9 is more likely to be seen as 9 than 0.9 is. Numbers formatted as 1.10 or 1.0 are also dangerous. The 1.0 is more likely to be misinterpreted as 10 than 1. is. Misinterpretations of decimal numbers have repeatedly been proven to kill patients. These formatting guidelines were originally developed for handwritten prescriptions. Some people think the guidelines should also be applied to computerized forms. I'm not suggesting you change your T-SQL code, but I thought those of you who do user interface design might find this discussion interesting.

 

 

 

 

Published Saturday, April 27, 2013 7:13 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

John Alan said:

John,

Wow, datatypes and order matter - it could lead to obscure logical bugs!

Thanks for bringing this up

April 28, 2013 3:49 AM
 

Stan L said:

Hi John,

What about dates?  Is there any difference between the following:

WHERE CreatedDate BETWEEN '2013-01-01' AND '2013-01-31'

and

WHERE CreatedDate >= '2013-01-01' AND CreatedDate <= '2013-01-31'

Good article and thanks.

Stan

April 29, 2013 6:41 PM
 

Sunil said:

Nice Article John :)

April 30, 2013 9:45 AM
 

John Paul Cook said:

Stan, those are equivalent statements. Be sure to keep in mind that '2013-01-31' is actually '2013-01-31 00:00:00' and that '2013-01-31 14:22:05', for example, is greater than '2013-01-31 00:00:00'.

May 3, 2013 12:18 AM
 

Stan L said:

Thanks John for the clarity.  That was very helpful.

Cheers,

Stan

May 3, 2013 2:29 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement