sql - SQL 之间的 from 和 to 值的顺序

Question

我正在 SQL Server 中创建一个简单的过程，如下所示。

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

如果你运行这个，第一个 select 语句给你 5，第二个什么都不返回。
我很困惑为什么会这样，因为这两种情况都应该返回 true，因为 5 介于 10 和 1 以及 1 和 10 之间。
BETWEEN 10 AND 1是否有原因线条不符合逻辑？

谢谢

Answer 1

这就是 SQL 的标准(已决定)。

x BETWEEN a AND b

代表

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

参见第 211 页: 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".

我知道的唯一远离标准(并遵循您的逻辑)的 RDBMS 是 MS-Access。