sql: BETWEEN v1 AND v2

Question

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.

score 13 · Accepted Answer · edited Oct 16 '09 at 08:50

13

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"

edited Oct 16 '09 at 08:50

onedaywhen

55,269
12
100
138

answered Oct 15 '09 at 14:46

Tom Ritter

99,986
30
138
174

And check out "select 1 where 7 between 7 and 1" - awesome! – Cade Roux Oct 15 '09 at 14:50
the examples are not syntactically valid in MySQL – törzsmókus Mar 29 '13 at 14:06
1

@törzsmókus maybe because it was an SQL Server question – Atys Nov 19 '17 at 10:04

mjv · Answer 2 · 2009-10-15T14:51:55.483

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.

score 4 · Answer 3 · answered Oct 15 '09 at 14:47

4

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

answered Oct 15 '09 at 14:47

Maximilian Mayerl

11,253
2
33
40

sql: BETWEEN v1 AND v2

3 Answers3

Linked