sql: BETWEEN v1 AND v2

问题:

Is there a difference in the order of v1 and v2 in a BETWEEN query on SQL Server?

SELECT *
  FROM table
 WHERE col BETWEEN v1 AND v2

currently I don’t get any results if v1 is bigger than v2. Is this only syntactic sugar for

col >= v1 AND col <= v2

or does it really take all values between the two? on my current observations I guess it’s the first case.

回答1:

SQL Server 2008:

select 1 
where 5 between 1 and 7

1 result

select 1 
where 5 between 7 and 1

0 results

Based on these results, and the Postgre Docs I would hypothesize that the ANSI Standard is as follows (although I can't find that doc).

a between x and y
==
a >= x AND a <= y

UPDATE:

The SQL-92 spec says (quote):

"X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z"

回答2:

Yes! The order of the arguments in the BETWEEN predicate matter. And, yes, this is [mostly] syntactic sugar for the AND-ed comparison form.

The "mostly", above comes from the fact that while logically equivalent, the two expressions may (probably in the past...) receive a distinct query plan on some SQL servers. It is a safe guess that most servers, nowadays, provide an optimal handling of this type of filter, regardless of its form.

回答3:

Yes, you are right, is it only syntactic sugar for the construct you mentioned.