between operator behavior

In sql server,teradata,oracle when you use between operator to query the
table it shows as below
Where 1 between 1 and 10 -> True
Where 1 between 10 and 1 -> False
Logically speaking the above method is wrong so I want to know why the
implementation is done like this across all databases.
Appreciate your help in understanding the myth.
Thanks
BETWEEN internally operates by expanding the expression to:
WHERE 1 >= (lower bound) AND 1 <= (upper bound)
That does not work for WHERE 1 BETWEEN 10 AND 1 ...
WHERE 1 >= 10 --FALSE
"PraveenMohan" <PraveenMohan@.discussions.microsoft.com> wrote in message
news:41C2CFE5-3A25-4678-A395-3EAAD516EAC9@.microsoft.com...
> In sql server,teradata,oracle when you use between operator to query the
> table it shows as below
> Where 1 between 1 and 10 -> True
> Where 1 between 10 and 1 -> False
> Logically speaking the above method is wrong so I want to know why the
> implementation is done like this across all databases.
> Appreciate your help in understanding the myth.
> Thanks
>
|||Because that's how it's defined in the ANSI/ISO SQL Standard.
X BETWEEN Y AND Z
is equivalent to
X>=Y AND X<=Z
therefore:
1 BETWEEN 10 AND 1
= 1>=10 AND 1<=1
= FALSE
It might seem "illogical" if you have some different intuitive model of what
"between" means but to me the only obvious alternative is to raise an error
if Y>Z. I think that would be a lot less convenient since you would
constantly have to implement bounds checking before using BETWEEN.
David Portas
SQL Server MVP
|||Just some useless info to add...:
Some earlier version of SQL Server did actually return rows even if the values are "swapped". My
guess is that 6.0 was the version that changed this as MS did a lot of ANSI SQL work on that
version. Imagine the number of bugs creeped up in sw, with 6.0... :-).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ACDBB972-14E2-4240-8E8A-D4EABC5D13FA@.microsoft.com...
> Because that's how it's defined in the ANSI/ISO SQL Standard.
> X BETWEEN Y AND Z
> is equivalent to
> X>=Y AND X<=Z
> therefore:
> 1 BETWEEN 10 AND 1
> = 1>=10 AND 1<=1
> = FALSE
> It might seem "illogical" if you have some different intuitive model of what
> "between" means but to me the only obvious alternative is to raise an error
> if Y>Z. I think that would be a lot less convenient since you would
> constantly have to implement bounds checking before using BETWEEN.
> --
> David Portas
> SQL Server MVP
> --
>