The BETWEEN operator is a handy SQL construct, but it can cause
unexpected results when it isn’t understood. Consider the following code
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.