Order of from and to values in SQL Between

Question

I'm creating a simple procedure in SQL Server as below.

DECLARE @num int;
SET @num = 5;
SELECT @num WHERE @num BETWEEN 1 AND 10;
SELECT @num WHERE @num BETWEEN 10 AND 1;

If you run this, the first select statement gives you 5 and the second one returns nothing. I am confused as to why this is, as both cases should return true as 5 is between 10 and 1 as well as 1 and 10.

Is there is a reason why the BETWEEN 10 AND 1 line defies logic?

Thanks

Answer 1

This is what the standard (was decided to be) for SQL.

x BETWEEN a AND b

stands for

( a <= x )  AND  ( x <= b )

See page 211 of: SQL-92 specifications (a copy of a review draft)

   8.3  <between predicate>

     Function
     Specify a range comparison.

     Format
     <between predicate> ::=
          <row value constructor> [ NOT ] BETWEEN
            <row value constructor> AND <row value constructor>

     Syntax Rules
     1) The three <row value constructor>s shall be of the same degree.
     2) Let respective values be values with the same ordinal position
        in the two <row value constructor>s.
     3) The data types of the respective values of the three <row value
        constructor>s shall be comparable.
     4) Let X, Y, and Z be the first, second, and third <row value con-
        structor>s, respectively.
     5) "X NOT BETWEEN Y AND Z" is equivalent to "NOT ( X BETWEEN Y AND
        Z )".
     6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".

---

The only RDBMS I know that is away from the standard (and follows your logic) is MS-Access.

Answer 2

The BETWEEN statement can be translated as

WHERE @num >= 1 and @num <= 10.

Turning this around makes

WHERE @num >= 10 and @num <= 1.

Which won't return much.