between

hi,
what diff:
select * from mytable where intfieldname between 1 and 5
and
select * from mytable where intfieldname between 5 and 1?
Thanks.X BETWEEN Y AND Z
is equivalent to:
X>=Y AND X<=Z
so your second example will never return any rows.
David Portas
SQL Server MVP
--|||The second will return 0 rows. The smaller value need to be the first value
you list.
(SQL Server allowed both to return rows in some prior version, it was change
d in, I believe, 6.0, to
adhere to the ANSI SQL standard.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"js" <js@.someone@.hotmail.com> wrote in message news:%23itV%2388rFHA.3392@.TK2MSFTNGP11.phx.g
bl...
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>|||can you see it?
select * from t1 where c1 >= 1 and c1 <= 5
select * from t1 where c1 >= 5 and c1 <= 1
AMB
"js" wrote:

> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>
>|||The Standard changed too. SQL99 defined a SYMMETRIC / ASYMMETRIC clause
for the BETWEEN predicate. ASYMMETRIC - the old behaviour - is still
the default, but:
X BETWEEN SYMMETRIC Y AND Z
is equivalent to:
((X BETWEEN ASYMMETRIC Y AND Z)
OR (X BETWEEN ASYMMETRIC Z AND Y))
David Portas
SQL Server MVP
--|||Order matters. The left side must be smaller.
My typical suggestion is to avoid between for this and other reasons...
http://www.aspfaq.com/2280
"js" <js@.someone@.hotmail.com> wrote in message
news:%23itV%2388rFHA.3392@.TK2MSFTNGP11.phx.gbl...
> hi,
> what diff:
> select * from mytable where intfieldname between 1 and 5
> and
> select * from mytable where intfieldname between 5 and 1?
> Thanks.
>|||> http://www.aspfaq.com/2280
Quote:
"When you are using other clauses in your query, you also have to
remember to wrap the BETWEEN clause in brackets so that its AND isn't
with other ANDs in the WHERE clause."
Just to be clear about this point. Parenthesizing the BETWEEN predicate
probably aids readability but has no effect on the order of evaluation.
That's because the AND keyword in the BETWEEN predicate has nothing to
do with the Boolean operator of the same name. "x BETWEEN y AND z" is
evaluated as a single expression. I mention this detail because I've
found people get similarly over the predicate "IS NOT NULL",
which is three keywords but a single expression.
David Portas
SQL Server MVP
--|||Yes, the point about evaluation was meant to apply only to the point about ,
not that x = 5 or x between 7 and 12 or x = 4 would be evaludated in some
weird way. The bullet itself was added merely to address readability
concerns, but it does bring up the notion of more complex queries. I'm not
sure how to word it better to turn off the alarm bells that caused your
reply, however...
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1125676773.780264.262550@.g49g2000cwa.googlegroups.com...
> Quote:
> "When you are using other clauses in your query, you also have to
> remember to wrap the BETWEEN clause in brackets so that its AND isn't
> with other ANDs in the WHERE clause."
> Just to be clear about this point. Parenthesizing the BETWEEN predicate
> probably aids readability but has no effect on the order of evaluation.
> That's because the AND keyword in the BETWEEN predicate has nothing to
> do with the Boolean operator of the same name. "x BETWEEN y AND z" is
> evaluated as a single expression. I mention this detail because I've
> found people get similarly over the predicate "IS NOT NULL",
> which is three keywords but a single expression.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks all for the help.|||That's news to me. Thanks David. :-)
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:1125674797.272562.256010@.g47g2000cwa.googlegroups.com...
> The Standard changed too. SQL99 defined a SYMMETRIC / ASYMMETRIC clause
> for the BETWEEN predicate. ASYMMETRIC - the old behaviour - is still
> the default, but:
> X BETWEEN SYMMETRIC Y AND Z
> is equivalent to:
> ((X BETWEEN ASYMMETRIC Y AND Z)
> OR (X BETWEEN ASYMMETRIC Z AND Y))
> --
> David Portas
> SQL Server MVP
> --
>