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.

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"

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.

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