Thread: between syntax

Good day,

shouldn't 

'between 1 and 7'

return the same results as

'between 7 and 1'

?

Markus Bertheau

Markus Bertheau <twanger@bluetwanger.de> writes:
> shouldn't 
> 'between 1 and 7'
> return the same results as
> 'between 7 and 1'
> ?

Nope.  SQL92 section 8.3 defines BETWEEN thusly:
        6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".
        regards, tom lane

On 16 Nov 2001, Markus Bertheau wrote:

> Good day,
>
> shouldn't
>
> 'between 1 and 7'
>
> return the same results as
>
> 'between 7 and 1'

Don't think so, if I'm reading the spec correct, the conversion is:

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

Markus Bertheau writes:

> shouldn't
>
> 'between 1 and 7'
>
> return the same results as
>
> 'between 7 and 1'

This question might have been interesting 10 or 15 years ago when the SQL
standard was written, but today the answer is no.

What you're looking for is the syntax BETWEEN SYMMETRIC 1 AND 7, which is
new in SQL99 and not yet implemented in PostgreSQL.

-- 
Peter Eisentraut   peter_e@gmx.net