|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
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 changed in, I believe, 6.0, to adhere to the ANSI SQL standard.) -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "js" <js@some***@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. > > 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 -- That's news to me. Thanks David. :-)
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@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 > -- > For one meeting, the default behavior in the working draft SQL2, was
SYMMETRIC. Microsoft was attending meetings and they picked it up. Then the next meeting we switched it back because almost no products were SYMMETRIC except MS. That sounds strange to me. SQL Server has been SYMMETRIC since 1.0 to some 4.21 (as I remember).
Probably a legacy from Sybase. And in 6.0 or it could have been 4.2, they changed it to ASYMMETRIC. So I doubt that MS picked up SYMMETRIC from an ANSI meeting, as this was the behavior since a lot earlier. And prior to 6.0, Sybase made those calls, not MS. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1125874296.990565.129420@g44g2000cwa.googlegroups.com... > For one meeting, the default behavior in the working draft SQL2, was > SYMMETRIC. Microsoft was attending meetings and they picked it up. > Then the next meeting we switched it back because almost no products > were SYMMETRIC except MS. > can you see it?
select * from t1 where c1 >= 1 and c1 <= 5 select * from t1 where c1 >= 5 and c1 <= 1 AMB Show quote "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. > > > 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 Show quote "js" <js@some***@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. > >
"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 confused 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 confused 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... Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1125676773.780264.262550@g49g2000cwa.googlegroups.com... >> 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 > confused 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 confused over the predicate "IS NOT NULL", > which is three keywords but a single expression. > > -- > David Portas > SQL Server MVP > -- > 
Other interesting topics
|
|||||||||||||||||||||||
