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
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.
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.
That's the way it's designed.
WHERE 5<test_expression> BETWEEN 10<begin_expression> AND 1<end_expression>;
Does not meet the requirement for a BETWEEN match;
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
精彩评论