[ACCEPTED]-sql: BETWEEN v1 AND v2-syntactic-sugar

Accepted answer
Score: 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 4 these results, and the Postgre Docs I would hypothesize 3 that the ANSI Standard is as follows (although 2 I can't find that doc).

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


The SQL-92 1 spec says (quote):

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

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

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

Score: 4

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

More Related questions