PL/SQL "between x and y" does not work, if y < x. Why?

Question

I have a PL/SQL code like:

case when column between 201203 and 201201
then other_column
end

I know that there are values in column that are 201203. So code should return some values. But it didn't until I corrected it like:

case when column between 201201 and 201203
then other_column
end

Why between keyword works like that? How it is implemented on Oracle Databases?

Answer 1

This is ANSI SQL behavior.

 expr1 BETWEEN expr2 AND expr3

translates to

 expr2 <= expr1 AND expr1 <= expr3

Answer 2

In Between Clause, always lower value comes first instead of higher value.

Ex:- BETWEEN 100 AND 200

instead of

BETWEEN 200 AND 100

When Query Parser Parse BETWEEN 100 AND 200 then it would be like this:-

X >= 100 AND X <= 200

Answer 3

Consider

val1 between val2 and val3

as

(val1 >= val2) and (val1 <= val3)

Then what we have?

column between 201203 and 201201

is the equivalent to

( column >= 201203 ) and (column <= 201201)

If the column value is, let's say, 201202 then the first condition ( column >= 201203 ) will be false and the second one also will be false. That is why you are not getting any results. Find out more.