Skip to main content

Comparison Operators and Between in Relational Databases

Sometimes our techie-brains convert a concept clearly articulated in plain English into unnecessarily mathematically expressed code.

For example, "I want all records with an amount between 1000 and 1999" can become "x >= 1000 AND x <= 1999"

But modern RDBMS database systems give us an English equivalent that saves translating the concept "between" into "greater than or equal to this, and less than or equal to that."

It's the BETWEEN function, and it is the same across SQL Server, PostgreSQL, MySQL, and Oracle. Maybe others, too, but those are the ones that I have used in the past.

BETWEEN is of the form:
MyTestValue BETWEEN StartValue AND EndValue
It is an inclusive comparison, equivalent to using >= and <= So these generate the same results:
SELECT * FROM MyTable WHERE MyValue BETWEEN 100 AND 1000
vs
SELECT * FROM MyTable WHERE MyValue >= 100 AND MyValue <= 1000

What are the advantages of BETWEEN? It's a little less typing; it's more intuitive to understand, without losing any precision in the expression.
And it avoids angle-brackets. Which can sound trivial, but was an issue on a recent bug report I had to deal with. Here's the story:


One sub-system at my current place of work allows our clients to write their own SQL queries, to retrieve exactly the data they need for their specialized business purposes. The sub-system allows global and context-specific variables to be substituted at run-time. These variables are enclosed in angle-brackets, e.g. "<PAYMENTTYPE>"

In this bug report, a client's SQL worked on all her staff's work-stations except one. This one work-station was receiving an Invalid SQL error.

After some effort, I isolated the source of the error. Buried deep in the client's SQL was this Where-clause (some details changed to protect the client):
WHERE G.AccountNumber <> 0
AND ( (G.LastYrAmount >= 1000 AND G.LastYrAmount <= 1999.99)
 OR (G.ThisYrAmount >= 1000 AND G.ThisYrAmount <= 1999.99) )

It's a pretty straight-forward Where-expression. But on the work-station of this one staff member, that condition was being sent to the database as:
WHERE G.AccountNumber <> 0
AND ( (G.LastYrAmount >= 1000 AND G.LastYrAmount = 1999.99)
 OR (G.250.00 = 1000 AND G.ThisYrAmount <= 1999.99) )

It appeared that the sub-system was performing the variable substitution, replacing ThisYrAmount with its value, 250.00 The opening "<" was coming from "G.LastYrAmount <= 1999.99" and the closing ">" from "G.ThisYrAmount >= 1000"

The sub-system needs to be smart enough to recognize that, given all of the other items mixed in between the two angle-brackets, it is NOT dealing with a variable-replacement token.

And it is, everywhere but on this one work-station.

There is something deeper going on with the client's work-station, which may be an issue for another day. For now, by advising them to rewrite their query to use BETWEEN, we can eliminate the paired angle-brackets and therefore the misapplied variable replacement.
WHERE G.AccountNumber <> 0
AND ( (G.LastYrAmount BETWEEN 1000 AND 1999.99)
 OR (G.ThisYrAmount BETWEEN 1000 AND 1999.99) )

A final note: the database backing this sub-system is Microsoft SQL Server, which has another alternative to eliminate the paired angle-brackets while still using the mathematical comparison operators. T-SQL and SQL Server provide Not-Greater and Not-Less operators, "!>" and "!<"
So the WHERE could be rewritten like this instead:
WHERE G.AccountNumber <> 0
AND ( (G.LastYrAmount >= 1000 AND G.LastYrAmount !> 1999.99)
 OR (G.ThisYrAmount >= 1000 AND G.ThisYrAmount !> 1999.99) )
Labels:

Popular posts from this blog

Git Reset in Eclipse

Using Git and the Eclipse IDE, you have a series of commits in your branch history, but need to back up to an earlier version. The Git Reset feature is a powerful tool with just a whiff of danger, and is accessible with just a couple clicks in Eclipse. In Eclipse, switch to the History view. In my example it shows a series of 3 changes, 3 separate committed versions of the Person file. After commit 6d5ef3e, the HEAD (shown), Index, and Working Directory all have the same version, Person 3.0.
Read more

Scala Collections: A Group of groupBy() Examples

Scala provides a rich Collections API. Let's look at the useful groupBy() function. What does groupBy() do? It takes a collection, assesses each item in that collection against a discriminator function, and returns a Map data structure. Each key in the returned map is a distinct result of the discriminator function, and the key's corresponding value is another collection which contains all elements of the original one that evaluate the same way against the discriminator function. So, for example, here is a collection of Strings: val sports = Seq ("baseball", "ice hockey", "football", "basketball", "110m hurdles", "field hockey") Running it through the Scala interpreter produces this output showing our value's definition: sports: Seq[String] = List(baseball, ice hockey, football, basketball, 110m hurdles, field hockey) We can group those sports names by, say, their first letter. To do so, we need a disc
Read more

Java 8: Rewrite For-loops using Stream API

Java 8 Tip: Anytime you write a Java For-loop, ask yourself if you can rewrite it with the Streams API. Now that I have moved to Java 8 in my work and home development, whenever I want to use a For-loop, I write it and then see if I can rewrite it using the Stream API. For example: I have an object called myThing, some Collection-like data structure which contains an arbitrary number of Fields. Something has happened, and I want to set all of the fields to some common state, in my case "Hidden"
Read more

How to do Git Rebase in Eclipse

This is an abbreviated version of a fuller post about Git Rebase in Eclipse. See the longer one here : One side-effect of merging Git branches is that it leaves a Merge commit. This can create a history view something like: The clutter of parallel lines shows the life spans of those local branches, and extra commits (nine in the above screen-shot, marked by the green arrows icon). Check out this extreme-case history:  http://agentdero.cachefly.net/unethicalblogger.com/images/branch_madness.jpeg Merge Commits show all the gory details of how the code base evolved. For some teams, that’s what they want or need, all the time. Others may find it unnecessarily long and cluttered. They prefer the history to tell the bigger story, and not dwell on tiny details like every trivial Merge-commit. Git Rebase offers us 2 benefits over Git Merge: First, Rebase allows us to clean up a set of local commits before pushing them to the shared, central repository. For this
Read more

Code Coverage in C#.NET Unit Tests - Setting up OpenCover

The purpose of this post is to be a brain-dump for how we set up and used OpenCover and ReportGenerator command-line tools for code coverage analysis and reporting in our projects. The documentation made some assumptions that took some digging to fully understand, so to save my (and maybe others') time and effort in the future, here are my notes. Our project, which I will call CEP for short, includes a handful of sub-projects within the same solution. They are a mix of Web APIs, ASP MVC applications and Class libraries. For Unit Tests, we chose to write them using the MSTest framework, along with the Moq mocking framework. As the various sub-projects evolved, we needed to know more about the coverage of our automated tests. What classes, methods and instructions had tests exercising them, and what ones did not? Code Coverage tools are conveniently built-in for Visual Studio 2017 Enterprise Edition, but not for our Professional Edition installations. Much less for any Commun
Read more