In SQL, “BETWEEN X AND Y” means “greater than or equal to X and less than or equal to Y” – not necessarily what you might infer from its plain-English counterpart.

Is 6 between 3 and 9? Yes.

Is 6 between 9 and 3? Yes, but not in SQL.

In SQL, we use the BETWEEN operator to determine whether a value falls between two other values. We might think that using a clause like “BETWEEN X AND Y” establishes a range between X and Y, and that value comparisons will be true as long as the value being compared falls into that range.

However, the BETWEEN operator works differently. “BETWEEN X AND Y” actually translates into “greater than or equal to X and less than or equal to Y.” This means that X and Y have to be placed in ascending order for the statement to work correctly – X must be the lower of the two values.

Here’s a screenshot demonstrating this in Oracle, but virtually any database works the same way. I’ve included examples for both numbers and characters.

Usually, this behavior isn’t a problem. Especially if you are working with literals, as in the screenshot above, you’re likely to put the lower value first, just as a matter of habit. However, if you are performing logic based on variables or column values, you need to make sure that the BETWEEN clause gets the lower end of the range first, or else your results may not be what you expect.